SQL remote database access

1. enable Ad Hoc Distributed Queries

Enable the Ad Hoc Distributed Queries service before using openrowset/opendatasource. Because this service is insecure, SqlServer is disabled by default.

How to enable Ad Hoc Distributed Queries

SQL Server blocks the STATEMENT 'openrowset/OpenDatasource 'of the 'ad Hoc Distributed Queries' component'

Because this component has been disabled as part of the server security configuration. The system administrator can use

Sp_configure enables 'ad Hoc Distributed Queries '. For details about enabling 'ad Hoc Distributed Queries'

For more information, see "peripheral application configurator" in SQL Server books online ".

Enable the Ad Hoc Distributed Queries method and 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 it 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. Example

-- Create a linked server

Exec sp_addrole server 'itsv', '', 'sqloledb', 'remote server name or IP address'

Exec sp_add1_srvlogin '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 in use

Exec sp_dropserver 'itsv', 'droplogins'

-- Connect to remote/LAN data (openrowset/openquery/opendatasource)

-- 1. openrowset

-- Query example

Select * from openrowset ('sqlodb', 'SQL Server name', 'username', 'Password', and database name. Dbo. Table name)

-- Generate a local table

Select * into table from openrowset ('sqlodb', 'SQL Server name'; 'username'; 'Password', database name. Dbo. Table name)

-- Import a local table to a remote table

Insert openrowset ('sqlodb', '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

From openrowset ('sqlodb', 'SQL Server name'; 'username'; 'Password', database name. Dbo. Table Name) as a inner join local Table B

On a. column1 = B. column1

-- Create a connection for openquery usage

-- First create a connection to create a linked server

Exec sp_addrole server 'itsv', '', 'sqloledb', 'remote server name or IP address'

-- Query

Select *

FROM openquery (ITSV, 'select * FROM database. Dbo. Table name ')

-- Import a local table to a remote table

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

Inner join local table B on a. Column A = B. Column

-- 3. opendatasource/openrowset

SELECT *

FROM opendatasource ('sqlodb', 'Data Source = ip/ServerName; User ID = login name; Password = password '). Test. dbo. roy_ta

-- Import a local table to a remote table

Insert opendatasource ('sqlodb', 'Data Source = ip/ServerName; User ID = login name; Password = password '). Database. Dbo. Table Name

Select * from local table

3. Examples written by myself

-- Some examples of using OLEDB in openrowset

Select * from openrowset ('sqloledb', 'server = (local); PWD = ***; UID = sa; ', 'select * from TB. dbo. school ') as t

Select * from openrowset ('sqloledb', 'server = (local); PWD = ***; UID = sa; ', TB. dbo. school) as t

Select * from openrowset ('sqloledb', 'server = (local); Trusted_Connection = yes; ', TB. dbo. school) as t

Select * from openrowset ('sqloledb', '(local)'; 'sa ';' *** ', 'select * from TB. dbo. school') as t

Select * from openrowset ('sqloledb', '(local)'; 'sa ';' *** ', TB. dbo. school) as t

Select * from openrowset ('sqloledb', '(local)'; 'sa ';' *** ', 'select school. id as id1, people. id as id2 from TB. dbo. school inner join TB. dbo. people on school. id = people. id') as t

-- Some examples of using SQLNCLI in openrowset (SQLNCLI can be used later than SqlServer2005)

Select * from openrowset ('sqlncl', '(local)'; 'sa ';' *** ', 'select * from TB. dbo. school') as t

Select * from openrowset ('sqlncl', 'server = (local); Trusted_Connection = yes; ', 'select * from TB. dbo. school') as t

Select * from openrowset ('sqlncl', 'server = (local); UID = sa; PWD = ***; ', 'select * from TB. dbo. school ') as t

Select * from openrowset ('sqlncl', 'server = (local); UID = sa; PWD = ***; ', TB. dbo. school) as t

Select * from openrowset ('sqlncl', 'server = (local); UID = sa; PWD = ***; DataBase = tb', 'select * from dbo. school ') as t

-- Other openrowset usage

Insert openrowset ('sqlncl', 'server = (local); Trusted_Connection = yes; ', 'select name from TB. dbo. school where id = 1 ') values ('ghjkl')/* Do You Want To insert a single row */

Update openrowset ('sqlncl', 'server = (local); Trusted_Connection = yes; ', 'select name from TB. dbo. school where id = 1') set name = 'kkkkkkk'

Delete from openrowset ('sqlncl', 'server = (local); Trusted_Connection = yes; ', 'select name from TB. dbo. school where id = 1 ')