Use OpenQuery in SQLServer to access other databases

OpenQuery is a technology used by SQLServer to interact with other servers. Through OpenQuery, SQLServer can directly access other database resources. Other databases exist in the OpenQuery expression as the external server. Use sp_slave servers to find all slave servers of the current database.

OpenQuery is a technology used by SQL Server to interact with other servers. Through OpenQuery, SQL Server can directly access other database resources. Other databases use the Linked Server in the OpenQuery expression. Use sp_receivservers to find all the linked servers of the current database.

OpenQuery is used by SQL ServerOthersServer interaction technology,

Through OpenQuery, SQL Server can directlyAccessOthersDatabaseResources.

WhileOthersDatabaseThe OpenQuery expression contains the Linked Server.

UseSp_incluservers can find the currentDatabaseAll linked servers.

You can also view the currentDatabaseLinked Server in:

The OpenQuery expression can be written as follows:

Select * from OpenQuery（[linkedServerName],'Select * from table1 where rownum < 10')

It is worth noting that if you want to execute the followingDatabaseStatement,

Select * from tbl_emp Where empName = 'leo''

Leo must be input in double quotation marks, as shown below,

Select * from OpenQuery（[linkedServerName],'Select * from table1 where empName = ''leo''')

If you want to input a parameter to OPENQUERY,

Dynamic SQL statements can be assembled and executed through exec.

DECLARE @Sql VARCHAR(1000)DECLARE @organizationID VARCHAR(10)SELECT @organizationID = (SELECT ORGANIZATION_ID FROM MYORGS WHERE ORGANIZATION_NAME = 'MMT')SET @Sql = 'SELECT * from tableName where organization_id='+@organizationIDSET @Sql = 'SELECT * FROM OPENQUERY(BETSYCRP2, ''' + REPLACE(@Sql, '''', '''''') + ''')'EXEC(@Sql)