Copying table data from SQL Server 2005 to SQL Server 2000 - Very Slow when using OLEDB Source a

An SSIS package to transfer data from a DB instance on SQL Server 2005 to SQL Server 2000 is extremely slow. The package uses an OLEDB Source to OLEDB Destination for data transfer which is basically one table from sql server 2005 to sql server 2000. The job takes 5 minutes to transfer about 400 rows at night when there is very little activity on the server. During the day the job almost always times out.

On SQL Server 200 instances the job ran in minutes in the old 2000 package.

Is there an alternative to this. Tranfer Objects task does not work as there is apparently a defect according to Microsoft. Please let me know if there is any other option other than using a Execute 2000 package task or using an ActiveX Script to read records from one source and to insert them into the destination source, which I am not certain how long it might take and how viable will that be?

Any inputs will be much appreciated.

Thanks,

MShah

What defect are you referring to in "Transfer Objects" task?|||

Check this out for the defect:

http://lab.msdn.microsoft.com/ProductFeedback/viewFeedback.aspx?feedbackid=336f6832-f68c-4a7f-be74-3e62e1310609

MShah

|||This bug was specific to using "SQL Server Authentication" at the destination of the transfer. It has been fixed in SP1.

So, to copy data from one table to the other, you should be able to use Transfer task with "Windows Authentication" at the destionation if you are using SQL Server 2005 RTM. If you have SP1 installed, you should be able to use "SQL Server Authentication" or "Windows Authentication".|||

Thanks. I will upgrade to SP1. We only use SQL Server Authentication here, no windows / mixed mode.

sql

Copying database over network

Hi,
I'm extremely new to sql , using sql 2000.
I have an application server and a database sitting on a NAS. I would like
to copy the database from the NAS over the network to another server (test
server). I believe with the application server running I would have to detac
h
the data base. If I do not start-up the application server and just do a cop
y
from the NAS to the test server, would this work? and naturally with out
detaching(because there is nothing to detach it from). and I would also like
to give the database a different name. I hope I explaned this correctly. Any
help would be appreciated. Thank You in advance.
Greg newbieConsider using BACKUP and RESTORE. BACKUP lets you backup the database to a
file, which you can move to a different machine, and restore it with a new
name if you prefer, using the RESTORE command. See SQL Server Books Online
for more information and examples on these commands.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Greg" <Greg@.discussions.microsoft.com> wrote in message
news:E71A2217-AB1E-49CB-9F5D-FBA2B45FAD88@.microsoft.com...
> Hi,
> I'm extremely new to sql , using sql 2000.
> I have an application server and a database sitting on a NAS. I would
> like
> to copy the database from the NAS over the network to another server (test
> server). I believe with the application server running I would have to
> detach
> the data base. If I do not start-up the application server and just do a
> copy
> from the NAS to the test server, would this work? and naturally with out
> detaching(because there is nothing to detach it from). and I would also
> like
> to give the database a different name. I hope I explaned this correctly.
> Any
> help would be appreciated. Thank You in advance.
> --
> Greg newbie

Copying database over network

Hi,
I'm extremely new to sql , using sql 2000.
I have an application server and a database sitting on a NAS. I would like
to copy the database from the NAS over the network to another server (test
server). I believe with the application server running I would have to detach
the data base. If I do not start-up the application server and just do a copy
from the NAS to the test server, would this work? and naturally with out
detaching(because there is nothing to detach it from). and I would also like
to give the database a different name. I hope I explaned this correctly. Any
help would be appreciated. Thank You in advance.
--
Greg newbieConsider using BACKUP and RESTORE. BACKUP lets you backup the database to a
file, which you can move to a different machine, and restore it with a new
name if you prefer, using the RESTORE command. See SQL Server Books Online
for more information and examples on these commands.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Greg" <Greg@.discussions.microsoft.com> wrote in message
news:E71A2217-AB1E-49CB-9F5D-FBA2B45FAD88@.microsoft.com...
> Hi,
> I'm extremely new to sql , using sql 2000.
> I have an application server and a database sitting on a NAS. I would
> like
> to copy the database from the NAS over the network to another server (test
> server). I believe with the application server running I would have to
> detach
> the data base. If I do not start-up the application server and just do a
> copy
> from the NAS to the test server, would this work? and naturally with out
> detaching(because there is nothing to detach it from). and I would also
> like
> to give the database a different name. I hope I explaned this correctly.
> Any
> help would be appreciated. Thank You in advance.
> --
> Greg newbie

Copying database over network

Hi,
I'm extremely new to sql , using sql 2000.
I have an application server and a database sitting on a NAS. I would like
to copy the database from the NAS over the network to another server (test
server). I believe with the application server running I would have to detach
the data base. If I do not start-up the application server and just do a copy
from the NAS to the test server, would this work? and naturally with out
detaching(because there is nothing to detach it from). and I would also like
to give the database a different name. I hope I explaned this correctly. Any
help would be appreciated. Thank You in advance.
Greg newbie
Consider using BACKUP and RESTORE. BACKUP lets you backup the database to a
file, which you can move to a different machine, and restore it with a new
name if you prefer, using the RESTORE command. See SQL Server Books Online
for more information and examples on these commands.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Greg" <Greg@.discussions.microsoft.com> wrote in message
news:E71A2217-AB1E-49CB-9F5D-FBA2B45FAD88@.microsoft.com...
> Hi,
> I'm extremely new to sql , using sql 2000.
> I have an application server and a database sitting on a NAS. I would
> like
> to copy the database from the NAS over the network to another server (test
> server). I believe with the application server running I would have to
> detach
> the data base. If I do not start-up the application server and just do a
> copy
> from the NAS to the test server, would this work? and naturally with out
> detaching(because there is nothing to detach it from). and I would also
> like
> to give the database a different name. I hope I explaned this correctly.
> Any
> help would be appreciated. Thank You in advance.
> --
> Greg newbie