Solved

sql dts failure DBNETLIB connectionread general network error

Posted on 2008-06-25
9
1,075 Views
Last Modified: 2013-11-30
I have this sql 2000 DTS package created years ago by the original developer. The package has been running fine for years but recently the job executing the package has been playing up for unknown reasons. The package just imports from a text file and then execute a series of stored procs that create daily temp tables, update some values , change some field types etc. All internal transact sql with no communication with another server etc.

Today the job failed and troubleshooting by exeuting individual steps in the pacakge identified a stored proc in the package as the culprit.
The error message is:
"[DBNETLIB][ConnectionRead (recv()).]General network error. Check your network documentation.".

And the stored proc is (yes, not very elegant):
ALTER  Procedure SP_LOAN_ASSET_UPDATE_START_MATURITY_DATES_3
AS
BEGIN
ALTER TABLE LOAN_ASSET
ALTER COLUMN ASSET_START_DATE SMALLDATETIME

ALTER TABLE LOAN_ASSET
ALTER COLUMN ASSET_MATURITY_DATE SMALLDATETIME

update loan_asset
set asset_purch_date = '990722'
where asset_loan_num = 40806
and asset_purch_date = '991722'

update loan_asset
set asset_purch_date = '020706'
where asset_loan_num = 138609
and asset_purch_date = '027706'

ALTER TABLE LOAN_ASSET
ALTER COLUMN ASSET_PURCH_DATE SMALLDATETIME

However when the stored proc was executed in query analyzer by the same user it executed fine.
As you can see, the sp just updates and alters the table.
The job owner executes under the sql agent proxy account which has the necessary rights.
We do not want a repeat of this failure as the job executes dailly at 5am when no one is in and is mission critical.

So my question is why would this job/package fail at this point occassionally when it has the necessary permissions but work 90% of the time?
0
Comment
Question by:frankytee
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 38

Expert Comment

by:Jim P.
ID: 21874699
I haven't really run into this unless the SQL Server is starved for memory.

You might want to look at the article below, and the other thing is to monitor your memory use.

http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q229/5/64.asp&NoWebContent=1
0
 
LVL 19

Author Comment

by:frankytee
ID: 21880113
thanks for the response. i had a quick look and it mentions application roles on ADO connection to SQL which we do not use. but i'll have to read it further.
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 21880208
Can you run the performance monitor on the server and see how it looks for memory?
0
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
LVL 19

Author Comment

by:frankytee
ID: 21896440
performance monitor is fine at the moment. is there any way to historically view this (ie go back to the 26th june)?
0
 
LVL 38

Accepted Solution

by:
Jim P. earned 500 total points
ID: 21898513
>> is there any way to historically view this

Not unless you were logging it to a file already.

This is one you are going to have to just troubleshoot when it occurs.

0
 
LVL 19

Author Comment

by:frankytee
ID: 21930753
thats a good idea. i found the link below but haven't gone right through it.
http://www.netadmintools.com/art577.html
it mentions creating a sql db to hold the log. would this be the best way to do it or is there a more effective way?
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 21933030
Not that I can think of off hand.
0
 
LVL 19

Author Closing Comment

by:frankytee
ID: 31470818
it wont be possible to go back in time to diagnose the actual problem but jim's comments lead me in the right direction moving forward for future troubleshooting
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 21934323
Glad to be of assistance. May all your days get brighter and brighter.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question