Transport Connection Errors

Hi :)

We are upgrading from 2000 to 2005.  I have a few other posts open regarding DTS package problems and things... I am trying to reengineer a DTS package that is no longer functioning the way it does in 2000.

Basically, we're connecting to another sql 2000 machine with a sql login and pulling down tables into our enviornment.

I have tried migrating the package to SSIS... that fails... even running the package as a DTS fails.  So, I am trying to disect this a bit further and find out exactly why...

I created a linked server to the sql server 2000 machine.  I can connect to it... I can see the tables ... I can query the tables...

If I try to run an insert query via the linked server it only works if the table is small and the connection is quick.  Once it gets to a table that is large and it has to run for a while it fails giving me the :
A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)

This is also occuring in the SSIS package... it will run for about 10 minutes or so and then bomb with the above error message.  

I went to our SQL 2000 box and did the same thing and ran the same insert statement with a linked server and it ran fine for 30 mins and inserted 8 million records into the table.

From my research, it says that sql 2005 sets the default timeout to 0 to run infinitely... is this really true?  What is really happening here?  

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

By default that is true, but there is nothing to say that the timeout setting has not been set on either instance and the Package itself.
Try running a simple Neverending query from SQLCMD and SSMS to test where the timeout setting exists.

Declare @time datetime
While 1=1
	Select @time = GETDATE()

Open in new window

Reza RadConsultant, TrainerCommented:
for first problem:
did you tried to run dts package with execute sql server 2000 package? what happened?

for second:
try to fetch data not full in one step, fetch portion of data with some select top x .... and transfer this portion of data, then go to next portion, you can do this with combination of foreach loop and dataflow task.
Roxanne25Author Commented:
Actually if you reread my original post I said that this is happening even when I try to run an insert query with a linked server... I did this in a query window.  

It did manage to execute successfully once when I used an openquery but then when I tried to run it again I got the same thing... so it does it intermitantly at random times.  As I also mentioned, if the data set that I am pulling is small then it seems to work more often than not.  But when I test this in sql server 2000 it works perfectly fine 100% of the time.  So, it seems to be something with sql 2005 itself.

I am off from work for a while though so I was going to try Banthor's suggestion on Monday.
Roxanne25Author Commented:
I'm actually not sure if this is a sql server problem at this point at all.  We have had reports of some other similar problems happening on different applications... so, I don't believe the problem is related at all to anything within sql.  At this point I believe its a network problem.

I will request that this question be removed.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anthony PerkinsCommented:
>>At this point I believe its a network problem.<<
That is correct.  You get the same "A transport-level error has occurred ..." when the connection is interrupted to SQL Server and you can easily duplicate it by temporarily disconnectiing the network cable to your workstation when using SSMS.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.