ExecuteSQLTask (what is the max length of SQLStatement)

[SQLDTS2000]

Hi,
I've tried typing 1000 insert statements (e.g. 1000 statements of insert #temp_tst values '111111') in 1 ExecuteSQLTask that connects to a Sybase ASE ODBC Driver (Other ODBC Data Source). During run time upon clicking the Execute package it appeared that only 61 rows was inserted into the temporary table in Sybase. Do you have any idea why this happened? What is the maximum length of the SQLStatement? Do we have any solution on this problem so that I can used only one connection that connects the SQL Server 2000 package to Sybase?

Thanks and Regards,
Charmine Azajar

charmine.azajar@oocl.com

 

[osjohnm]

Hi

There aren't any limitations on statements that I know of.
It might be your #temp table, since temp tables are only valid while the connection is active. Are you splitting the inserts into batches or are you trying to insert all of them at once?

You can try and put GO statements between each insert but that might kill the connection for the temp table.

Do you always insert into the same table?
Why are you using a temp table in Sybase?

Depending on your answer there might be some options.

John

 

[SQLDTS2000]

Hi John,
Yes, I'm inserting them all at once using the same temp table. I've use the recordset so that I can get all the data in the temporary table and loop through it by using the Active X script task of the ExecuteSQLTask to be able to produce the 1000 insert statements.

I'm running sybase stored procedures in one Sybase server that transfers the data to SQL server through using the data pump. But I have to insert again the temp table to another Sybase server (or another Sybase connection same Sybase server) so that when I run a stored procedure (using the Data Pump) the temp table will be visible in that Sybase server.


Thanks a lot the GO statement worked. =)

Regards,
Charmine