Operation is not allowed when the object is open.

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

General SQL Server Forums

New to SQL Server Programming

Operation is not allowed when the object is open.

Author

Topic

mdean9999
Starting Member

21 Posts

Posted - 2011-05-27 : 06:33:52

hi
I have a classic asp website written years ago sql server 2000. Upgrading the code to use a SQL Server 2008 database is introducing some unexpected issues!
I am having a problem with stored procedures. I open a connection on each page and then use that connection for the sql on that page.
All this works ok.

But if a page has a stored procedure then that call fails within the procedure.
-----
ADODB.Recordset error '800a0e79'
Operation is not allowed when the object is open.
-----------
I tried passing the connection as a parameter and that fails.

example stored procedure
------------------------
FUNCTION LoginCustomer(login, password)

Const adCmdStoredProc = 4
Const adInteger = 3
Const adCurrency = 6
Const adParamInput = 1
Const adParamOutput = 2
Const adExecuteNoRecords = 128

DIM cnnStoredProc
DIM cmdStoredProc
DIM rstStoredProc
DIM paramID

SET cnnStoredProc = Server.CreateObject("ADODB.Connection")
conn.open "PROVIDER=MSDASQL;DRIVER={SQL Server};SERVER=xx.xx.xx.xx;UID=xx;PWD=xxx;DATABASE=xxxxxx"

SET cmdStoredProc=Server.CreateObject("ADODB.COMMAND")
cmdStoredProc.ActiveConnection=cnnStoredProc

' Set up the arguments
cmdStoredProc.CommandText = "ValidateCustomer"
cmdStoredProc.CommandType = adCmdStoredProc
cmdStoredProc.parameters(1) = login
cmdStoredProc.parameters(2) = password
cmdStoredProc.Execute , , adExecuteNoRecords
LoginCustomer=cmdStoredProc.Parameters("@RetID").Value
SET cmdStoredProc=NOTHING
END FUNCTION
------------------------------
what is different about connections and stored procedure?
thanks
Michael

jfarrugia
Yak Posting Veteran

55 Posts

Posted - 2011-05-27 : 06:39:09

try the below (basically open connection after allocating it to command):.. if it doesnt work would be nice to get the line number where the error is being triggered.

a) you're using conn.open where i guess you should be using cnnStoredProc?

b)

SET cnnStoredProc = Server.CreateObject("ADODB.Connection")
SET cmdStoredProc=Server.CreateObject("ADODB.COMMAND")
cmdStoredProc.ActiveConnection=cnnStoredProc

cnnStoredProc.open "PROVIDER=MSDASQL;DRIVER={SQL Server};SERVER=xx.xx.xx.xx;UID=xx;PWD=xxx;DATABASE=xxxxxx"

Where software development knowledge meets the reader

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-27 : 08:01:04

Add SET NOCOUNT ON at the beginning of the Procedure.

Jim

Everyday I learn something that somebody else already knew

mdean9999
Starting Member

21 Posts

Posted - 2011-05-27 : 08:05:55

Many thanks jfarrugia
I should be able to do all the others procedures now.
What I missed was the referencing(!) of the objects
e.g.
cmdStoredProc.CommandText = "ValidateCustomer"
needed to be:
cmdStoredProc.CommandText = ="mdean_stampsforsale.ValidateCustomer"
I knew I had to reference database objects but this bit caught me out!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources