Transferring table between servers 1

[Glasgow]

I have an application that will access a SQL 2000 server and a SQL 2005 server and I want to copy the contents of one table in a database on the SQL 2000 server to the SQL 2005 server. This process will probably be run a few times each day.

Can I write an INSERT..INTO..FROM query that will work across both servers (i.e. source table on SQL 2000 server and destination on the SQL 2005 server) or do I need to use another mechanism - e.g. DTS or ADO recordsets with different connections?

I feel I should know the answer but I dont!!

 

[ca8msm]

As long as the servers can see each other and you have permission to do the insert, then you can access the other server by using the naming convention [tt][server].[database].[owner].

[/tt]


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[Glasgow]

Thanks I knew I'd come across this before - I was just looking at sp_addlinkedserver when your post came through.