We help IT Professionals succeed at work.

How to copy data from linked server (SQL Server 2008) table to SQL Server 2000 table

printmedia
printmedia asked
on
Hi all.

I would like to know how to copy data from linked server (SQL Server 2008) table to SQL Server 2000 table.

The linked server (myLinkedServer) has a table (myTable) that I want to copy the data from and insert it into myTable2 in the myDatabase on SQL Server 2000.

How can I do this using a DTS?

Thank you in advance.
Comment
Watch Question

Top Expert 2012

Commented:
I would like to know how to copy data from linked server
...
How can I do this using a DTS?


I am confused do you want to use a linked server or DTS?  If the first it is as simple as using an INSERT statement with a 4 part name.  If it is the second you do not need a linked server, you just open a connection to each database and transfer the data.

Author

Commented:
The linked server is a sql server 2008 with a database called myDatabase. The data in the table in myDatabase needs to copied everyday which is why I need a DTS to do this.
Top Expert 2012

Commented:
So you want to use DTS to transfer data from one database on a SQL Server 2008 to another database on SQL Server 2000?  If so, then you do not need linked servers for this task.  Period.

Are you going to create the DTS package on SQL Server 2000 or on SQL Server 2008?  If the second, I would recommend you use SSIS, instead.

Author

Commented:
The DTS package will be on SQL Server 2000.

So if not by linked server, then how do I copy data from the table on SQL Server 2008 to SQL Server 2000?
Top Expert 2012

Commented:
1. Create a DTS Package
2. Create a connection to the database on SQL Server 2000
3. Create a connection to the database on SQL Server 2008
4. Create a data transformation task between the two connections to move the data.

It does not get any simpler than that.

Author

Commented:
When I try to create a connection, in the "Data Source" drop down I select "Microsoft OLE DB Provider for SQL Server", I then select myServer from the "Server" drop down. But when I get to the "Database" drop down, I only see the master, msdb and tempdb databases, I don't see my databases. Is there a setting I need to update on SQL Server 2008?
Top Expert 2012
Commented:
Other than to verify you have the appropriate permissions, I have no idea.

Author

Commented:
It was a permissions issue.