Tricky Stored Procedure Problem - for SQL Experts only!
From: John Hoge (jhoge123_at_yahoo.com)
Date: 03/13/04
- Next message: Roust_m: "Replication error"
- Previous message: Dave Navarro: "Desperately Need Help with Query!"
- Next in thread: Andy: "Re: Tricky Stored Procedure Problem - for SQL Experts only!"
- Reply: Andy: "Re: Tricky Stored Procedure Problem - for SQL Experts only!"
- Messages sorted by: [ date ] [ thread ]
Date: 13 Mar 2004 06:07:12 -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
- Next message: Roust_m: "Replication error"
- Previous message: Dave Navarro: "Desperately Need Help with Query!"
- Next in thread: Andy: "Re: Tricky Stored Procedure Problem - for SQL Experts only!"
- Reply: Andy: "Re: Tricky Stored Procedure Problem - for SQL Experts only!"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
