Accessing the remote database in a T-SQL statement

1 , enable Ad Hoc Distributed Queries

Before using Openrowset/opendatasource to enable the ad Hoc distributed queries service, because this service is not secure, SQL Server is turned off by default

Methods for enabling AD HOC distributed queries

SQL Server blocked STATEMENT ' openrowset/opendatasource ' for component ' Ad Hoc distributed Queries '

Access, because this component has been shut down as part of this server's security configuration. System administrators can use the

sp_configure enable ' Ad Hoc distributed Queries '. For more information about enabling ' Ad Hoc distributed Queries '

Information, see "Surface area Configurator" in SQL Server Books Online.
To enable the ad Hoc distributed queries method, execute the following query statement:

exec sp_configure ' show advanced options ', 1
Reconfigure
exec sp_configure ' Ad Hoc distributed Queries ', 1
Reconfigure
After use, remember to close it, because this is a security risk, remember to execute the following SQL statement

exec sp_configure ' Ad Hoc distributed Queries ', 0
Reconfigure
exec sp_configure ' show advanced options ', 0
Reconfigure

2 , using the sample

--Create a linked server
exec sp_addlinkedserver ' itsv ', ' ', ' SQLOLEDB ', ' Remote server name or IP address '
exec sp_addlinkedsrvlogin ' itsv ', ' false ', NULL, ' username ', ' password '

--query Example
SELECT * from ITSV. Database name. dbo. Table name

--import Example
SELECT * into table from ITSV. Database name. dbo. Table name

--Remove linked server when no longer in use
exec sp_dropserver ' itsv ', ' droplogins '

--Connect remote/LAN data (Openrowset/openquery/opendatasource)
--1, OpenRowset

--query Example
SELECT * FROM OPENROWSET (' SQLOLEDB ', ' SQL Server name '; ' User name '; ' Password ', database name. dbo. Table name)

--Raw cost surface
SELECT * Into table from OPENROWSET (' SQLOLEDB ', ' SQL Server name '; ' User name '; ' Password ', database name. dbo. Table name)

--Import local tables to remote tables
Insert OPENROWSET (' SQLOLEDB ', ' SQL Server name '; ' User name '; ' Password ', database name. dbo. Table name)
Select *from Local surface

