ASP - SQL - Server Cursor Error

[Today]

We are running ASP connecting to an SQL server 7 database.

We are getting the following error message from the web server when a stored procedure is called.


Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]A server cursor is not allowed on a stored procedure with more than one SELECT statement. Use a default result set or client cursor.
/updown/main_menu.asp, line 113

Any ideas/help would be appreciated ?

The stored procedure is as follows.

Alter Procedure sp_upload_services (@TPLocationCode varchar(35))
As
SELECT OrigID, RecipID, FTATR.MessageType, EANCode, TPName, DisplayText, URLLocation
FROM dbo.FTATR,dbo.TProfile,dbo.WEBPages
WHERE TProfile.EANCode = FTATR.RecipID
AND FTATR.OrigID = @TPLocationCode
AND FTATR.MessageType = WEBPages.MessageType


return (0)

The ASP file contains the following details.

Dim cmdOCProfiles
Dim rsUploadPages

'Define a command object for sp_get_ocprofiles stored procedure.
Set cmdOCProfiles = server.CreateObject("ADODB.Command")
Set cmdOCProfiles.ActiveConnection = Conn
cmdOCProfiles.CommandText = "sp_upload_services"
cmdOCProfiles.CommandType = adCmdStoredProc
cmdOCProfiles.CommandTimeout = 0


'UserName parameter
Set prmTPLocationCode = server.CreateObject("ADODB.Parameter")
prmTPLocationCode.Type = adVarChar
prmTPLocationCode.Size = 35
prmTPLocationCode.Direction = adParamInput
prmTPLocationCode.Value = Session("USER")
cmdOCProfiles.Parameters.Append prmTPLocationCode


'Create recordset by executing the command.
Set rsUploadPages = server.CreateObject("ADODB.Recordset")
rsUploadPages.Open cmdOCProfiles
rsUploadPages.MoveFirst
'-----------------------------------------------------------------

'---------------CALL STORED PROCEDURE FOR DOWNLOADS----------------
' SEND TRADING PARTNERS EANCODE TO
' DATABASE AND RETRIEVE TRADING
' RELATIONSHIP DETAILS FOR DROP DOWN MENUS
Dim cmdDownloads
Dim rsDownPages

'Define a command object for sp_get_ocprofiles stored procedure.
Set cmdDownloads = server.CreateObject("ADODB.Command")
Set cmdDownloads.ActiveConnection = Conn
cmdDownloads.CommandText = "sp_download_services"
cmdDownloads.CommandType = adCmdStoredProc
cmdDownloads.CommandTimeout = 0


'UserName parameter
Set prmLocationCode = server.CreateObject("ADODB.Parameter")
prmLocationCode.Type = adVarChar
prmLocationCode.Size = 35
prmLocationCode.Direction = adParamInput
prmLocationCode.Value = Session("USER") '
cmdDownloads.Parameters.Append prmLocationCode



'Create recordset by executing the command.
Set rsDownPages = server.CreateObject("ADODB.Recordset")
rsDownPages.Open cmdDownloads
rsDownPages.MoveFirst ********************** line 113

[Johnny101]

the "Return(0)" statement acts like a SELECT statement. I'm curious as to why it's there at all. If you get data back you know the query executed okay, i'd say if you get an error -only then return something (-1, perhaps). if you need to have that return value, then try setting NOCOUNT ON as one the first thing the proc does - that nastly little NOCOUNT can cause all kinds of problems. Usually they are involved in using virtual temp tables (#tablename) in stored procs. sometimes that return value or a recordcount (done by SQL Server) will come back as a second recordset causing problems.

try it without the Return statement and go from there.

hope this helps,

John

John Pirkey
MCSD
http://www.ShallowWaterSystems.com
http://www.stlvbug.org