SQL Server connects to databases across servers
Article Table of Contents
Open component
Grammar
Add link
Close component
Open component
Firstly, open the Ad Hoc Distributed Queries component and execute the following statement in the SQL query editor:
Exec sp_Configure 'show advanced options', 1
Configure
Exec sp_Configure 'Ad Hoc Distributed Queries', 1
Reconfigure.
The reason for opening a component will be mentioned in the syntax.
Grammar
The openrowset() method is a command to connect to other server databases
Syntax: openrowset ('SQLOLEDB ',' ip, port ';' username ';' password ',' SQL statement or table ')
Two examples:
1. Select * from openrowset ('SQLOLEDB ',' 127.0.0.18080 ';' sa ';' 12345678 ',
[Database] [dbo]. table)
//Directly connecting the table does not require quotation marks.
2. Select * from openrowset ('SQLOLEDB ',' 127.0.0.18080 ';' sa ';' 12345678 ',
Select ID, name from [Database] [dbo]. table ')
//If it is an SQL statement, quotation marks are required.
Note: If the component is not enabled, the following error will be reported.
Add link If you feel that using the openrowset() method every time is too cumbersome, you can add a linked server.
EXEC sp_Addlinkedserver
@Server' Iptest ', -- server alias (can be IP or set by oneself, just a name)
@Srvproduct'-- The product name of the OLEDB data source that links to the server, and the object is sqlserver. It is not necessary to specify
@Provider' SQLOLEDB, a dynamic link library that provides interaction between programs and data sources, is a connection object that does not need to be changed
@Datasrc=' 101.1.10101 '- IP address of the accessed server.
Add user login link.
EXEC sp_Addlinkedsrvlogin
'iptest', -- Consistent with the server alias above
'false', - don't worry
NULL, -- don't worry
'administrator', -- the server account being accessed
'password' - The password of the accessed server.
At this point, execute:
Select * from [iptest] [Database] [dbo] [table]
Select * from openrowset ('SQLOLEDB ',' 127.0.0.18080 ';' sa ';' 12345678 ',
[Database] [dbo]. table)
The effect is the same
Suggestion: If it is confirmed that the database name and table name are not duplicated or named with keywords, the square brackets can be removed.
Close component
Note: If you do not need a link, you need to close the component because opening it can pose a security risk and prevent others from attacking.
Exec sp_Configure 'Ad Hoc Distributed Queries', 0
Configure
Exec sp_Configure 'show advanced options', 0
Reconfigure.
If the link is no longer in use, you can delete an existing link.
Exec sp_Droplinkedsrvlogin server alias, null
Exec sp_Dropserver server alias.