Global variables in SQL task

  • I'm accessing a global variable in SQL task using the Parameters.  The only thing is, my variables are being cut off after 20 characters.  Is there a way to increase this?

    If I do a simple msgbox in ActiveX, the complete lenght is displayed.  But when I access it using SQL task, it gets cut off after 20 characters.. 

  • How is this variable declared?

  • if your global variable is a string type you shouldn't have any problem.

     

     

  • Ya its declared as a string. 

    ActiveX can read the whole length fine

    When I add the ? to the SQL and associate an parameter to it, I only get 20 characters.

    Update table

    SET TableName = ?

    WHERE Active = 1

    If it's (?) more then 20 characters, it gets cut off.  I will double check tomorrow, but it's driving me nuts.

     

  • Ahhh ... dumb question, but you never know ... what is the datatype of the field you're updating?

    As a test to rule out a SQL task problem. How about running the SQL in an ActiveXScript task?

     

    --------------------
    Colt 45 - the original point and click interface

  • sometimes we may forget....

    exactly what phil said, what's the datatype for TableName? 

  • dito .

  • The Table is nvarchar(100). 

    If I manually put in

    Table = 'blaa blaa blaa blaa'  

    It works and I get more then 20 characters.  I'll try it again tomorrow morning, maybe I over looked something dumb ?

  • It's always something dumb once we find it .

  • This is interesting...

    Here is my actually query (Two steps in 1)

    UPDATE DataWarehouse.dbo.SQLTableStatus

    SET Status = 'N',

     Comments = 'Can not find TextFile',

     LastMaintDate = getdate()

    WHERE Textfile = ?

    INSERT INTO Email.dbo.AutoEmail

     (Source, Recipients, Subject, Message)

     VALUES ('SQL',?,'SQL DTS Error -> '+ ?, 'Can not find the text file  '+ ? )

    Depending on what I set the length of for TextFile (the first ? in pink) in the table SQLTableStatus, will control what the rest of the lengths are for the rest of the ?.

    So I set the length to 25 for the Textfile, and I only got 25 characters for the rest of the question marks.    Is this a bug ?

  • Could be... certainly not the expected behavior. Maybe you should ask on a dts specialized site for this one. You might get a clearer answer.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply