October 5, 2007 at 4:19 am
I was asked to run the profiler to check for an performance problem in an VB.NET program and found an error the developer had missed:
The following is sent to SQLServer:
declare @p1 int
set @p1=NULL
declare @p3 int
set @p3=229378
declare @p4 int
set @p4=294916
declare @p5 int
set @p5=NULL
exec sp_cursoropen @p1 output,N'SELECT count(distinct(s.sickStartDate)) as nrSickReports, m.employeeID FROM empCalMain m, empCalOlap o, empCalSick s Where m.CompanyID = 1 and m.CompanyID = s.CompanyID and m.companyID = o.companyID and m.relMainID = o.relMainID and m.relSickID = s.relSickID and m.calDate >=''20070301 0:0:0'' and m.calDate 0 and s.totalCalSickDays <=42 Group By m.employeeID',@p3 output,@p4 output,@p5 output
select @p1, @p3, @p4, @p5
This results in the errors:
Could not create an acceptable cursor.
The cursor was not declared.
sp_opencursor is not documented, but I found a good description on:
http://jtds.sourceforge.net/apiCursors.html
@p must be the parameter @ccopt, that are the concurrency options.
294916=0x48004
But 0x8000 does not seem to be documented. So I guessed it had no meaning. I took the flag out and got 0x40004=262148.
With this value for @p4 the statement works in Management studio.
But what should I tell the developer to make the program work?
Is there something that causes this 0x8000 flag?
October 5, 2007 at 8:33 am
Not entirely sure but you should check client side code for any unhandled exception. The "missing cursor declaration" part is a hint that something is not working properly on the sequence of events.
* Noel
October 5, 2007 at 8:42 am
noeld (10/5/2007)
Not entirely sure but you should check client side code for any unhandled exception. The "missing cursor declaration" part is a hint that something is not working properly on the sequence of events.
Noel,
Do you think so?
I was pretty sure that "the missing cursor declaration" was caused by "could not create an acceptable cursor".
And that seemed to be caused by an illegal value for @p4
Anyway, it can do no harm to check for an exception. Thank you for your answer.
Joachim.
October 5, 2007 at 12:18 pm
joachim.verhagen (10/5/2007)
noeld (10/5/2007)
Not entirely sure but you should check client side code for any unhandled exception. The "missing cursor declaration" part is a hint that something is not working properly on the sequence of events.Noel,
Do you think so?
I was pretty sure that "the missing cursor declaration" was caused by "could not create an acceptable cursor".
And that seemed to be caused by an illegal value for @p4
Anyway, it can do no harm to check for an exception. Thank you for your answer.
Joachim.
Joachim,
I am not "sure" that is the case 100%. I would definitely look also for connections that are in a "bad" state. Those paramemters are "system" generated and is highly unlikely the place where the bug is ( although with M$ you never know 😉 )...
* Noel
October 7, 2007 at 9:14 pm
Just my two cents.
If you are establishing a cursor to work with data that can not be a set based query / update / ect. And the data set is not going to be large I would not use a cursor in the first place.
If your data set is going to be fairly small use a table variable. It is used by SQL as a table, gives you all the functionality of a cursor however it is easier to establish, you do not tear it down, you insert / update / delete / Ect. to it just as you would a physical table, it stays in memory (only for that transaction) so it is pretty fast.
If you do not know how to create one I will give you a simple example here, write me if you want more detail.
DECLARE @MyTable TABLE
(
Variable1 bigint
,Variable2 varchar(20)
,AnotherVariable decimal (2,10)
)
INSERT INTO @MyTable
(Variable1, Variable2, AnotherVariable)
( You can insert say values that are in declared variables already, and keep returning to it as you have more results)
VALUES (@Variable1, @Variable2, @AnotherVariable)
OR The insert can be records from a physical table like this:
SELECT Variable1, Variable2, AnotherVariable
FROM MyPhysicalTable
Ect...
Hope this helps.
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
October 17, 2007 at 7:26 am
There seems nothing strange in the source.
I found something new. If I take the sum out the statement, like below it works fine:
declare @p1 int
set @p1=0
declare @p3 int
set @p3=229378
declare @p4 int
set @p4=294916
declare @p5 int
set @p5=0
exec sp_cursoropen @p1 output,N'SELECT s.sicknessPercentage/ 100 * o.parttimeFactor as nrSickDays FROM empCalMain m, empCalOlap o,
empCalSick s Where m.CompanyID = 1 and m.CompanyID = s.CompanyID and m.companyID = o.companyID and m.relMainID = o.relMainID and m.relSickID
= s.relSickID and m.calDate >=''20050901 0:0:0'' and m.calDate 0 and
s.totalCalSickDays 2',@p3 output,@p4 output,@p5 output
select @p1, @p3, @p4, @p5
Replacing it with avg also goes wrong.
The statement without a cursor works fine in management studio.
Does somebody knows about a problem with aggregate functions in cursors?
October 17, 2007 at 7:40 am
Those parameters mean a dynamic cursor. I changed @p2 that is the scroll-options to static or forward-only and @p4, that is the concurrency option to read-only it works fine. I guess the developer can take it from there. Thanks for all the help.
Joachim.
October 17, 2007 at 8:30 am
joachim.verhagen (10/17/2007)
Those parameters mean a dynamic cursor. I changed @p2 that is the scroll-options to static or forward-only and @p4, that is the concurrency option to read-only it works fine. I guess the developer can take it from there. Thanks for all the help.Joachim.
Wow, excellent finding. Now I wonder how those parameters were generated incorrectly from VB ...? :hehe:
* Noel
October 17, 2007 at 8:40 am
I understood that that is what the code asked for. Don't blame VB. And I do not really wish to know how many dynamic cursors are used where a fast_forward would suffice. 🙁
October 17, 2007 at 11:50 am
joachim.verhagen (10/17/2007)
I understood that that is what the code asked for. Don't blame VB. And I do not really wish to know how many dynamic cursors are used where a fast_forward would suffice. 🙁
I understand your point but if you find out that the developers can do something better ( like requesting a read-only cursor in their code ) you should help them and it will help you not to see this problem again.
Thanks for sharing,
* Noel
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply