Showing posts with label himy. Show all posts
Showing posts with label himy. Show all posts

Monday, March 19, 2012

Best Practice when copy table from srv to srv

Hi!

My first post in this great forum. :)
Here goes:

I need some feedback on best practice (or just possible practice!) on creating a copy of a table from one SQLserver to another SQLserver.

I have a stored proc that loops some srv/databases/table-names and need to copy a specific table out to them all.

It works ok on the local server, but when i want to go across to another server trouble starts.

I have tried various approaches.

1) Linked server followed by "Insert into remotesrv.remotedb.dbo.tabel..."
result: cant run ALTER query in remote srv. SELECT statements works fine though.

2) Replication/Subscription
result: Works in general, but it only syncronizes alike tabels. Cant alter structure of table on remote.

3) DTS
result: Works fine, but not generic enough (variable tablenames needed).

What do you guys use in these situations?Ok no replys :)

For future reference I chose the following:

If fact 2) Replication/Subscription are open for alterations of tabel stucture.(I just needed to refresh my snapshot-file in the test)

The copy of tables are therefore done via replication triggerede by a stored procedure.