Use the OpenRowSet function to connect to and access remote database data

We sometimes encounter data that needs to be accessed not only on the local server, but also on the remote database server, for example, if we store data with the same table structure on the two servers, what should we do if we combine the two data and display them together? Then we use the OpenRowSet function provided by SQL Server, it is easy to use. Remember to reference the OpenRowSet function just like referencing the table name.

We can write as follows:
1. select. * From Table1 a left join OpenRowSet ('msdasql ', 'driver = {SQL Server}; server = 192.168.0.1, 2412; uid = sa; Pwd = BB', dbname. DBO. table2) as B on. id = B. id order by. id DESC
Connect table 2 on SQL Server with port 2412 192.168.0.1 to table 1 on the local server.

2. select * from (select. * From Table1 a union select B. * From OpenRowSet ('msdasql ', 'driver = {SQL Server}; server = 192.168.0.1, 2412; uid = AA; Pwd = BB', dbname. DBO. table2) as B) temptable order by ID DESC
Add the content in table 2 on SQL Server with the address 192.168.0.1 port 2412 to table 1.

In the above example, we use the odbc ole db to provideProgramOf course, you may also use SQL Server's Microsoft OLE DB, jet's Microsoft OLE DB and other providers to connect.

Use SQL Server's Microsoft OLE DB:
OpenRowSet ('sqlodb', 'server address, port'; 'username'; 'Password', 'SQL statement ')

Use Microsoft ole db of jet:
OpenRowSet ('Microsoft. Jet. oledb.4.0 ', 'c: \ test. mdb'; 'admin'; 'pwd', orders)

Note: The preceding two user names and passwords are separated by semicolons.

I just used it today, so I can't write it down if my memory is poor.