--update local table  
update b 
set b. Column a=a. column a 
From openrowset (   ' sqloledb  ',  ' SQL Server name   ';  ' user name   ';  ' password   ', database name. dbo. Table name as a  inner join  local table  b 
on a.column1=b.column1 

--openquery usage requires creating a connection  

--first create a connection to create a linked server  
Exec sp_addlinkedserver     ' itsv  ',  '   ',  ' sqloledb  ',  ' remote server name or IP address   '  
--Query  
select * 
From openquery (itsv,   ' select *  from  Database. dbo. Table name   ')  
--Import the local table into the remote table  
Insert openquery (itsv,   ' select *   FROM  database. dbo. Table name   ')  
select * from  Local table  
-Update local table  
Update  b 
set b. Column b=a. column b 
from openquery (itsv,   ' Select * from   database. dbo. Table name   ')  as a  
inner join  Local table  b on a. Column a=b. Column A  

--3, Opendatasource/openrowset
SELECT *
From OpenDataSource (' SQLOLEDB ', ' Data source=ip/servername; User id= login name; password= password '). Test.dbo.roy_ta
--Import local tables to remote tables
Insert OpenDataSource (' SQLOLEDB ', ' Data source=ip/servername; User id= login name; password= password '). database. dbo. Table name
SELECT * FROM local surface

3. connecting to a database with SQL statements

--Bind the "database address PC" to the connection name "DTT"

Execsp_addlinkedserver ' DTT ', ' ', ' SQLOLEDB ', ' 192.168.9.11 '

--Bind the "username sa" and "password" to the connection name "DTT"

Execsp_addlinkedsrvlogin ' DTT ', ' false ', NULL, ' sa ', '

-After the establishment is complete, it can be used normally.

--Connection name. Database name. dbo. Table name This is a complete SQL statement

Selecttop 1 * from [DTT]. [Dqdb]. [dbo]. [Customer]

--After the operation, delete the "DTT" Connection name

exec sp_dropserver ' DTT ', ' droplogins '

4 , an example of your own writing

--openrowset some examples of using OLE DB
Select * from openrowset (' SQLOLEDB ', ' server= (local) ; pwd=***; Uid=sa; ', ' Select * from tb.dbo.school ')  as t
select * from  OpenRowset (' SQLOLEDB ', ' server= (local); pwd=***; Uid=sa; ', TB.dbo.school)  as t
Select * from openrowset (' SQLOLEDB ', ' server= (local); Trusted_connection=yes; ', TB.dbo.school)  as t
Select * from openrowset (' SQLOLEDB ', ' (local) '; ' Sa '; ' ', ' Select * from tb.dbo.school ')  as t
Select * from openrowset (' SQLOLEDB ', ' (local) '; ' Sa '; ' ', TB.dbo.school)  as t
Select * from openrowset (' SQLOLEDB ', ' (local) '; ' Sa '; ' ', ' select school.id as id1,people.id as id2 from tb.dbo.school  Inner join tb.dbo.people on school.id=people.id ')  as t

--openrowset some examples of using SQLNCLI (SQLNCLI above SqlServer2005 to use)
select * from  OpenRowset (' SQLNCLI ', ' (local) '; ' Sa '; ' ', ' Select * from tb.dbo.school ')  as t
Select * from openrowset (' Sqlncli ', ' server= (local); Trusted_connection=yes; ', ' Select * from tb.dbo.school ')  as t
select *  From openrowset (' sqlncli ', ' server= (local); Uid=sa; pwd=***; ', ' Select * from tb.dbo.school ')  as t
select * from  OpenRowset (' SQLNCLI ', ' server= (local); Uid=sa; pwd=***; ', TB.dbo.school)  as t
Select * from openrowset (' sqlncli ', ' server= (local); Uid=sa; pwd=***;D atabase=tb ', ' Select * from dbo.school ')  as t

--openrowset Other uses
Insert OPENROWSET (' SQLNCLI ', ' server= (local); Trusted_connection=yes; ', ' select name from TB.dbo.school where id=1 ') VALUES (' GHJKL ')/* to not be in the same way, insert a line */
Update OPENROWSET (' SQLNCLI ', ' server= (local); Trusted_connection=yes; ', ' select name from TB.dbo.school where Id=1 ') set name= ' Kkkkkk '
Delete from OPENROWSET (' SQLNCLI ', ' server= (local); Trusted_connection=yes; ', ' select name from TB.dbo.school where Id=1 ')

Some examples of--opendatasource using SQLNCLI
SELECT * from OpenDataSource (' sqlncli ', ' server= (local); Uid=sa; pwd=***; '). TB.dbo.school as T
SELECT * from OpenDataSource (' sqlncli ', ' server= (local); Uid=sa; pwd=***;D atabase=tb '). TB.dbo.school as T

--opendatasource examples of using OLE DB
SELECT * from OpenDataSource (' SQLOLEDB ', ' server= (local); Trusted_connection=yes; '). TB.dbo.school as T

--opendatasource Other uses
Insert OpenDataSource (' sqlncli ', ' server= (local); Trusted_connection=yes; '). TB.dbo.school (name) VALUES (' GHJKL ')/* to not have the same where, insert a line */
Update OpenDataSource (' SQLNCLI ', ' server= (local); Trusted_connection=yes; '). TB.dbo.school set name= ' KKKKKK '
Delete from OpenDataSource (' sqlncli ', ' server= (local); Trusted_connection=yes; '). TB.dbo.school where id=1

--openquery Some examples of using OLE DB
exec sp_addlinkedserver ' itsv ', ', ' SQLOLEDB ', ' (local) '
exec sp_addlinkedsrvlogin ' itsv ', ' false ', NULL, ' sa ', ' * * * '
SELECT * FROM OPENQUERY (ITSV, ' SELECT * from TB.dbo.school ')

Some examples of--openquery using SQLNCLI
exec sp_addlinkedserver ' Itsva ', ', ' sqlncli ', ' (local) '
exec sp_addlinkedsrvlogin ' Itsva ', ' false ', NULL, ' sa ', ' * * * '
SELECT * FROM OPENQUERY (ITSVA, ' SELECT * from TB.dbo.school ')

--openquery Other uses
Insert OpenQuery (ITSVA, ' select name from TB.dbo.school where id=1 ') VALUES (' GHJKL ')/* To do not have the same where, insert a line */
Update OpenQuery (ITSVA, ' select name from TB.dbo.school where Id=1 ') set name= ' Kkkkkk '
Delete OpenQuery (Itsva, ' select name from TB.dbo.school where Id=1 ')

Accessing the remote database in a T-SQL statement