SQL Server Performance Forum – Threads Archive

Global Variable Data Type

Hi, I want to define a global variable in DTS which could hold approx 1000 characters. I thought of defining as varchar but that is not one of the datatype supported in global variable declaration. I defined it as string but then it can only have length of 255. Any idea of what datatype this variable should be? Thanks,
Nilay.

You may want to refer to this previous post on a related issue. http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=2010 In the meantime, I’ll keep checking. Nathan H.O.
Moderator
SQL-Server-Performance.com

I guess the next question would be what you need a global variable of that length for? Not saying its not necessary, just wondering if there is another way of doing it. Chris

I think holding text datatype for global variable will prove costly in terms of performance, so I guess using global temp table would in this case. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

The reason I require is that I am passing Quote Number & Detail from SQL Server to AS400. As400 requires flat file. I am using Transform Data Task to generate txt file. Only some of the Quote Number are passed AS400, this logic is currently written into ActiveX script in Transform Data Task. One of the task is to also store all the quote numbers, which goes to AS400, in log table. And to do that I am currently using global variable which is populated with all the quote numbers seperated by comma(,). But then I ran out of length as it was declared as string. Can you tell me more about global temp table? And how to populate into global temp table in the above given scenario.. I guess I would have to go for Data Driven Query. Am I right? Please let me know.

Yes use data driven query. Sample script by Nigel fromhttp://www.nigelrivett.net/ImportTextFiles.html here. HTH _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

]]>