[SQL Exception] SQL Server blocked the component ' Ad Hoc distributed Queries '

/ / Perform remote database table query
Select * from openrowset( ‘SQLOLEDB‘, ‘192.168.5.547‘; ‘sa‘; ‘password ‘, [database name].[dbo].[table name])

An exception occurred:

News15281, Level16, State1, section 1 line
SQL Server Blocked component ' ad Hoc distributed Queries Statement "openrowset/ Opendatasource "because this component has been shut down as part of this server's security configuration. System administrators can enable ad Hoc distributed queries< by using sp_configure Span style= "COLOR: #800000" >. About enabling ' ad Hoc distributed Queries For more information, see peripheral application configurator "

Required when using sandbox mode fails, workaround:

 
//Enable Ad Hoc Distributed Queries:
Exec sp_configure ‘show advanced options‘,1
Reconfigure
Exec sp_configure ‘Ad Hoc Distributed Queries’, 1
Reconfigure



//Close Ad Hoc Distributed Queries:
Exec sp_configure ‘Ad Hoc Distributed Queries’,0
Reconfigure
Exec sp_configure ‘show advanced options‘,0
Reconfigure

 
Sp_addlinkedserver
Create a linked server that allows access to distributed, heterogeneous queries against OLE DB data sources. After you create a linked server with sp_addlinkedserver, this server can perform distributed queries. If the linked server is defined as Microsoft® SQL ServerTM, the remote stored procedure can be executed.

Grammar
Sp_addlinkedserver [ @server = ] ‘server‘
[ , [ @srvproduct = ] ‘product_name‘ ]
[ , [ @provider = ] ‘provider_name‘ ]
[ , [ @datasrc = ] ‘data_source‘ ]
[ , [ @location = ] ‘location‘ ]
[ , [ @provstr = ] ‘provider_string‘ ]
[ , [ @catalog = ] ‘catalog’ ]
Grammar
Sp_addlinkedsrvlogin [ @rmtsrvname = ] ‘rmtsrvname‘
[ , [ @useself = ] ‘useself‘ ]
[ , [ @locallogin = ] ‘locallogin‘ ]
[ , [ @rmtuser = ] ‘rmtuser‘ ]
[ , [ @rmtpassword = ] ‘rmtpassword‘ ]


[@useself =] ‘useself‘

Decide which login name to use to connect to the remote server. Useself is varchar(8), with a default of TRUE. A value of true specifies that SQL Server authenticated logins use their own credentials to connect to rmtsrvname, ignoring the rmtuser and rmtpassword parameters. The value false specifies that the rmtuser and rmtpassword parameters are used to connect to the rmtsrvname of a particular locallogin. If rmtuser and rmtpassword are also set to NULL, there is no need to use any login or password to connect to the linked server. For Windows NT authentication logins, useself is true, unless the Microsoft Windows NT® environment supports secure account delegation, and the provider supports Windows authentication (you no longer need to create a mapping with a value of true, but create still effective).

[ @locallogin =] ‘locallogin‘

Login on the local server. Locallogin is sysname, with a default of NULL. A NULL value specifies that this entry will be applied to all local logins that connect to rmtsrvname. If the value is not NULL, locallogin can be a SQL Server login or a Windows NT user. Windows NT users must be granted direct access to SQL Server or through their access to SQL Server as a member of a group that has been granted access.
Data manipulation between different server databases
--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

--Delete the linked server when it is no longer used
Exec sp_dropserver ‘ITSV ‘, ‘droplogins ‘

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

--Query example
Select * from openrowset( ‘sqlOLEDB ‘, ‘sql server name ‘; ‘username ‘; ‘password ‘, database name.dbo.table name)

--Generate local tables
Select * into table from openrowset ( ‘sqlOLEDB ‘, ‘sql server name ‘; ‘username ‘; ‘password ‘, database name.dbo.table name)

-- Import local tables into remote tables
Insert openrowset( ‘SQLOLEDB ‘, ‘sql server name ‘; ‘username ‘; ‘password ‘, database name.dbo.table name)
Select *from local table

--Update local table
Update b
Set b. column A=a. column A
From openrowset( ‘sqlOLEDB ‘, ‘sql server name ‘; ‘username ‘; ‘password ‘, database name.dbo.table name) as a inner join local table b
On a.column1=b.column1

--openquery usage needs to create a connection

- First create a connection to create a linked server
Exec sp_addlinkedserver ‘ITSV ‘, ‘ ‘, ‘SQLOLEDB ‘, ‘remote server name or ip address ‘
--Inquire 
Select *
FROM openquery (ITSV, ‘SELECT * FROM database.dbo.table name ‘)
-- Import local tables into remote tables
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 into remote tables
Insert opendatasource( ‘SQLOLEDB ‘, ‘Data Source=ip/ServerName; User ID=login name; Password=password ‘).database.dbo.table name
Select * from

[SQL Exception] SQL Server blocked the component ' Ad Hoc distributed Queries '