Using an ADODB connection to query 2 different servers
Posted on 2014-11-10
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