I am currently using ADODB connections to make my queries from VB6 to SQL servers. My question is how do you make a statement that will reference 2 different SQL servers? I want to eventually query one SQL server, take data from a particular table and then INSERT that into a different SQL server.
Below I have sample code that I use currently to transfer data from one table to another on the SAME SQL server.
Set MyRec = CreateObject("adodb.recordset")
MyConn.Open = "provider=SQLOLEDB;Server=testserver;database=Data1;user id=sa;password=test123"
sSQL = "INSERT INTO [Data1].DBO.Table2_t "
sSQL = sSQL & "SELECT [Data1].DBO.Table1_t.* "
sSQL = sSQL & "FROM [Data1].DBO.Table1_t; "
MyConn.commandtimeout = 0
MyRec.Open sSQL, MyConn
MyConn.Close
Thanks
Our community of experts have been thoroughly vetted for their expertise and industry experience.
The Distinguished Expert awards are presented to the top veteran and rookie experts to earn the most points in the top 50 topics.