Time out errors - 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 Administration (2000)

Time out errors

Author

Topic

harshal_in
Aged Yak Warrior

633 Posts

Posted - 2004-05-10 : 01:04:34

Hi,
We have an application connects to the database server, recently at times it gives "Error in OpenConnection ()
TimeOut Expired" Error.
I tried to put profiler and found that
"Error: 16945, Severity: 16, State: 1"
"Error: 16937, Severity: 16, State: 1"
are currently occuring on the server at some regular intervals and at that time connection timeout expired error
comes.
the above two cursors talk about cursors, but the procedures that the application is trying to use does not have any
cursors.
any help would be highly appreciated.
regards,
Harshal.

He is a fool for five minutes who asks , but who does not ask remains a fool for life!<N>

http://www.sqldude.4t.com

harshal_in
Aged Yak Warrior

633 Posts

Posted - 2004-05-10 : 02:04:23

Ok I know now that 16937 refers to "A server cursor is not
allowed on a remote stored procedure or stored procedure
with more than one SELECT statement. Use a default result
set or client cursor."
I checked the stored procedure and
there is one SELECT Statement in it.
Here is the RPC that calls the stored procedure:

declare @P1 int
set @P1=0
declare @P2 int
set @P2=557064
declare @P3 int
set @P3=98305
declare @P4 int
set @P4=0
exec sp_cursoropen @P1 output, N'exec usp_UserGetInfo ''8185564400''', @P2 output, @P3 output, @P4 output
select @P1, @P2, @P3, @P4

Here is the Stored Procedure usp_UserGetInfo
create proc usp_UserGetInfo
(@MailboxID nvarchar(16)) AS

/*IF EXISTS (SELECT MailboxID FROM Users WHERE MailboxID = @MailboxID)*/
BEGIN

SELECT US.UserID,
US.MailBoxID,
CASE US.PIN WHEN '000000' THEN '' ELSE US.PIN END AS PIN,
US.FName,
US.LName,
US.BirthMonth,
US.BirthDay,
US.BirthYear,
US.ZipCode,
US.ANI,
US.UserProfile,
US.ClassID,
UD.AccessCount,
UD.DCRAppState,
UD.UpdateDateTime,
UD.SVPNClientID,
UD.PrefferedApp,
US.ContactDataChanged
FROM Users US

LEFT JOIN User_Details UD
ON US.UserID = UD.UserID

WHERE US.MailboxID = @MailboxID
AND ISNULL(US.DisableFlag,0) <> 1
AND LEN(US.MailboxID) <> 0
AND US.MailboxID IS NOT NULL

END

But I am still getting error 16937 followed by 16945.

Any thoughts?

Regards,
Harshal.

He is a fool for five minutes who asks , but who does not ask remains a fool for life!<N>

http://www.sqldude.4t.com

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-05-10 : 23:46:31

It's telling you what your error is isn't it??? You have two select statements in that procedure. You're calling it from a cursor. Put your "cursor information" into a temp table. Use a while loop to loop through it and execute the stored procedure with each set of information. You shouldn't have the problem then because you won't be using the cursor.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources