Closed recordset error from ADO

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Closed recordset error from ADO

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-04-08 : 08:33:02

Bob writes "Windows XP client
SQL Server 8.0 on Windows Server 2003

We are calling a SP from ASP code.
This SP creates a temp table and calls a second SP that uses dynamic sql to return the insert data for the temp table.
These SPs work as expected from Query Analyzer.
The only way we get it to work in ADO is by moving the
'insert into temp table' from the first SP to the
dynamic sql in the second SP.
This change has hurt performance noticeably.

For example:
start.asp
Connection conn;
' establish connection...
sqlStmt = 'wb_sp_GetAllData parm1, parm2, parm3'
rs.Open sqlStmt, conn

' test rs.State always returns as 0=closed
' and we get the closed recordset error

wb_sp_GetAllData
create table #finalResults (...)
while (u < 10)
begin
insert into #finalResults (x,y,z)
exec wb_sp_GetData parm1, parm2, parm3
end

wb_sp_GetData
-- MUST USE Dynamic SQL to get select, shown is not the real
-- statement because it is too long
set @finalIA = 'select x,y,z from myTable'
exec ('exec sp_executesql N''' + @finalIA + '''')

We tried putting 'SET NOCOUNT ON' in the SPs and ADO calls. (KB article)
We updated MDAC from 2.7 to 2.8 (post in a newsgroup) because
of warnings about temp table row size.
We did simple ASP and ASP.NET pages to call only this.
IT WORKS IN .NET but not in ASP.

The only way we got it to work in ASP ADO is by changing the SPs to:
wb_sp_GetAllData
create table #finalResults (...)

while (x < 10)
begin
exec wb_sp_GetData
end

wb_sp_GetData
--- blah blah blah
-- MUST USE Dynamic SQL to get select, shown is not the real
-- statement because it is too long
set @finalIA = 'insert into #finalResults (x,y,z) '
+ 'select x,y,z from myTable '
exec ('exec sp_executesql N''' + @finalIA + '''')

This suggests the real problem has to do with using the dynamic sql.
We've noticed before that dynamic alters to tables in an SP will not
show up in the SP without using dynamic SQL to select from the altered
table.

Can anyone confirm this?
Any ideas what is really going on.

Thanks,"

nr
SQLTeam MVY

12543 Posts

Posted - 2005-04-09 : 12:00:03

>> Can anyone confirm this?
Yes
When you run an SP the query plan is created. This will include the structures of tables. If the table exists then that will be use as the structure. If it does not then the create statement is used (but not alter statements) so whenever the sp accesses the table it will use that structure - any columns added or changed durin the sp can cause errors.
Dynamic sql is a seperate batch and the query plan is created at execution using the structures available at that time.
see
http://www.mindsdoor.net/SQLTsql/AccessTempTablesAcrossSPs.html

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources