Problem with temporary table and ado.net
hi all i have created a store procedure that works fine on sql server 2005 exprees, it uses the temporary table
#TEMP...the sproc is something like this:
select name
into #TEMP
from TableName
...some operation with temporary Table #TEMP
drop #TEMP
So the problem is : when i try to call the store procedure from a dataAdapter from a visual studio project i have the following error:
name of object '#TEMP' not valid.
....who can help me to solve this problem?
[671 byte] By [
Bule] at [2007-12-24]
You should do everything with the temporary table within your stored procedure code. INSERT / SELECT and cleaning the house, in the meaning of dropping the table at the end. temporary tables are sessin based on will not be persisted for data retrieval.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
i am sorry but i don't understand...i do everything inside the store procedure and it ends with the drop table #TEMP. the sproc works inside sql server but it gives errors in ado.net or better in ado.net i can retrive the data but the sqladpter isn't able to make up a type dataset...
i have build up an untyped dataset which collects the data from the sproc and it works....but i'd like to know because i have this problem...
Did anyone find an explanation/solution for this? I'm having the same problem. Apparently VB2005 doesn't recognise temporary tables even if they are defined (and closed) inside the stored procedure....
Any help appreciated. Thanks.
#LS at 2007-10-8 >
Ok, I think I was lucky. I found a simple solution googling around.
Just add this line in the begining of your stored procedure:
SET FMTONLY OFF; SET NO_BROWSETABLE OFF;
Apparently the problem is on the no_browsetable beeing turned to on by default, before the SP starts.
#LS at 2007-10-8 >
