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?
Roxanne25Connect With a Mentor Author 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.
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.
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.