SQL Server Access database across servers (openrowset/opendatasource/openquery)

SQL Server Access database across servers (openrowset/opendatasource/openquery)

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. Examples of Use

--Create a linked server
Execsp_addlinkedserver‘Itsv‘,‘‘,‘SQLOLEDB‘,‘Remote server name or IP address‘
Execsp_addlinkedsrvlogin‘Itsv‘,‘False‘,Null,‘User name‘,‘Password‘

--query Example
Select*FromITSV. Database name. dbo. Table name

--Import sample
Select*IntoTableFromITSV. Database name. dbo. Table name

--Remove linked server when no longer in use
ExecSp_dropserver‘Itsv‘,‘Droplogins‘

--Connecting Remote/LAN data (Openrowset/openquery/opendatasource)
--1, OpenRowset

--query Example
Select*FromOpenRowset(‘SQLOLEDB‘,‘SQL Server Name‘;‘User name‘;‘Password‘, database name. dbo. Table name)

--Raw cost surface
Select*IntoTableFromOpenRowset(‘SQLOLEDB‘,‘SQL Server Name‘;‘User name‘;‘Password‘, database name. dbo. Table name)

--Importing a local table to a remote table
InsertOpenRowset(‘SQLOLEDB‘,‘SQL Server Name‘;‘User name‘;‘Password‘, database name. dbo. Table name)
Select*FromLocal surface

--Update local surface
UpdateB
SetB. Column A=A. Column A
FromOpenRowset(‘SQLOLEDB‘,‘SQL Server Name‘;‘User name‘;‘Password‘, database name. dbo. Table name)AsAInnerJoinLocal surface B
OnA.column1=B.column1

--OPENQUERY usage requires creating a connection

--Create a linked server by creating a connection first
Execsp_addlinkedserver‘Itsv‘,‘‘,‘SQLOLEDB‘,‘Remote server name or IP address‘
--Inquire
Select*
FromOpenQuery(ITSV,‘Select * from database. dbo. Table name‘)
--Importing a local table to a remote table
InsertOpenQuery(ITSV,‘Select * from database. dbo. Table name‘)
Select*FromLocal surface
--Update local surface
UpdateB
SetB. Column B=A. Column B
FromOpenQuery(ITSV,‘Select * from database. dbo. Table name‘)AsA
InnerJoinLocal surface BOnA. Column A=B. Column A

--3, Opendatasource/openrowset
Select*
FromOpenDataSource(‘SQLOLEDB‘,‘Data Source=ip/servername; User id= login name; password= Password‘). Test.dbo.roy_ta
-- Import the local table into the remote table   insert  Opendatasource ( sqloledb  ' data source=ip/servername; user id= login name; password= password   "). database. dbo. Table name  
select *  from  local table

SQL Server Access database across servers (openrowset/opendatasource/openquery)