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.
printmediaAsked:
Who is Participating?
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.
0
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.
0
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy 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?
0
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.
0
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?
0
Anthony PerkinsCommented:
Other than to verify you have the appropriate permissions, I have no idea.
0

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

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.