September 28, 2003 at 7:18 pm
I have this command (unfortunately can't remember where from, but somewhere on the Internet). It's part of a daily checks system. It works great from my own desktop through a firewall, onto most of my servers:
--------------------------------------------
select convert (smallint, req_spid) As spid, convert(char(10),db_name(rsc_dbid)) As dbid,
convert(char(20),object_name(rsc_objid)) As ObjName,
substring (v.name, 1, 4) As Type, substring (u.name, 1, 8) As Mode, substring (x.name, 1, 5) As Status,
convert(char(8),P.cmd) CMD, convert(char(20),P.program_name) PRG, count(*) 'num'
from master.dbo.syslockinfo, master.dbo.sysprocesses P, master.dbo.spt_values v, master.dbo.spt_values x,
master.dbo.spt_values u where master.dbo.syslockinfo.req_spid *= P.spid and master.dbo.syslockinfo.rsc_type =
v.number and v.type = 'LR' and master.dbo.syslockinfo.req_status = x.number and x.type = 'LS' and
master.dbo.syslockinfo.req_mode + 1 = u.number and u.type = 'L' and substring (v.name, 1, 4) != 'DB' and
substring (u.name, 1, 8) != 'S' group by convert (smallint, req_spid) , convert(char(10),db_name(rsc_dbid)) ,
convert(char(20),object_name(rsc_objid)), substring (v.name, 1, 4), substring (u.name, 1, 8),
substring (x.name, 1, 5), convert(char(8),P.cmd), convert(char(20),P.program_name) order by P.spid
--------------------------------------------
The problem is that it fails on two servers, one on 8.00.679 and one on 8.00.760, although it works on all the sql7 servers and even works on another 8.00.679 server.
The succesful output is normally instantaneous and only 1-10 lines.
The failure message is:
--------------------------------------------
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionCheckForData (CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broken
--------------------------------------------
It doesn't really seem to be a connection problem, because if I just "select *" and remove the "group by" clause, what's left will run OK on the failing servers too.
On the other hand, I can't start a trace from my desktop to the failing servers either. I get a similar message:
--------------------------------------------
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionRead (recv()). (State 01000)(Code 10054)
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]General network error. Check your network documentation. (State 08501)(Code 11)
--------------------------------------------
Can anyone help?
September 29, 2003 at 4:00 am
I'm no expert but it looks like the connection timeout has been reached (or maybe just the query timeout). Increase the timeouts on the servers concerned (sorry, I don't know the best way to do it on your version).
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply