December 9, 2005 at 4:46 am
I have written three Stored Procedures which work quite well when called from the SHAPE command. The data was extracted from existing tables within the database and all was fine.
However, I have had to change these SPs and they now construct the required data within temporary tables and SELECT everything from the temporary table at the end.
The new SPs work perfectly well when called from within Query Analyzer, and the format of the data (column names, definitions, sequence etc) are identical to the format they were in the previous version of the procedures. The difference now is that the program fails when attempting to run the shape command with the message "[Microsoft][ODBC SQL Server Driver][SQL Server] Invalid Object Name '#TempTable2" - the name of the temporary table in the second SP.
If I change the SHAPE to use the 2nd and 3rd SP the message then refers to '#TempTable3', i.e. it always rejects the second SP's temporary table.
If I remove the creation and dropping of the temporary table and use tables with the same name created within the database it works fine.
Does the SHAPE processing have a problem with SPs that use temporary tables?
Thanks,
Steve.
December 9, 2005 at 12:22 pm
Ain't familiar with SHAPE.
Does the same happen when you make a new stored procedures that calls the other 3? It might be using 3 different connections.
December 12, 2005 at 5:47 am
Thanks for the reply Jo, but I've had to take a different approach to solve this. I managed to find someone who has had the same problem and apparently SPs run from the SHAPE instruction can't contain temporary tables.
One intersting twist is that as I was forced to re-think and re-write the SPs I've actually made them simpler! It transpires that using the temporary tables was just a tad lazy.
Regards,
Steve.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply