• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3363
  • Last Modified:

Dts execution failure : [DBNETLIB][ConnectionWrite (send()).]General network error. Check your network documentation

I am trying to execute a dts package which will transfer data from oracle to sql server. But while executing this package i am getting the error

"[DBNETLIB][ConnectionWrite (send()).]General network error. Check your network documentation" at third and last step of dts execution.

Kindly help me regarding this.

I am executing this dts package of a remote server using query analyzer.

 
0
jaisonshereen
Asked:
jaisonshereen
  • 11
  • 8
2 Solutions
 
mironCommented:
did you try to search web on the error message, the first entry I got back was this one:
http://support.microsoft.com/default.aspx?scid=kb;en-us;899599

as I looked through I found a number of ways how this or similar error message could occur due to environmental issues such as NIC settings, TCP protocol settings, and other factors.  I can not see from here if the target SQL Server the same server you connect to using Query Analyzer and whether it has one NIC card or multi  homed. In other words at this point I would not exclude even subtle hardware malfunction such as NIC or a connection timeout due to remote query connection timeout set to low number.

To ensure good coverage, let me focus on how this issue can occur due to SQL Server related scenarios.

First of all what is the service pack level on the target server, both SQL Server and Windows?

Does DTS package live on the destination SQL Server or intermediate server placed between Oracle and destination SQL Server instance, if later then what is the service pack on the SQL Server where package is running.

Is log file configured to capture detailed progress for DTS package, any helpful info in addition to message you posted with question.

Is the data extracted and then sent to destination server using script or mapping between tables and/or views. If script is used could you post the code.

What are the chances of troubleshooting exact condition creates error, can you try to run package when it transfers no rows to isolate connection only related issue. Does package fail with the same error message?

Is there any chance TCP/IP connection is disenabled on the target SQL Server?

Since you are running package using Query Analyzer, could you try to use the following guide to troubleshoot connection http://support.microsoft.com/kb/827422, if you do not have access to troubleshoot SQL Server could you ask your colleague with appropriate access to do the same.

-- cheers
0
 
jaisonshereenAuthor Commented:
This is the sercvice pack

Microsoft SQL Server  7.00 - 7.00.842 (Intel X86)   Mar  2 2000 06:49:37   Copyright (c) 1988-1998 Microsoft Corporation  Standard Edition on Windows NT 4.0 (Build 1381: Service Pack 6)

I  logged on to  the server and was able to see the full domain list
and all the servers.

If I do a continuous  ping  to the  server it does not drop a single packet.

So  as  far  as I  can see it does not show me any network or NIC issues.

DTS package live on the destination SQL Server


Is log file configured to capture detailed progress for DTS package, any helpful info in addition to message you posted with question.

No log file configured here

Is the data extracted and then sent to destination server using script or mapping between tables and/or views. If script is used could you post the code.

No script, columns are mapped inside dts

What are the chances of troubleshooting exact condition creates error, can you try to run package when it transfers no rows to isolate connection only related issue. Does package fail with the same error message?

Since it is a live server this will not be easy

Is there any chance TCP/IP connection is disenabled on the target SQL Server?

No,its enabled and fine

This package was executing for one year ...suddenly from last month this error start comming..

Please help me


 

0
 
jaisonshereenAuthor Commented:
And this package is executing from some other remote servers also !
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
mironCommented:
initial comment reads:
"executing this dts package of a remote server"

last comment reads:
"package is executing from some other remote servers also !"

Are the "other remote servers" experience the same trouble? Or this issue only affecting single remote connection.

I whish I could look at the package, peruse logs, launch package with some pre - conditions to test whether package fails without records. In my absence all I can ask is approachable explanation, and some willingness to try proposed steps.

We need to start somewhere to fix this issue.

I would work in parallel, if the issue is local between one remote workstation and server then I would in parallel troubleshoot

1 security grants at each level, including OS and SQL Server
2 tracing data and queries getting exchanged between SQL Server and DTS package
3 logging DTS package step - by - step status completion
4 verifying connection settings, such as remote query timeout
5 checking hardware issues
6 checking system event log
7 SQL Server Error log
8 checking if any new software was installed on the machine, for instance, Oracle connectivity software. There can be any number of reasons, including pending reboot, or some memory hungry application that was left on the server after install completed, that can potentially cause entire machine to behave unexpectedly. Given that package was running free of issues for so long the chance of some un  likely combination of factors causing the issue to arise is 50% at least.

I parallel I would try to install SQL Server 7.0 Service Pack 4. Ensuring matching connection libraries across remote machines ups the chance of trouble - free connection.

Let me return back to an unlikely combination of factors, when issue similar to this affect connectivity Admins usually start placing NIC in promiscuous mode and collect network traces. The finding could be trivial, or can be something like a virus, which will prompt machine re  build. BTW, when was this machine last time rebuilt?

Chances are we need to nail each one of the above steps to get this issue resolved.

-- cheers
0
 
jaisonshereenAuthor Commented:
executing stored procedures inside dts packages individually ... by logging to the server ..resolved the issues..!!

Thanks for the help... i ll split points for the assistance!
0
 
mironCommented:
a bit unclear, do you still run in DTS, only now logged on with personal account, or it is each procedure that need to be executed individually. Later seems to be a bit of a manual overhead. If this is the case I would try to modify DTS script so that it runs free of issues.

-- Miron.
0
 
jaisonshereenAuthor Commented:
Yes i still run the dts package.

