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?