January 14, 2002 at 5:18 am
I'm using a DTS data transformation task to output data to a text file. The source is an SQL query which executes a stored procedure. The stored procedure uses a temporary table and finally selects the columns from it. In the DTS task, I get the message 'Invalid Object Name #tablename'. I would like to know if this is this a limitation of DTS or is there a workaround by still using temporary tables.
Paul Ibison
Paul Ibison
Paul.Ibison@replicationanswers.com
February 5, 2002 at 5:45 am
The end of the stored proc. commit the transaction and clear all temp tables and variables. Perhaps you'd better to use a ##tablename so it is stored in the general repository.
February 5, 2002 at 7:33 am
Thanks - that'll save us plenty of work.
The original sp was of the form:
select * into #temp from contacts.dbo.tblindustry
select * from #temp
and I've replaced it with:
select * into ##temp from contacts.dbo.tblindustry
select * from ##temp
This works, and is now editable in the DTS package. Actually, the original worked but was not editable in DTS when going from the source to destination tabs. This is still a little hazy to me, because presumably the resulting source recordset (from the select * from #temp) is cached in tempdb and should not still have a reference to the temp table. Anyway, you've solved the problem in the DTS designer, and I'll additionally modify the packages to remove the global temp table in a separate dynamic sql task.
Paul Ibison
Paul Ibison
Paul.Ibison@replicationanswers.com
February 5, 2002 at 4:48 pm
The problem is that the # temp table only exists for the duration of the process. Technically the source runs then the transformation but because the transformation is to SQL not in the same process it cannot see the # temp table. ## tables are visible to everyone and automatically go away when the conections that were touching it go away.
June 7, 2002 at 8:05 am
I am experiencing this same problem with the 'invalid object name' error using a DTS that calls a SP that ultimately selects * from a temp table.
I tried the solution that worked for you, using a global temp table and committing the transaction as well as specifying a temp table name that was guaranteed to be unique, but I am still experiencing this error when trying to setup the DTS package.
I was wondering, was there something else that you had to do to solve this problem?
Any help anyone can provide is much appreciated!
THANKS
Gina Reaves
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply