Warnings in SQL Trace

Question

I am seeing a bunch of warnings repeated several time every hour in the trace files. the warnings are mostly shown under "Application Name" - "Internet Information Services". My developer says it is an IIS issue and if it were application they would have seen this in the app...see below some warnings. These are under the event "User Error Messages". What is the fix ?

Thanks.

A server cursor cannot be opened on the given statement or statements. Use a default result set or client cursor.

The cursor was not declared.

Incorrect syntax near the keyword 'IF'.

---

Ranga

Answer 1

Ok what you can do is start the sql server profiler and see if there is any custom stored proc is getting call

The issues seems to be some stored proc is using some cursor and that too is written in dynamic sql that there was no compile time here but it is actually coming at run time

---

Mark this post as answer if this resolves your issue.

---

Everything about SQL Server | Experience inside SQL Server -Mohammad Nizamuddin

Answer 2

It seems there is code related problem that is causing cursor  error.

As Mohammad Nizamuddin says run an SQL server profiler & monitor the error. It will help you in getting more details about error like database, user etc.

---

Regards,
Rohit Garg
(My Blog)
This posting is provided with no warranties and confers no rights.
Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread.

Answer 3

I setup an extended event to trap these messages and here is the sql text: 

(@P1 varchar(25)) EXEC EDWebPageSessionKeySelectAsp @P1  



Here are few error messages:

Incorrect syntax near the keyword 'SET'.
A server cursor cannot be opened on the given statement or statements. Use a default result set or client cursor.
The cursor was not declared.
Incorrect syntax near the keyword 'IF'.


Here is the SP code:

CREATE PROCEDURE dbo.EDWebPageSessionKeySelectAsp  
@SessionKey AS VARCHAR(25)  
AS  
SET NOCOUNT ON  
BEGIN  
 SELECT SessionKey FROM [dbo].[AccessKey] WHERE SessionKey = @SessionKey  
END  
RETURN 0  

Looks like the way this SP is called in the app is not correct. Will update once I hear from the developers as how this sp is called. This is called using ADO.

---

Ranga

Answer 4

Edit you SP like below & try again...

SET NOCOUNT ON 

GO

CREATE PROCEDURE dbo.EDWebPageSessionKeySelectAsp 
@SessionKey AS VARCHAR(25) 
AS 
BEGIN 
SELECT SessionKey FROM [dbo].[AccessKey] WHERE SessionKey = @SessionKey 
END 
RETURN 0 

---

Regards,
Rohit Garg
(My Blog)
This posting is provided with no warranties and confers no rights.
Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread.

Answer 5

Rohit,

Do you mean SET NOCOUNT ON should be outside of the SP, is all the errors dues to putting that inside of the SP ? Just curious.

Thanks,

Ranga

---

Ranga

Answer 6

yes, you got it right.

SET NOCOUNT ON should be outside of the SP

---

Regards,
Rohit Garg
(My Blog)
This posting is provided with no warranties and confers no rights.
Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread.

Answer 7

yes, you got it right.

SET NOCOUNT ON should be outside of the SP

With due respect, Could you please let me know Why it should be outside the sp? The below works neatly. Moreover, SET NOCOUNT ON should be in the procedure inside to restrict the messages. Correct me if am wrong, am just curious.

Create proc sp1
as
SET NOCOUNT ON
Begin
	Print 'hi'
End

---

Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 8

It is common practice to specify SET NOCOUNT ON inside a stored procedure and there should be no problem in doing so.  I haven't been able to duplicate this so I suspect it is related to how the proc is being executed on the client side.  Perhaps you can reach out to the developers and provide a code snippet that shows how this proc is being run.  I don't think the error/warning is anything to be concerned about but it would be nice to know the underlying cause and remediate.

---

Dan Guzman, SQL Server MVP, http://www.dbdelta.com

Answer 9

Here are few error messages:

Incorrect syntax near the keyword 'SET'.
A server cursor cannot be opened on the given statement or statements. Use a default result set or client cursor.
The cursor was not declared.
Incorrect syntax near the keyword 'IF'.

The error messages do not seem to relate to the stored procedure code you have shared with us.

How did you determine that each of these error messages were produced by the proc?

is dbo.AccessKey a table, view or function? Can you find its dependencies?

---

George
blog | twitter