I am getting burned in every direction I take to get around this problem.

I have a loop in a Stored Procedure that reads records from a table to build
the SQL string to create the structure of another table. In this loop I am
concantinating the field names together.

Doing this I CANNOT exceed 4000 characters regardless of what type of string
I use.

According to the SQL manual a "char" or "varchar" has a limitation of 8000
characters. However using these types I CANNOT get passed 4000. Interestingly
enough if I test for the 4000 max and begin a new string when it has been
exceeded, outside the loop, I CAN concantinate the two strings together
successfully.

The same problem exists in the passing of OUTPUT parameters in an Execute
SQL object for DTS. Even after I have successfully concantinated the strings
with the workaround, when I attempt to pass them as OUTPUT the strings get
truncated. Even though I have specified varchar(8000).

Additionally I am having a similar problem in an ActiveX Script object where
I read the Rowset into a Global Variable. I need to loop thru the Rowset and
build some strings that contain the Field names and another that contains the
Field size. I CANNOT seem to get passed 1400 characters using local
variables. Worse yet the trick I used in the stored procedure does NOT work
here.

For an Enterprise Solution, I find the SQL 2000 DTS to be very limiting.

Am I missing something here?

I found a workaround...

Oddly enough it was something that came to me after reading Allan Mitchell's
response to a completely different problem I posted earlier.

I am not going to bother with any Global Variables or even the Dynamic
Properties object. Instead I am going to write data directly to the
properties of the object I need to affect.

Within the ActiveX Script I referenced the object I needed to work with and
then populated it's properties with the data I could not get into any of the
variables.

e.g. Dim TextFileSrc
Set TextFileSrc = DTSGlobalVariables.Parent.Connections("Text File
(Source)")
TextFileSrc.ConnectionProperties("Column Names").Value =
"Col001,Col002"
TextFileSrc.ConnectionProperties("Column Lengths").Value = "5,20"
TextFileSrc.ConnectionProperties("Number Of Column").Value = "2"

Apparently the properties of these objects can handle more than 4000
characters.

I will be more likely to use this approach more often and drop the Dynamic
Properties object as well as the use of Global Variables when I can helpl it.

Jamie

Glad I could help even if indirectly

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - SQL Server 2005 Integration Services.
www.Konesans.com


"Jamie Carper" <JamieCarper (AT) discussions (DOT) microsoft.com> wrote