DTS: Transform Task > Stored Procedure > Temp Table error
-
Hi all I've got a problem that's driving me a bit mad, so any help gratefully received! I've got a Data Transformation Task that is calling a parameterised stored procedure: exec [myDB].[dbo].[vlGetOutputFile] 'Sep 28 2007', 6, 86 Within that stored procedure, I'm creating and dropping a couple of temporary tables and then returning the result set. If I click preview, I get the first 200 lines I'd expect, but as soon as I go to transformations I get the error 'Invalid object name #temp' and no columns to select from. What am I doing wrong? Thanks in advance Ben
-
Hi all I've got a problem that's driving me a bit mad, so any help gratefully received! I've got a Data Transformation Task that is calling a parameterised stored procedure: exec [myDB].[dbo].[vlGetOutputFile] 'Sep 28 2007', 6, 86 Within that stored procedure, I'm creating and dropping a couple of temporary tables and then returning the result set. If I click preview, I get the first 200 lines I'd expect, but as soon as I go to transformations I get the error 'Invalid object name #temp' and no columns to select from. What am I doing wrong? Thanks in advance Ben
-
Hi all I've got a problem that's driving me a bit mad, so any help gratefully received! I've got a Data Transformation Task that is calling a parameterised stored procedure: exec [myDB].[dbo].[vlGetOutputFile] 'Sep 28 2007', 6, 86 Within that stored procedure, I'm creating and dropping a couple of temporary tables and then returning the result set. If I click preview, I get the first 200 lines I'd expect, but as soon as I go to transformations I get the error 'Invalid object name #temp' and no columns to select from. What am I doing wrong? Thanks in advance Ben
The reason you are getting this error is because you cannot use temp tables with DTS packages. This is because DTS packages cannot resolve temp tables in design time. This is a 'feature' of SQL Server 2000. My advice is not to use temp tables with the DTS package. What I do is pipe the result of the stored procedure to a table then call the DTS package from within a stored procedure (using the freshly populated table in the DTS package). You can then drop the table or delete the rows once the DTS package has been run. Alternatively if you really want to use temp tables check out this link where I explain how to get around this problem. The solution is not pretty. Basically my advice is to avoid temp tables with DTS packages - this is a documented 'feature'. I hope this helps. -- modified at 16:32 Tuesday 23rd October, 2007 -- modified at 16:33 Tuesday 23rd October, 2007
You always pass failure on the way to success.
-
Presumably SQL Server. The temp tables are created in the stored proc correct? If so why are you dropping them?
Yes, SQL Server. Fair point, just my natural tidiness :-) Anyway, dropping the drops doesn't solve the issue.... Cheers Ben
-
The reason you are getting this error is because you cannot use temp tables with DTS packages. This is because DTS packages cannot resolve temp tables in design time. This is a 'feature' of SQL Server 2000. My advice is not to use temp tables with the DTS package. What I do is pipe the result of the stored procedure to a table then call the DTS package from within a stored procedure (using the freshly populated table in the DTS package). You can then drop the table or delete the rows once the DTS package has been run. Alternatively if you really want to use temp tables check out this link where I explain how to get around this problem. The solution is not pretty. Basically my advice is to avoid temp tables with DTS packages - this is a documented 'feature'. I hope this helps. -- modified at 16:32 Tuesday 23rd October, 2007 -- modified at 16:33 Tuesday 23rd October, 2007
You always pass failure on the way to success.
Thanks for the pointer. Seems most odd that that this isn't supported - is there a reason they opted not to allow this? Cheers Ben
-
Thanks for the pointer. Seems most odd that that this isn't supported - is there a reason they opted not to allow this? Cheers Ben
Bjohnson33 wrote:
Seems most odd that that this isn't supported
Yes this is what we call a 'bug' and microsoft calls a 'feature':laugh: What I tend to do nowadays is always pipe the results into a table then run the DTS from one table. This has a tendency to simplify things a lot as processes, including file copying etc, are visible within the stored procedure and not hidden within the DTS package (I avoid the DTS vb scripting feature as although it looks good - try debugging a DTS package with all that code hidden away in various places).
You always pass failure on the way to success.