Copy tables between 2 SQL Servers

[tgprakash]

Hello All,
I have a requirement wherein have 2 sql server databases(say machine1 & machine2).
I want to run a sql query on machine1 , which would copy 5 tables in a 'XYZ' database on machine2 TO 'XYZ' database on machine1.
Note : I have user/password for both the machines.

I tried this using BCP utility, but problem using BCP is, exceptions cannot be handled And if there is a network failure during the process of data transfer , the process console window window of BCP utility, remains still(stuck) and needs user input to continue.

Please send me a QUERY(I already know about the Export/Import in SQL Server Enterprise Manager, but i need a query to run from vc++) to copy tables between 2 sql servers. that query, i should be able to place between BEGIN & COMMIT Transaction Process.

Waiting for ur reply.

Regards,
Prax.

[panayotisk]

I am sending you a Dynamic Select query that uses a remote table (just to give an idea):

DECLARE @SrcServer nvarchar(300)
DECLARE @TrgServer nvarchar(300)

SET @SrcServer = 'OPENDATASOURCE(''SQLOLEDB'', ''Data Source=PC-4;User ID=sa;Password=1'').'
SET @TrgServer = ''

SET @dic_id = 66

-- Find the language tables that the dictionary uses
SET @select_stmt =
'SELECT @l_a = [lang_a], @l_b = [lang_b] FROM ' + @SrcServer + '[Sys Dictionaries].[dbo].[DIC] WHERE [id] = ' + CAST(@dic_id AS varchar)

EXECUTE sp_executesql @select_stmt, N'@l_a TLangAbbr OUTPUT, @l_b TLangAbbr OUTPUT', @l_a = @lang_a OUTPUT, @l_b = @lang_b OUTPUT

Another option would be to use sp_addlinkedserver if I remember well...
Search for OPENDATASOURCE() in Books Online

[Andreas Masur]

[ Moved thread ]