Sproc with temp tables and input parameters causing trouble with dynamic recordsets - a tricky one!

From: John Hoge (jhoge123_at_yahoo.com)
Date: 03/24/04 

Date: 24 Mar 2004 05:40:21 -0800

In a web application I need to call a stored procedure that lists
items in a shopping cart. The Sproc is pretty complicated as it needs
to do compatibility checks between the items in the cart, and makes
use of temporary tables. It has one parameter, the order ID.

The problem lies when the recorset needs to be opened in any
non-forward only mode, such as static, dynamic or keyset. This is
needed because the checkout page contains script to compose an email,
so the recordset needs to be looped through twice.

It seems the recordset.moveFirst command will cause this error

Microsoft OLE DB Provider for ODBC Drivers error '80040e18'
Rowset position cannot be restarted.

in any situation where a stored procecure including a temporary table
is called, and a parameter is passed. Take a look at the Sproc below:

create proc usp_test(@id int) as
 select @@version as version into #test
select * from #test

Pretty simple. It makes a temp table and spits it out. When called by
the vbscript code below, it produces the above error. I've tried this
both on SQL7 and SQL2000.

set ordItemsRS = server.createObject("ADODB.RECORDSET")
with cmdorderItems
 .activeConnection = "DSN=SeaEagle"
 .commandType = adCmdStoredProc
 .Commandtext = "usp_test"
 .Parameters.Append .CreateParameter("@ord_id",adInteger,adParamInput,,25075)
end with

ordItemsRS.open cmdOrderItems, ,adOpenStatic ,adLockReadOnly
 
do while not ordItemsRS.eof
 response.write ordItemsRS("version") & "<br>"
 ordItemsRS.moveNext
loop
 
ordItemsRS.moveFIrst

Relevant Pages

  • Tricky Stored Procedure Problem - for SQL Experts only!
    ... In a web application I need to call a stored procedure that lists ... items in a shopping cart. ... The Sproc is pretty complicated as it needs ... ordItemsRS.open cmdOrderItems,,adOpenStatic,adLockReadOnly ...
    (microsoft.public.sqlserver)
  • Re: Tricky Stored Procedure Problem - for SQL Experts only!
    ... > In a web application I need to call a stored procedure that lists ... > items in a shopping cart. ... The Sproc is pretty complicated as it needs ...
    (microsoft.public.sqlserver)
  • Re: Retrieving SPROC return value using SPT
    ... You cannot use the parenthesis in calling the stored procedure. ... Your parameter must be declared as OUTPUT inside your stored procedure. ... It still not updated after return from> sproc. ... The "@" seems to be sufficient to tell> SQL>> that it's an output parameter. ...
    (microsoft.public.fox.vfp.queries-sql)
  • Re: SqlDataAdapter - can it be (re)used for 2 SELECT statements
    ... dataadapter for 2 different calls to the same sproc, ... the stored procedure parameter ... //create our DataAdapter object and use it to fill the dataset object ...
    (microsoft.public.dotnet.framework.aspnet)
  • Im so confused
    ... normally from Query Analyzer rather than calling it from any other code so I ... > system stored procedure sp_grantdbaccess checks that there is not an ... > there is not an enclosing transaction. ... Here's my sproc and the ...
    (microsoft.public.sqlserver.security)