Using #temp table with DTS 2

[RajD]

Hi,

In one of my SP, which runs 100s of lines, I am using a #temp table to store the results during the process.

At the end of the SP, a simple select statement returns rows from #temp using "Select * from #temp"

The procedure works fine when run in SQL-QA, but when I am trying to use this SP to create a DTS job, I get no results returned. I get this message
"No rowset was returned from the SQL statement".

If I replace the # table with a permanent temp table, it works fine.

I can't use a permanent temp table since the procedure may run in multiple instances and all will be using the same table then.

Any suggestion????

Thanks in anticipation,

RajD

 

[tlbroadbent]

Temporarily modify the Stored Procedure to create global instead of local temporary table. Make sure the SP doesn't drop the global table at the end - you can temporarily comment out the code that drops the table. Compile and save the SP.

The DTS designer needs the table to exist so it can get the metadata info. Execute the SP. While the global temp table exists, create the DTS package using a SQL statement that executes the SP as the data source. Save the package.

After saving the DTS package, drop the global temp table and modify the SP to create a local temp table. Test the DTS package. It should work.

Be aware that any future changes to the package will require you to go through the same process. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[RajD]

Thanks Terry, It works as I wished.

The only thing I had to do was to delete the original DTS package and create a new one with this method.

RajD