Transfer records from one db to another 1

[MDA]

Hi all,

Need some help please...

I have a small VB app.. The user will select a few variables and that will execute a SP.. The SP will transfer records from on SQL server DB to a separate SQL server DB.

Two questions:
1. Is it possible to put the transactions that are created from the SP in a recordset, then using VB insert them into the table on the second DB?

2. OR Is there a way to connect to the separate SQL server with my stored procedure?

I was thinking I could do this using DTS but the challenge is that the SP creates a new table that is created in VB every time it is executed.

Any help or ideas are greatly appreciated.

Regards,

MDA

 

[NickISD]

MDA,
One method could be to change the SP, place new data created by the SP in to a temp table then use VB to query the temp data and then insert to second server using seperate ADO connections. Or you could set up replication on the first server to create / update the tables on the second one.

Regards, Nick

 

[NickISD]

MDA,
Just saw this in SQL Server 2000 help file. I think after completing this you can then query the other database using a single connection so it could make your transfer of data easier.

Regards, Nick


How to set up a linked server (Enterprise Manager)
To set up a linked server
Expand a server group, and then expand a server.
Expand Security, right-click Linked Servers, and then click New Linked Server.
Click the General tab, and in the Linked server box, type the name of the server to link.
Under Server type, click a selection.
If you select Other data source, you will have to specify provider properties.