Re: Max length of string global variable

Tech-Archive recommends: Fix windows errors by optimizing your registry

I had the same problem. I solved it this way

Well, when I launch dtsrun with a string parameter, it only passes the 255
first caracter to the global variable. So the solution is in the SQL code,
declare as many 255 variables and concatenate them in the DTS Code:

in sql code:

declare varlongstring nvarchar(4000),
varsubstring1 varchar(255),
varsubstring2 varchar(255),
.....

SET varsubtsring1 = substring(varlongstring , 1,255)
SET varsubtsring2 = substring(varlongstring , 256,510),
...

then send dtsrun /Avar1:8=varsubtsring1 /Avar2:8=varsubtsring2, .....

IN the DTS package
declare the global string variables varlongstring, var1, var2,...

and in a activeX, concatenation

DTSGlobalVariables("Varlongstring").Value =
DTSGlobalVariables("var1").Value & DTSGlobalVariables("var2").Value
DTSGlobalVariables("Varlongstring").Value =
DTSGlobalVariables("Varlongstring").Value & DTSGlobalVariables("var3").Value


Hope it's fine you

Mehdi






--
mehdi


"Andy" wrote:

> Yes it does work with a smaller size varchar, however I am getting mixed
> results. Sometimes with a larger varchar it has worked.
> "Allan Mitchell" wrote:
>
> > Should be OK
> >
> > I did this
> >
> > IN an ExecuteSQL task did
> >
> > SELECT REPLICATE('A',1000) as Value
> >
> > I assigned this to an output variable of type string
> >
> > I then had an ActiveScript task that did
> >
> > MsgBox(Len(DTSGlobalVariables("Val").Value))
> >
> >
> > The answer was 1000
> >
> > It looks as though your value is not being set. Period.
> >
> > Does it work with a smaller size varchar?
> >
> > --
> >
> >
> >
> > Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> > www.SQLDTS.com - The site for all your DTS needs.
> > www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
> > www.konesans.com - Consultancy from the people who know
> >
> >
> > "Andy" <Andy@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> > news:BABF4C3A-1E64-4D8D-89E1-7618288E46EA@xxxxxxxxxxxxxxxx
> > > Is there a max length for DTS Global Variables of type string? I have a
> > > stored procedure that I am running using an Execute SQL Task and it is
> > > outputting a paramater of type varchar. I would like to output that
> > > parameter to the DTS package. The output parameter is at least 500
> > > characters and the global variable is showing up as not displayable.
> > >
> > > Any help is appreciated.
> > >
> > > Thanks
> >
> >
> >
.

Relevant Pages

  • Re: Store a Path Change
    ... You're wrong about xAns being a public variable (I'm assuming you mean it's ... the string you're assigning to xAns is indeed lost once the ... As far as global variables themselves go, you should try to avoid them ... Object Hungarian Notation Naming Conventions for VB ...
    (microsoft.public.vb.general.discussion)
  • RE: List boxes, functions and query criteria
    ... hand in VBA base on the selections in the List Boxes and any other controls ... Dim strSQL As String ... Dim strWhereString As String ... To access the global variables in the query I have used simple functions ...
    (microsoft.public.access.queries)
  • Re: varchar problem
    ... A space is just another character. ... >> want to strip them, ... VARCHAR strings will only be as long as ... > the stored string itself. ...
    (alt.php)
  • RE: ADO.NET 2.0 saving single space to SQL?
    ... It turns out that changing the parameter to a type of varchar or using the ... Fields that are being inserted into the database as a zero length ... string in 1.1 contain a single space when inserted with 2.0 compiled code. ... The code specifies a stored proc to run and set up the parameter collection. ...
    (microsoft.public.dotnet.framework.adonet)
  • Not getting all data from Stored Procedure.
    ... I am trying to get the identity of a row I add in my stored procedure. ... Dim ConnectionString as String ... @srvname varchar, ...
    (microsoft.public.dotnet.framework.aspnet)