Cross-server operations

This article mainly describes the operation of accessing the remote database in the SQL Server database, including the activation of the ad HOC distributed queries service and an instance of accessing the remote database, which I hope can help you.

In SQL Server database remote operations, first enable the Ad Hoc distributed Queries service before using Openrowset/opendatasource. Because this service is not secure, SQL Server is turned off by default.

1 , the method of enabling AD Hoc distributed Queries

SQL Server blocked access to the statement ' Openrowset/opendatasource ' of component ' Ad Hoc distributed Queries ' because this component was shut down as part of this server's security configuration. System administrators can use the. sp_configure enable ' Ad Hoc distributed Queries '.

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

1 exec sp_addlinkedserver ' itsv ', ', ' SQLOLEDB ', ' Remote server name or IP address '

2

3 exec sp_addlinkedsrvlogin ' ITSV ', ' false ', NULL, ' username ', ' password '

query Example

4 SELECT * from ITSV. Database name. dbo. Table name

Import sample

5 SELECT * into table from ITSV. Database name. dbo. Table name

Remove linked server when no longer in use

6 exec sp_dropserver ' itsv ', ' droplogins '

3 , connecting remote/LAN data (Openrowset/openquery/opendatasource)

1 , OpenRowset

query Example

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

Raw cost surface

8 SELECT * into table from OPENROWSET (' SQLOLEDB ', ' SQL Server name '; ' User name '; ' Password ', database name. dbo. Table name)

Importing a local table to a remote table

9 Insert OpenRowset (' SQLOLEDB ', ' SQL Server name '; ' User name '; ' Password ', database name. dbo. Table name)

10

Select *from Local surface

Update local surface

Update b

13

Set B. Column a=a. Column A

15

From OPENROWSET (' SQLOLEDB ', ' SQL Server name '; ' User name '; ' Password ', database name. dbo. Table name) as a inner join local table B

17

On A.column1=b.column1

OPENQUERY usage requires creating a connection.

Create a linked server by creating a connection first

exec sp_addlinkedserver ' itsv ', ' ', ' SQLOLEDB ', ' Remote server name or IP address '

Inquire

SELECT * FROM OPENQUERY (ITSV, ' select * from database. dbo. Table name ')

Importing a local table to a remote table

Insert OpenQuery (ITSV, ' SELECT * from database. dbo. Table name ')

22

SELECT * FROM local surface

Update local surface

Update b

25

Set B. column b=a. Column B

27

From OPENQUERY (ITSV, ' SELECT * from database. dbo. Table name ') as a

29

Inner JOIN local table B on a. Column a=b. Column A

2 , Opendatasource/openrowset

SELECT * from OpenDataSource (' SQLOLEDB ', ' Data source=ip/servername; User id= login name; password= password '). Test.dbo.roy_ta

To import a local table into a remote table:

Insert OpenDataSource (' SQLOLEDB ', ' Data source=ip/servername; User id= login name; password= password '). database. dbo. Table name

33

SELECT * FROM local surface

Cross-server operations