Cannot push data from DTS to Access 1

[RogueSuit]

I created a Stored Proc, which pulls data into a temp (named - #temp) table and then selects “all” from the #temp table. All of the data is returned as it should and the proc “runs” as it should – no exceptions or errors.

I then create a DTS package with a connection From SQL and a connection To an Access Database with a “Transformation Data Task (TDT)” between the two.

On the source for the “TDT” I use query: “EXEC ReportProc”. I can preview the query and get the data I saw when I ran the proc in the query analyzer. No problems.

I cannot create a destination or any transformations – i.e. I cannot send the data to Access. I get the following error:
Error Description: Invalid object name `#temp`

I figure this error occurs because this is a “temp” table and it is dropped after the PROC is run – however, that does not explain why I can get the data in the preview by exec the PROC but still cannot drop the data into access.

Is there any way to gather data from a Stored Proc, Exec it in a DTS package, and then export it to Access from the DTS

Thanks

 

[tlbroadbent]

Someone recommended using a global temporary table instead of a local temp table. Change #temp to ##temp in the SP and try that. I've not tried it, so no guarantees. Terry

X-) "Life would be easier if I had the source code." -Anonymous

 

[RogueSuit]

Terry thx for the suggestion. I tried that when the localized variable did not work. The globals did not work until I made the adjustments below.

You got me thinking about the usage of those variables though....and this is how I fixed it:

-I used Global vars (##)
-I found that when you build a DTS package and call a SP( with a global var) from a box other than the machine that the DTS is on - the Global Var will not be recognized.
-I used a Execute SQL Task (Drop Table ##x) in the DTS, instead of in the SP. Execute at the end of the package.

Thanks again for all of your help and patience.

 

[tlbroadbent]

Great! Glad you got it to work and shared the solution. Terry

;-) "When I hear somebody sigh, 'life is hard', I am always tempted to ask, 'compared to what'?" - Sydney Harris