Can't call SP drectly - SQL Server Forums

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)

Can't call SP drectly

Author

Topic

stocksr
Starting Member

13 Posts

Posted - 2001-07-16 : 10:15:28

The Error:

[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.

The Code Via ASP:


my_conn.execute "EXEC ModuleDocuments_ReNumberSort " + Module_ID

Where
my_conn is a valid connection
and Module_ID is a Valid Module_ID

The SP:


CREATE PROCEDURE ModuleDocuments_ReNumberSort 
@Module_ID int
AS 
SET NOCOUNT ON
-- Re Number the Modules
CREATE TABLE #ReOrder (
	NewOrder int NOT NULL IDENTITY (10, 10),
	ModuleDocument_ID int NOT NULL
)
INSERT INTO 	#ReOrder (ModuleDocument_ID)
SELECT 		ModuleDocument_ID AS ModuleDocument_ID
FROM 		tblModuleDocuments
WHERE		Module_ID = @Module_ID
ORDER BY 	SortOrder

UPDATE 	tblModuleDocuments
SET 	SortOrder = NewOrder
FROM 	#ReOrder,tblModuleDocuments
WHERE	#ReOrder.ModuleDocument_ID = tblModuleDocuments.ModuleDocument_ID

DROP TABLE #ReOrder
SET NOCOUNT OFF

This SP is called by another sp that is called via ASP. and works fine so why can't I call it direct?

Many Thanks for any Help
Robert

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources