May 30, 2009 at 12:35 pm
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.
May 30, 2009 at 2:48 pm
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:
May 30, 2009 at 2:55 pm
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.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply