Using stored procedures as a source for a DTS Transformation

[LokiDba]

Hi,

I've currently got a problem with a DTS Package. I have a Data Source which is a stored proedure (that returns a resultset) which goes through a transfermation (just copying the field) to a Text file as the Destination.

The stored procedure works fine in SQL analyser and as a SQL Task in DTS, but when I use it for the source on the transformation, it previews fine with all the result, but when I run the transformation I get the follow error message.

Invalid Object: #ExpValues

#ExpValues is a temporary that that I'm using to do some work in the stored procedure.

Does anyone have any ideas why this is happening and how to solve it. I have my suspistions that its something to do the OLE DB for SQL Server because I get a different error when I use ODBC.

Any help much appreciated

Thanks in Advance

Andy H (LokiDBA)

 

[dbomrrsm]

#ExpValues - with it being temporary will only exist whilst the SP is running - try creating it as ##ExpValues and if that doesnt work try actually creating a perm table ExpValues in the SP - this of course can be truncated/deleted at the end of the DTS package.

[blue]DBomrrsm[/blue]

 

[LokiDba]

Thanks DBomrrsm,

I'll try the Global temporary table. Its just strange that it works fine in a SQL Task and through preview on the Source its self. Its only when I run the Transformation or Parse the Query that it gives me the error. If I remove the reference to the temporary table and as you say use a perminant table it all works fine. I don't think its a problem with my SQL just the way the DTS seems to work.

Just wondering if the a fix for it 2000 sp3 DTS or known work around

 

[LokiDba]

Thanks DBomrrsm,

I'll try the Global temporary table. Its just strange that it works fine in a SQL Task and through preview on the Source its self. Its only when I run the Transformation or Parse the Query that it gives me the error. If I remove the reference to the temporary table and as you say use a perminant table it all works fine. I don't think its a problem with my SQL just the way the DTS seems to work.

Just wondering if the a fix for it or known work around.

Thanks again


Andy

 

[dbomrrsm]

I think its just that a temp table only exists for the time the SP it is created in is running so as soon as the SP finishes the table deletes itself. So if you try to reference the table later in the DTS it wont exist whereas a global temp table of perm table will still be there.

[blue]DBomrrsm[/blue]