SQL Server database remote operations

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. How to enable 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:

1. exec sp_configure ' show advanced options ', 1
3. Reconfigure
5. exec sp_configure ' Ad Hoc distributed Queries ', 1
7. Reconfigure

After use, remember to close it, because this is a security risk, remember to execute the following SQL statement:

1. exec sp_configure ' Ad Hoc distributed Queries ', 0
3. Reconfigure
5. exec sp_configure ' show advanced options ', 0
7. Reconfigure

2. Examples of Use

Create a linked server

1. exec sp_addlinkedserver ' itsv ', ' ', ' SQLOLEDB ', ' Remote server name or IP address '
3. exec sp_addlinkedsrvlogin ' itsv ', ' false ', NULL, ' username ', ' password '

query Example

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

Import sample

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

Remove linked server when no longer in use

1. exec sp_dropserver ' itsv ', ' droplogins '

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

1, OpenRowset

query Example

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

Raw cost surface

1. 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

1. Insert OPENROWSET (' SQLOLEDB ', ' SQL Server name '; ' User name '; ' Password ', database name. dbo. Table name)
3. Select *from Local surface

Update local surface

1. Update b
3. Set B. column a=A. Column A
5. From OPENROWSET (' SQLOLEDB ', ' SQL Server name '; ' User name '; ' Password ', database name. dbo. Table name) as a inner join local table B
7. On a.column1=b.column1

OPENQUERY usage requires creating a connection.

Create a linked server by creating a connection first

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

Inquire

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

Importing a local table to a remote table

1. Insert OpenQuery (ITSV, ' SELECT * from database. dbo. Table name ')
3. SELECT * FROM local surface

Update local surface

1. Update b
3. Set B. column b=a. Column B
5. From OPENQUERY (ITSV, ' SELECT * from database. dbo. Table name ') as a
7. Inner JOIN local table B on a. Column a=b. Column A

2, Opendatasource/openrowset

1. 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:

1. Insert OpenDataSource (' SQLOLEDB ', ' Data source=ip/servername; User id= login name; password= password '). database. dbo. Table name
3. SELECT * FROM local surface

About the SQL Server database in the use of T-SQL statements to access the remote database operation is introduced here, I hope the introduction of this can be a harvest for you!

SQL Server database remote operations