How to open OpenDataSource permissions (data operations between different Server databases)-*-access remote database in T-SQL statements (OpenRowSet/OpenDataSource/openq

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 more information 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', 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

 

-- 2. 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 ('sqlodb', 'server = (local); Pwd = ***; uid = sa; ', 'select * from TB. DBO. school ') as t
select * From OpenRowSet ('sqlodb', '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 ('sqlodb', '(local)'; 'sa'; '***', 'select * from TB. DBO. school ') as t
select * From OpenRowSet ('sqlodb',' (local) '; 'sa';' *** ', TB. DBO. school) as t
select * From OpenRowSet ('sqlodb', '(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

-- 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 ')

 

 

 

-- Examples of using sqlncli for OpenDataSource
Select * From OpenDataSource ('sqlncl', 'server = (local); uid = sa; Pwd = ***; '). TB. DBO. School as t
Select * From OpenDataSource ('sqlncl', 'server = (local); uid = sa; Pwd = ***; database = tb'). TB. DBO. School as t

-- Example of using oledb for OpenDataSource
Select * From OpenDataSource ('sqloledb', 'server = (local); trusted_connection = yes; '). TB. DBO. School as t

-- Other OpenDataSource usage
Insert OpenDataSource ('sqlncl', 'server = (local); trusted_connection = yes ;'). TB. DBO. school (name) values ('ghjkl ')/* Do You Want To insert a row */
Update OpenDataSource ('sqlncl', 'server = (local); trusted_connection = yes; '). TB. DBO. School Set Name = 'kkkkkkk'
Delete from OpenDataSource ('sqlncl', 'server = (local); trusted_connection = yes; '). TB. DBO. School where id = 1

 

 

 

-- Some examples of using oledb in openquery
Exec sp_addrole server 'itsv', '', 'sqloledb', '(local )'
Exec sp_add1_srvlogin 'itsv', 'false', null, 'sa ','***'
Select * From openquery (itsv, 'select * from TB. DBO. school ')

-- Examples of using sqlncli in openquery
Exec sp_addmediaserver 'itsva', '', 'sqlncl', '(local )'
Exec sp_add1_srvlogin 'itsva', 'false', null, 'sa ','***'
Select * From openquery (itsva, 'select * from TB. DBO. school ')

-- Other openquery usage
Insert openquery (itsva, 'select name from TB. DBO. School where id = 1') values ('ghjkl ')/* Do You Want To insert a row */
Update openquery (itsva, 'select name from TB. DBO. School where id = 1') Set Name = 'kkkkkkk'
Delete openquery (itsva, 'select name from TB. DBO. School where id = 1 ')
 

 

 

4. Summary

You can see that there are three ways to connect sqlserver to multiple servers.

I personally think OpenRowSet is the best. It is easy to use and supports flexible query statement formulation during connection.

Openquery is also good. You can also specify the query statement when querying. It is also flexible to use it. However, before querying, you must first use exec sp_add1_server and exec sp_add1_srvlogin to establish a connection between the server and the server.

The OpenDataSource is slightly poor, and it cannot be used to specify a query during connection.

In addition, you can also connect to the remote Analysis Server for MDX queries, and then use t-SQL for nested queries, it can be seen that the remote query of T-SQL is very powerful

 

 

 

1. Operate SQL server data, such as SQL Server/Oracle
Format:
Select top 10 *
From OpenDataSource (
Providername,
Data Source = servername; user id = loginuser; Password = Password
). [Databasename]. DBO. tablename
The above is my company database, ordermaster is the sales form
For example:
Select top 10 *
From OpenDataSource (
Sqloledb,
Data Source = globe01; user id = xw_cai; Password = 123
). [Order]. DBO. ordermaster
2. operate an Excel table
Format:
Select *
From OpenDataSource (Microsoft. Jet. oledb.4.0,
Data Source = "Excel file storage path"; user id =; Password =; extended properties = Excel 5.0)... sheetname $
Sheetname: name of the workspace. The name of the workspace must be suffixed with $.
For example:
Select *
From OpenDataSource (Microsoft. Jet. oledb.4.0,
Data Source = "G: \ workeveryday \ daydo \ orderlist"; user id =; Password =; extended properties = Excel 5.0)... orderlist $
3. Operate text files
Format:
Select * from
OpenDataSource (Microsoft. Jet. oledb.4.0,
Text; HDR = no; database = path for storing text files)... [textname # TXT]
Textnameis the suffix file name, And txtis the suffix of the suffix file, so you cannot change the suffix to.. The format textname.txt is incorrect, and the correct textname # TXT. Database = '\' the last \ cannot be less.
Example:
Select * from
OpenDataSource (Microsoft. Jet. oledb.4.0,
Text; HDR = no; database = E: \ study \ C # \ data \? W ??? Y material \ Text \)... [textname # TXT]