Copy tables on remote servers

Hi all, I'm trying to create a script / stored proc that copies one table from a remote server to another table on a different remote server.

The approach I wanted to take was...

create a stored proc that can be called remotely the stored proc tests to see if the table exists, if it does it is dropped, if not it is created new table structure and data copied from remote server A to remote server B

...I feel that this should be quite easy, but I must admit I'm struggling. Any help would be really appreciated.

Cheers, Jon

Did you try to setup a linked server and then do a SELECT INTO?

How about using DTS instead?

|||

Thanks for your reply William.

I have tried SELECT INTO...

select * into remoteServer.intranetcms.dbo.woodford_bridge
from localServer.intranetcms.dbo.woodford_bridge

... but I get the error...

The object name 'remoteServer.intranetcms.dbo.' contains more than the maximum number of prefixes. The maximum is 2.

...I am familiar with DTS but what I would like to do is create a stored proc that can be executed inside an on_click event from a web page.

- Jon

|||

In Ado.net, there is a pretty good method for doing this. The api name is SqlBulkCopy.

Let me know if you want to know more about the api.

Thanks

Bei

|||

Thanks Bei, unfortunately we're not using .Net 2 so, as I understand it, can't use SQLBulkCopy. Any other ideas?

- Jon

|||

Hi, maybe very late, but I ran into the same problem and solved this by doing this:

Ex:

SELECT t.Bilagnr

FROM [172.18.165.25\ASNV].[AS_14830].[dbo].[tblSalesDetails] as t WHERE Pkey > 1

Hope this helps!

- Per S.

|||

I'll recommend the use of DTS. then

create an SP that triggers the DTS thru xp_cmdshell using dtsrun.

call the SP from ASP.net