Shulou(Shulou.com)06/01 Report--

There are three ways for MSSQL to access a remote database (openrowset/opendatasource/openquery):

Prerequisite: enable Ad Hoc Distributed Queries

1. Enable the Ad Hoc Distributed Queries service (this service is not secure. SqlServer is disabled by default).

2. How to enable and disable Ad Hoc Distributed Queries:

If it is not enabled, the following error message appears:

SQL Server blocked access to the STATEMENT'OpenRowset/OpenDatasource' of component'Ad Hoc Distributed Queries' because this component has been shut down as part of the security configuration of this server. The system administrator can enable'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling'Ad Hoc Distributed Queries', see "Peripheral Application Configurator" in SQL Server Books online.

Turn it on using the following statement:

Exec sp_configure 'show advanced options',1reconfigureexec sp_configure' Ad Hoc Distributed Queries',1reconfigure

After use, use the following statement to close (this is a security hazard):

Exec sp_configure'Ad Hoc Distributed Queries',0reconfigureexec sp_configure 'show advanced options',0reconfigure

Example:

1. Use to create a link, and then use the link operation / openquery:

-- create linked servers exec sp_addlinkedserver 'ITSV','', 'SQLOLEDB', 'remote server name or ip address' exec sp_addlinkedsrvlogin 'ITSV', 'false', null, 'username', 'password'-- query select * server. Database name. Dbo. Table name select * FROM openquery (ITSV, 'SELECT * FROM database. Dbo. Table name')-- Import select * into table from ITSV. Database name. Dbo. Table name insert openquery (ITSV, 'SELECT * FROM database. Dbo. Table name') select * from local table update b set b. Column Baua. Column B FROM openquery (ITSV, 'SELECT * FROM database. Dbo. Table name') as an inner join local surface b on a. List Abelib. Column A-- Import the local table into the remote table delete openquery (ITSVA,'select name from database. Dbo. Table name where id=1')-- delete exec sp_dropserver 'ITSV', 'droplogins'-- delete the linked server when it is no longer in use

2. Use openrowset to connect:

Select * from openrowset ('SQLOLEDB', 'sql server name'; 'user name'; 'password', database name. Dbo. Table name)-query select * into local new table from openrowset ('SQLOLEDB', 'sql server name'; 'user name'; 'password', database name. Dbo. Table name)-remote data table imports to local new table (local new table does not exist) insert into local old table from openrowset ('SQLOLEDB', 'sql server name'; 'user name'; 'password', database name. Dbo. Table name)-Import from the remote data table to the local old table (the local old table already exists) insert openrowset ('SQLOLEDB', 'sql server name'; 'user name'; 'password', database name. Dbo. Table name) select * from local table-imports local surface data into remote database table update b set b. List Atrea. Column A from openrowset ('SQLOLEDB', 'sql server name'; 'user name'; 'password', database name. Dbo. Table name) as an inner join local table b on a.column1=b.column1-update local surface data, set local surface data = remote table data

Use OLEDB:

Select * from openrowset ('SQLOLEDB','Server= (local); PWD=***;UID=sa;','select * from TB.dbo.school') as tselect * from openrowset (' SQLOLEDB','Server= (local); PWD=***;UID=sa;',TB.dbo.school) as tselect * from openrowset ('SQLOLEDB','Server= (local); Trusted_Connection=yes;',TB.dbo.school) as tselect * from openrowset (' SQLOLEDB',' (local)'; 'sa' '* *', 'select * from TB.dbo.school') as tselect * from openrowset (' SQLOLEDB',' (local)'; 'sa';'***',TB.dbo.school) as tselect * 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

Use SQLNCLI:

Select * from openrowset ('SQLNCLI',' (local)'; 'sa';'***','select * from TB.dbo.school') as tselect * from openrowset (' SQLNCLI','Server= (local); Trusted_Connection=yes;','select * from database. Dbo. Table name') as tselect * from openrowset ('SQLNCLI','Server= (local); UID=sa;PWD=***;','select * from database. Dbo. Table name') as tselect * from openrowset ('SQLNCLI','Server= (local); UID=sa;PWD=***;', database. Dbo. As tselect * from openrowset ('SQLNCLI','Server= (local); UID=sa;PWD=***;DataBase= database', 'select * from dbo. Table name') as tinsert openrowset ('SQLNCLI','Server= (local); Trusted_Connection=yes;','select name from database. Dbo. Table name where id=1') values ('ghjkl') / * whether you want where or not, insert a row * / update openrowset (' SQLNCLI','Server= (local); Trusted_Connection=yes;','select name from database. Dbo. Table name where id=1') set name='kkkkkk'delete from openrowset ('SQLNCLI','Server= (local); Trusted_Connection=yes;','select name from database. Dbo. Table name where id=1')

3. Use opendatasource:

Use SQLNCLI:

Select * from opendatasource ('SQLNCLI','Server= (local); UID=sa;PWD=***;'). Database. Dbo. The table name is as tselect * from opendatasource ('SQLNCLI','Server= (local); UID=sa;PWD=***;DataBase= Database'). Database. Dbo. Table name as tinsert opendatasource ('SQLNCLI','Server= (local); Trusted_Connection=yes;') .TB.dbo.school (name) values (' ghjkl') / * insert a row * / update opendatasource ('SQLNCLI','Server= (local); Trusted_Connection=yes;') .TB.dbo.school set name='kkkkkk'delete from opendatasource (' SQLNCLI','Server= (local); Trusted_Connection=yes;'). TB.dbo.school where id=1 if you want the same where or not

Use OLEDB:

Select * from opendatasource ('SQLOLEDB','Server= (local); Trusted_Connection=yes;'). Database. Dbo. The table name is as t select * from OpenDataSource ('SQLOLEDB','Data Source= server; User ID=sa;Password=*'). Database. Dbo. Table name as T

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.