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

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.
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.

Anthony PerkinsCommented:
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.
printmediaAuthor 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.
Anthony PerkinsCommented:
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.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

printmediaAuthor 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?
Anthony PerkinsCommented:
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.
printmediaAuthor 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?
Anthony PerkinsCommented:
Other than to verify you have the appropriate permissions, I have no idea.

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
printmediaAuthor Commented:
It was a permissions issue.
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.