cursor issue in sql 2005 sp3

Question

cursor issue in sql 2005 sp3

Simon D Richards

Ten Centuries

Points: 1048
More actions
May 30, 2009 at 12:35 pm

#132708

HI
I am running the sql below:
declare @p1 int
set @p1=0
declare @p3 int
set @p3=1
declare @p4 int
set @p4=8193
declare @p5 int
set @p5=0
exec sp_cursoropen @p1 output,N'EXECUTE proc_MedSurv -473821157',@p3 output,@p4 output,@p5 output
select @p1, @p3, @p4, @p5
which produces the error below:
Executing SQL directly; no cursor.
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
This seems to only affect sql 2005 sp3, running the same query on an sp2 2005 instance works ok.
alternatively running the same query using a select instead of exec works as well:
declare @p1 int
set @p1=180150007
declare @p3 int
set @p3=1
declare @p4 int
set @p4=1
declare @p5 int
set @p5=1
exec sp_cursoropen @p1 output,N'SELECT coll_1, coll_2, coll_3, coll_site_list_bus_phone FROM COLLECTION WHERE coll_id = -1211926656',@p3 output,@p4 output,@p5 output
select @p1, @p3, @p4, @p5
is there a work around for this? the sql is all in legacy apps so not easy to change!
thanks.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

You might want to look at this article

FIX: You may receive error messages when you use the sp_cursoropen statement to open a cursor on a user-defined stored procedure in SQL Server 2005

at:

http://support.microsoft.com/kb/913371

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Simon D Richards Ten Centuries Points: 1048 More actions · Answer 2

Thanks, I had come across this article in my searches but it seems unrelated to what we are experiencing here. I was wondering whether it was permissions on the tempdb but having applied full permissions for a test user I was still getting the same error.