Shulou(Shulou.com)05/31 Report--

This article is about how to use SQL Server to enable Ad Hoc Distributed Queries, the editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

In SQLServer database remote operations, the AdHocDistributedQueries service is first enabled before using openrowset/opendatasource. Because this service is not secure, SqlServer is turned off by default. The following editor to explain how to use SQLServer to enable AdHocDistributedQueries?

How to enable AdHocDistributedQueries with SQLServer

1. How to enable AdHocDistributedQueries

SQLServer blocked access to the STATEMENT'OpenRowset/OpenDatasource' of component 'AdHocDistributedQueries' because this component has been shut down as part of the security configuration of this server. The system administrator can use. Sp_configure enables' AdHocDistributedQueries'.

To enable the AdHocDistributedQueries method, execute the following query statement:

Execsp_configure'showadvancedoptions',1reconfigureexecsp_configure'AdHocDistributedQueries',1reconfigure

After you have finished using it, be sure to close it, as this is a security hazard, remember to execute the following SQL statement:

Execsp_configure'AdHocDistributedQueries',0reconfigureexecsp_configure'showadvancedoptions',0reconfigure

2. Use examples

Create a linked server

Execsp_addlinkedserver'ITSV','','SQLOLEDB',' remote server name or ip address' execsp_addlinkedsrvlogin'ITSV','false',null,' username', 'password'

Query example

Select*fromITSV. Database name. Dbo. Table name

Import exampl

Select*into table fromITSV. Database name. Dbo. Table name

Delete linked servers when they are no longer used in the future

Execsp_dropserver'ITSV','droplogins'

How to enable AdHocDistributedQueries with SQLServer

3. Connect remote / LAN data (openrowset/openquery/opendatasource)

1 、 openrowset

Query example

Select*fromopenrowset ('SQLOLEDB','sql server name'; 'user name'; 'password', database name. Dbo. Table name)

Living cost surface

Select*into table fromopenrowset ('SQLOLEDB','sql server name'; 'user name'; 'password', database name. Dbo. Table name)

Import the local surface into the remote table

Insertopenrowset ('SQLOLEDB','sql server name'; 'user name'; 'password', database name. Dbo. Table name) select*from local table

Update the local surface

Updatebsetb. List Atrea. Column Afromopenrowset ('SQLOLEDB','sql server name'; 'user name'; 'password', database name. Dbo. Table name) asainnerjoin local table bona.column1=b.column1

Openquery usage requires the creation of a connection.

First create a connection to create a linked server

Execsp_addlinkedserver'ITSV','','SQLOLEDB',' remote server name or ip address'

Inquire

Select*FROMopenquery (ITSV,'SELECT*FROM database. Dbo. Table name')

Import the local surface into the remote table

Insertopenquery (ITSV,'SELECT*FROM database. Dbo. Table name') select*from local table

Update the local surface

Updatebsetb. Column Baua. Column BFROMopenquery (ITSV,'SELECT*FROM database. Dbo. Table name') asainnerjoin local table bona. List Abelib. Column A

2 、 opendatasource/openrowset

SELECT*FROMopendatasource ('SQLOLEDB','DataSource=ip/ServerName;UserID= login; Password= password'). Test.dbo.roy_ta

Import the local surface into the remote table:

Insertopendatasource ('SQLOLEDB','DataSource=ip/ServerName;UserID= login name; Password= password'). Database. Dbo. Table name select*from local table.

The above is how to use SQL Server to enable Ad Hoc Distributed Queries, the editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.