General network error
Yuval
We have developed downloading system which synchronize data between several
Remote MSDE 1.0 server
and Central SQL Server 2000.
The system has been working for 3 month fine but in the last 2 weeks we
encounter a problem on
one of the MSDE server in a specific store procedure. the error is "Server:
Msg 11 , Level 16, State 1, Procedure UpdatetblLog, Line 80 General network
error. Check your network documentation."
Here are the details
Enviorment:
11 - NT 4 SP 6a Servers running MSDE 1.0 SP 2
1 - Windows 2000 Professional SP 3 running SQL Server 2000 SP2
Located on a distributed wan with 200 hundred stations and a couple of more
servers.
Application Architcture:
On the Windows 2000 there is VB 6 Client which makes scheduele calls ( every
night) to intiate Download process.
The calls are made to VB 6 component Running under COM+ inviorment with no
transaction.
The first VB 6 component intiates seperate transactional download process
for each remote MSDE server through second VB 6 component which loops
through coupled insert / update store procedure for each table in the
database.
The store procedure for each table are basicly the same inset new recordes
to the central database according to Insert flag field in the remote
database and updates the insert flag field accordingly.
the update process works much the same.
The second component keeps the download process for each remote server
atomic.
here is an example code for an insert store procedure
CREATE PROCEDURE InserttblLog (@Region AS TINYINT , @ServerName AS
NVARCHAR(100), @BatchNum AS INT
,@TotalInsertedRows AS INT OUTPUT ) AS
DECLARE @InsertString AS NVARCHAR(2000)
DECLARE @RemoteExecSQL AS NVARCHAR(50)
DECLARE @UpdateBatch AS NVARCHAR(500)
SET XACT_ABORT ON
SET @InsertString = N'INSERT tblUnionLog (
Region,
RecID,
EventID,
OpenTime,
Counter,
Phone,
TownCode,
Address,
HouseNum,
Entrance,
Flor,
Apartment,
MainComplain,
Name,
Age,
Emergency,
CallCode,
MedicCode,
RecieveCall,
DrvAnnounce,
Departion,
ArivalToPlace,
Evacuation,
AtDestenation,
Vacancy,
Home,
DestCode,
[Continue],
KM,
Comment ,
Duplicate,
ToranCode,
LauncherCode,
AmbReg,
Amb,
Station,
Clr,
IndexAmb,
White,
DriverCode,
ExtraCode,
StandBy,
LightInjured,
MedumInjured,
SevereInjured,
Ex,
IndexStation,
CallerRegionNum,
Caller,
EmergDest,
BeepManag,
BeepTeam,
BeepCaller,
BeepTime )
SELECT ' + CONVERT ( NVARCHAR(4) ,@Region) + N' ,
RecID,
EventID,
OpenTime,
Counter,
Phone,
TownCode,
Address,
HouseNum,
Entrance,
Flor,
Apartment,
MainComplain,
Name,
Age,
Emergency,
CallCode,
MedicCode,
RecieveCall,
DrvAnnounce,
Departion,
ArivalToPlace,
Evacuation,
AtDestenation,
Vacancy,
Home,
DestCode,
[Continue],
KM,
Comment ,
Duplicate,
ToranCode,
LauncherCode,
AmbReg,
Amb,
Station,
Clr,
IndexAmb,
White,
DriverCode,
ExtraCode,
StandBy,
LightInjured,
MedumInjured,
SevereInjured,
Ex,
IndexStation,
CallerRegionNum,
Caller,
EmergDest,
BeepManag,
BeepTeam,
BeepCaller,
BeepTime
FROM ' + @ServerName + N'.dbo.tblLog
WHERE BatchFirst IS NULL '
EXEC sp_executesql @InsertString
SET @TotalInsertedRows = @@ROWCOUNT
SET @RemoteExecSQL = @ServerName + N'.dbo.sp_executesql'
SET @UpdateBatch = N'UPDATE tblLog SET BatchFirst = ' + CONVERT (
NVARCHAR(10) ,@BatchNum) + N' ,BatchLast = ' + CONVERT ( NVARCHAR(10)
,@BatchNum) + ' WHERE BatchFirst IS NULL'
EXECUTE @RemoteExecSQL @UpdateBatch
----------------------------------------------------------------------------
---------------------------------------------------
here is an example code for an update store procedure
CREATE PROCEDURE UpdatetblLog ( @Region AS TINYINT ,@ServerName AS
NVARCHAR(100) , @BatchNum AS INT
,@TotalUpdatedRows AS INT OUTPUT ) AS
DECLARE @UpdateString AS NVARCHAR(2000)
DECLARE @UpdateBatch AS NVARCHAR(500)
DECLARE @RemoteExecSQL AS NVARCHAR(50)
/*SET TRANSACTION ISOLATION LEVEL SERIALIZABLE*/
SET XACT_ABORT ON
SET @UpdateString = N'UPDATE tblUnionLog SET
EventID = Region.EventID ,
OpenTime = Region.OpenTime ,
Counter = Region.Counter ,
Phone = Region.Phone,
TownCode = Region.TownCode,
Address = Region.Address,
HouseNum = Region.HouseNum,
Entrance = Region.Entrance,
Flor = Region.Flor,
Apartment = Region.Apartment,
MainComplain = Region.MainComplain,
Name = Region.Name,
Age = Region.Age,
Emergency = Region.Emergency,
CallCode = Region.CallCode,
MedicCode = Region.MedicCode,
RecieveCall = Region.RecieveCall,
DrvAnnounce = Region.DrvAnnounce,
Departion = Region.Departion,
ArivalToPlace = Region.ArivalToPlace,
Evacuation = Region.Evacuation,
AtDestenation = Region.AtDestenation,
Vacancy = Region.Vacancy,
Home = Region.Home,
DestCode = Region.DestCode,
[Continue] = Region.[Continue],
KM = Region.KM,
Comment = Region.Comment,
Duplicate = Region.Duplicate,
ToranCode = Region.ToranCode,
LauncherCode = Region.LauncherCode,
AmbReg = Region.AmbReg,
Amb = Region.Amb,
Station = Region.Station,
Clr = Region.Clr,
IndexAmb = Region.IndexAmb,
White = Region.White,
DriverCode = Region.DriverCode,
ExtraCode = Region.ExtraCode,
StandBy = Region.StandBy,
LightInjured = Region.LightInjured,
MedumInjured = Region.MedumInjured,
SevereInjured = Region.SevereInjured,
Ex = Region.Ex,
IndexStation = Region.IndexStation,
CallerRegionNum = Region.CallerRegionNum,
Caller = Region.Caller,
EmergDest = Region.EmergDest,
BeepManag = Region.BeepManag,
BeepTeam = Region.BeepTeam,
BeepCaller = Region.BeepCaller,
BeepTime = Region.BeepTime
FROM tblUnionLog
INNER JOIN ' + @ServerName + N'.dbo.tblLog AS Region
ON tblUnionLog.RecID = Region.RecID AND tblUnionLog.Region = ' + CONVERT
( NVARCHAR(4) ,@Region) +
N' WHERE BatchLast IS NULL '
EXEC sp_executesql @UpdateString
SET @TotalUpdatedRows = @@ROWCOUNT
SET @RemoteExecSQL = @ServerName + N'.dbo.sp_executesql'
SET @UpdateBatch = N'UPDATE tblLog SET BatchLast = ' + CONVERT (
NVARCHAR(10) ,@BatchNum) + N' WHERE BatchLast IS NULL'
EXECUTE @RemoteExecSQL @UpdateBatch
the symptom:
As I mentioned on one of the remote servers on the last update procedure
which is listed above we are getting the same error
consistently the error is "Server: Msg 11 , Level 16, State 1, Procedure
UpdatetblLog, Line 80 General network error. Check your network
documentation."
The download process worked fine for 3 month.
Since the the error occures in the same line in a specific store procedure
(out of 20 for each remote server) we have concluded It is not a network
problem.
Additional information :
Each download takes up to 3 minutes for each remote server and no more then
400 recordes being inserted or updated.
The problem occured on another remote server once .
Notice that line 80 (where the error occures) is the line where the second
operation is being executed which updates the update flag field on the
remote table through a remote call to dbo.sp_executesql
Step Taken:
Rebooting the remote Server and the Central Server.
Executing DBCC CHECKDB no indication of something wrong.
Executing the single update procedure in a transaction in Query Analyzer the
same result
Executing the single update procedure with no transaction in Query Analyzer
no error generated.
Executing the single update procedure in a transaction in Query Analyzer
under ISOLATION LEVEL READ COMMITED the same result.
Updatenig the MSDE to SP 4 same result
Removing MSDE and instaling it again with SP 4 same result.
Where do I go from here ??????
Bill Hollinshead [MS]
Re:
>Since the error occurs in the same line in a specific store procedure
>(out of 20 for each remote server) we have concluded It is not a network
>problem.
The above is not a good assumption <g>.
I believe the stored procedure 'UpdatetblLog' does most of its work within
just one line:
EXECUTE @RemoteExecSQL @UpdateBatch
While I see you have determined line 80 is sp_executesql, the line number
reported in a stroed procedure is not reliable in my experience <g>
(because the parser doesn't count all white space in the same manner you
may be counting it). Thus I am guessing that the above line is actually the
one referenced by the error message as UpdatetblLog, Line 80 <g>. Due to
wrap on the newsgroup, line 80 is (at least for me) the blank (whites pace)
line in UpdatetblLog that between "NVARCHAR(10) ,@BatchNum) + N' WHERE
BatchLast IS NULL' " and "EXECUTE @RemoteExecSQL @UpdateBatch" (but even
without wrap, as I said this isn't reliable AFAIK). I believe this EXECUTE
@RemoteExecSQL @UpdateBatch is where the majority of the client -server
network connectivity time will be spent, and thus it more than likely is
the line that will always be where the network error is raised. This in
turn means the network problem is not necessarily between the client (that
is executing this stored procedure 'UpdatetblLog') and the server (where
UpdatetblLog is running), but instead, the network problem (if this is a
network problem) is more likely between that same server and the server
that is referenced in @RemoteExecSQL. The other line where a network
problem can be seen (EXEC sp_executesql @UpdateString, as you identified)
would indicate a (possible) networking problem between the client (that is
executing this stored procedure 'UpdatetblLog') and the server (where
UpdatetblLog is running). Determining the line number is a stored procedure
is always difficult <g> because the parser doesn't count lines
In either case, a General Network Error is raised by Network-Library code,
and that code (both the client-side and the server-side) is supplied by
MDAC. Thus it is important to first ensure that a theoretical
Network-Library bug is not raising a bogus General Network Error, and thus
it would be easiest to ensure that SQL Server (or MSDE) is (or SQL Servers
are) running upon the latest MDAC service pack. If the latest MDAC service
pack does not remove the error, then we need to consider possible causes. A
General Network Error can be due to a variety of causes (it just means the
client lost its connection with a server), here are some:
1) Database corruption and/or a handled Exception (seen in a SQL Server
checkdb report and/or a SQL Server errorlog respectively) causing a spid to
be killed
2) A shutdown of the remote server while that stored procedure is executing
(seen by inspecting the start time and end time between a pair of
errorlogs)
3) A name resolution problem (WINS, LMHOSTS and/or DNS)
4) A SQL Server configuration problem (make sure
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q319942 are at
defaults) in conjunction with an MDAC problem
5) A router problem, such as a "black hole router" (that fails to forward
packets greater than a certain size, and of course
6) A Network problem related to the amount of data being sent - for
example, for the purposes of a repro, try executing the contents of the
stored procedure in Query Analyzer and (to test a different Database API),
try isql.exe (which uses DB-Library).
Here are some further tips (extracted from other cases and somewhat
duplicating what I mentioned above):
The conditions under which this error occurs could be related to sending
data over slow, or unreliable network links, especially large amounts of
data, or other network-related issues. The error is not specific to any one
type of T-SQL, nor is it specific to a particular execution of T-SQL. The
resolution to these errors is specific to the type of failure reported by
the network library. For example, connecting to an alias that specifies a
different Network-Library may be useful. However, because the vast majority
of these errors are network related, so consult with a network
administrator to perform analysis of routers, firewalls, network cards, and
other associated network devices. Tools such as Windows Network Monitor,
ODBC tracing, and SQL Server Profiler are valuable in these types of
problems. In other situations, the error can be caused by Windows NT-level
permissions problems, cross-domain trust issues, or name resolution
problems.
The following Knowledge Base articles may be helpful in any network
examination:
Q102908 How to Troubleshoot TCP/IP Connectivity with Windows NT
Q169790 How to Troubleshoot Basic TCP/IP Problems in Windows NT 4.0
Q173210 How to Troubleshoot Event 2000 in System Event Log
Q159211 Diagnoses and Treatment of Black Hole Routers
Q162326 Using TRACERT to Troubleshoot TCP/IP Problems in Windows
(I prefer pathping to tracert - bill)
Q148942 How to Capture Network Traffic with Network Monitor
In situations where this error is intermittent, the problem may be avoided
by simply restarting the T-SQL that has failed. In other cases, changing
the network library used by the client and server can work around the
problem. However, if you find that some phases of the T-SQL are more
subject to this error than others (delivery of a large data set, or general
problems with communication to a particular server, for example), it may be
necessary to investigate your network for the root cause of the error.
General Network Errors can be very tough to resolve, and usually take
patience <g>. On a couple of occasions, I have seen a reboot of a router
(inexplicably) resolve the problem, thus knowing the physical network
between clients and server is useful (perhaps there is a commonality).
After trying the quicker suggestions made above, you may find it useful to
open a Support Case on this one, because resolving this error can take a
considerable amount of labor.
Thanks,
Bill Hollinshead
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no
rights. Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
Yuval
Against my first assumption the problem was indeed network error and as you
mentioned
a simple reboot of the router did the job.
""Bill Hollinshead [MS]"" <bil...@online.microsoft.com> wrote in message
news:c7wfMdGgCHA.2476@cpmsftngxa08...