Before, i was running this dts package by connecting a remote database to the sql query analyser .

But now i am running this by connecting to the remote server directly using demware ( software to connect to server login remotely)

And it only runs fine when i execute each step manually one by one and ...

But for the last step i need to run the each stored procedures inside the last step individually inside the sql query
analyser of the live server which i logged in using demware.
0
 
mironCommented:
hmm...
I wonder what can be done to automate these steps...
could it be timeout issue. If I tried to guess it could be related to missing index coupled with inflow of rows. The Query Analyser is configured to run query without timing out, while dts could potentially carry default 30 second timeout for a command object for Exceute SQL task. If this is so see if you could set CommandTimeout to -1 and check for task completion. If an index is missing, we could look into troubleshooting. It would help if you could please post queries.

-- Miron.
0
 
jaisonshereenAuthor Commented:
right now the timeout of dts package is set to "zero".

In the description of dts itself is written that zero timeout means infinity

you want me to make it -1 instead of zero and try to execute this package?
0
 
jaisonshereenAuthor Commented:
Could you explain in detail what is missing index coupled with inflow of rows?
0
 
mironCommented:
my bad, 0 is no - timeout.
the missing index and inflow of rows could over time increase length of query, causing timeout to cut off connection, with symptom of "[DBNETLIB][ConnectionWrite (send()).]General network error."
0
 
mironCommented:
default timeout for remote query is 10 minutes ( 600 seconds )
any chance this could be the trouble when "executing this dts package of a remote server using query analyzer"
0
 
jaisonshereenAuthor Commented:
so you mean to say i need to change dts timeout to -1?
0
 
mironCommented:
so you mean to say i need to change dts timeout to -1?
-- no, "my bad, 0 is no - timeout."
-- could you answer question above:
-- default timeout for remote query is 10 minutes ( 600 seconds )
-- any chance this could be the trouble when "executing this dts package of a remote server using query analyzer"

for the last step i need to run the each stored procedures inside the last step individually inside the sql query analyser of the live server which i logged in using demware
-- is it Execute SQL task that runs these procedures inside DTS package
-- if you run it as a single task, what error occurs ( I assume this is the way to avoid an error message ), is it "[DBNETLIB][ConnectionWrite (send()).]General network error." or some other error message
0
 
jaisonshereenAuthor Commented:
Question:

Default timeout for remote query is 10 minutes ( 600 seconds )
any chance this could be the trouble when "executing this dts package of a remote server using query analyzer"


Answer: I am not executing the DTS package using query analyzer.I am executing it by right clicking the package by "execute package" option.

Question:

For the last step i need to run the each stored procedures inside the last step individually inside the sql query analyser of the live server which i logged in using demware
-- is it Execute SQL task that runs these procedures inside DTS package
-- if you run it as a single task, what error occurs ( I assume this is the way to avoid an error message ), is it "[DBNETLIB][ConnectionWrite (send()).]General network error." or some other error message

Answer:

-> I have a DTS Package
->When i execute it by right clicking the package by "execute package" option, i am getting the "General network error"
-> This DTS consists of three steps
-> So i execute the entire package by ........

Open the dts in design view...

Right clicking on the step1 and clicking the "execute step"
Result: Step1 execution Success

Right clicking on the step2 and clicking the "execute step"
Result: Step2 execution Success

Right clicking on the step3 and clicking the "execute step"
Result: Step3 execution Failure

Hence the step3 is failure ..i open the step three ..and from properties general...i can see the store procedures which execute in the step 3 ..i just executed the  each stored procedure seen there ..using..query analyzer ..in the actual db where the db is residing .. !

Hence ...its become a success!!!!!!!!!!!!!!!!!!!


Please let me know anything else you need to know!







0
 
mironCommented:
Right clicking on the step3 and clicking the "execute step"
Result: Step3 execution Failure

--- what message do you read when failure occurs?
--- Is it still "[DBNETLIB][ConnectionWrite (send()).]General network error." or different error message

..i just executed the  each stored procedure seen there ..using..query analyzer ..in the actual db where the db is residing .. !

--- ok, ok. I wonder what could help here... is that the package ( as opposed to manual running procedures in SQL Server Query Analyzer ) point to incorrect database / wrong server. I would check just to make sure.
--- Also, check if the script in the package begins with "SET NOCOUNT ON" with no quotes as script first line.
0
 
jaisonshereenAuthor Commented:
Is it still "[DBNETLIB][ConnectionWrite (send()).]General network error." or different error message

Yes it is still [DBNETLIB][ConnectionWrite (send()).]General network error.


point to incorrect database

No its pointing to correct database and server


Also, check if the script in the package begins with "SET NOCOUNT ON" with no quotes as script first line

i ll check within 5 minutes ....and let you know

0
 
jaisonshereenAuthor Commented:
no there is no set count


below is the step three code


Declare @nReturn	as int
EXEC @nReturn = sp_ProcessInboundCardSales
 
IF @nReturn = 0
BEGIN
	EXEC spUpdateSpendToDate 
	EXEC spUpdateCustomerDiscountCards
	EXEC sp_ProcessTillTransactions
	EXEC spUpdateSpend
 
	EXEC sp_MoveFile
END
ELSE
BEGIN
	RAISERROR (50101, 8, 1)
END
 
GO

Open in new window

0
 
jaisonshereenAuthor Commented:
Please see the DTS
dts.jpg
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

  • 11
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now