July 10, 2007 at 12:40 pm
I am receiving an error message when running a DTS package that transfers data from one SQL server to another. The sending SQL is Win2003 SP1 and SLQ2000 Ent. Ed. SP3a. The receiving server sits on a DMZ and is running Win2000 AS and SQL2000 Ent. Ed. SP3a. The problem occurs only once in a while. We have been able to workaround the issue by setting up multiple retries and/or setting the insert batch size to a very low number. Still there are random failures. Yet, when we transfer data to other servers not on the DMZ (no firewall) there are no failures. The network and security guys think it's SQL, but I am not convinced. At this point, I am at a loss. Any help would be very much appreciated.
Error Detail Records:
Error: -2147467259 (80004005); Provider Error: 11 (B)
Error string: [DBNETLIB][ConnectionWrite (send()).]General network error. Check your network documentation.
Error source: Microsoft OLE DB Provider for SQL Server
Help file:
Help context: 0
DTSRun OnError: DTSStep_DTSDataPumpTask_2, Error = -2147213206 (8004206A)
Error string: The number of failing rows exceeds the maximum specified.
Error source: Microsoft Data Transformation Services (DTS) Data Pump
Help file: sqldts80.hlp
Help context: 0
July 11, 2007 at 12:35 am
Read the KB article in the link below. It gives a solution for you.
http://support.microsoft.com/kb/303120/
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
August 14, 2007 at 8:31 am
Thanks for the KB article, but we are not running in fiber mode. I suspect there is a issue with the network because the server we are connecting to is on a DMZ and passes through a firewall. The problem is entirely random with an approximate 90% success rate only once in a while do we see a failure. Additionally, if we move the server off the DMZ to the local domain it never fails.
August 14, 2007 at 9:07 am
I cannot remember exactly all of the details, but we did used to get this error quite frequently, though it was not in a DTS, but just in some applications that we had written. I believe the issue was with the data that we were extracting. Some of the fields were text or large varchar fields. I believe the problem was corrected by rewriting some of the apps and upgrading to SP4.
Not a silver bullet, but it might be another place to look.
-ewahner
August 14, 2007 at 11:37 pm
Likely culprit in this scenario would be the firewall - since it "never" fails while both servers are behind the firewall, but fails when the server is put in the DMZ, firewall is the place to start.
General network errors typically are broken connections - something is causing the firewall to break the open connection and DTS will "sense" that break and throw the general network error. I can think of a lot of different scenarios (e.g. a clustered firewall or rule change) where your connection might be getting inadvertently broken during the normal operations of the firewall... have you talked to your network folks yet?
Joe
August 15, 2007 at 1:00 pm
We have suspected the firewall from day one. The network folks have run traces and say the disconnect appears to be coming from the source system (i.e., sql server). Our tests, however, demonstrate when the target server is removed from the DMZ failures cease to occur. So, we are at a standstill. The current solution is reduce the insert batch size in DTS to a very low number (in some cases 10). This seems to be working, although tasks take much longer to complete.
Thanks for the reply.
August 15, 2007 at 10:16 pm
Thomas -
What make/model of firewall are you dealing with? My thought would be that the firewall is automatically closing connections after a specific period of perceived inactivity, perhaps that particular setting can be adjusted upwards for connections on port X (1433)?
Joe
August 16, 2007 at 8:24 am
As I start to remember more and more about this "General Network Error", I now remember that this is just the last error in the error chain. We too went down the path of ripping apart the firewall. This error is very missleading. We eventually figured it out to be as I stated in my earlier post that there was some data-type inconsistency and the way we figured it out was running exactly what the DTS was doing through a .NET console app in debug mode. As soon as an error was thrown, we were able to drill into all of the inner exceptions until we got to the root problem. Of course the first error we saw was this "General Network Error" and the cause of this was probably due to some mid-stream abort of the recordset being returned to the client. Don't trust that error.
August 16, 2007 at 8:48 am
It may be a connection issue. When threads exceed its maximum setting (or capability), you may have this error. Here is a link. Hopefully, it is useful.
http://knowledgebase.datadirect.com/kbase.nsf/SupportLink+Online/2475396BD?OpenDocument
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply