SQL Access remote Database

1. Enable AD Hoc distributed Queries

To enable the ad Hoc distributed queries service before using Openrowset/opendatasource, because this service is not secure, SQL Server is turned off by default

Methods for enabling the ad Hoc distributed queries

SQL Server blocked the STATEMENT ' openrowset/opendatasource ' of component ' Ad Hoc distributed Queries '

Because this component has been shut down as part of this server security configuration. System administrators can use the

sp_configure enable ' ad hoc distributed Queries '. For more information about enabling ' ad hoc distributed Queries '

Information, see "Perimeter Configuration" in SQL Server Books Online.

To enable the method for the ad HOC distributed queries, 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 example

--Create a linked server

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

exec sp_addlinkedsrvlogin ' itsv ', ' false ', NULL, ' username ', ' password '

--query Example

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

--Import sample

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

--Delete a linked server when you no longer use it

exec sp_dropserver ' itsv ', ' droplogins '

--Connect remote/LAN data (Openrowset/openquery/opendatasource)

--1, OpenRowset

--query Example

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

--Raw cost surface

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

--Import a local table into a remote table

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

Select *from Local surface

--Update the surface

Update b

Set B. Column a=a. Column A

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

On A.column1=b.column1

--openquery usage requires creating a connection

--First create a connection create a linked server

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

--Query

SELECT *

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

--Import a local table into a remote table

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

SELECT * FROM local surface

--Update the surface

Update b

Set B. column b=a. Column B

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

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

--3, Opendatasource/openrowset

SELECT *

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

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

SELECT * FROM local surface

3, write their own examples

Some examples of--openrowset using OLE DB

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--openrowset using SQLNCLI (SQLNCLI above SqlServer2005 can be used)

SELECT * FROM OPENROWSET (' SQLNCLI ', ' (local) '; ') Sa '; ' ', ' select * from TB.dbo.school ') as T

SELECT * FROM OPENROWSET (' SQLNCLI ', ' server= "(local); Trusted_connection=yes ', ' select * from TB.dbo.school ') as T

SELECT * FROM OPENROWSET (' SQLNCLI ', ' server= "(local); Uid=sa; pwd=*** ', ' select * from TB.dbo.school ') as T

SELECT * FROM OPENROWSET (' SQLNCLI ', ' server= "(local); Uid=sa; pwd=*** ', TB.dbo.school) as T

SELECT * FROM OPENROWSET (' SQLNCLI ', ' server= "(local); Uid=sa; pwd=***;D atabase=tb ', ' select * from Dbo.school ') as T

--openrowset other use

Insert OPENROWSET (' SQLNCLI ', ' server= (local); Trusted_connection=yes ', ' select name from TB.dbo.school where id=1 ') VALUES (' GHJKL ')/* do not where all the same, insert a line * *

Update OPENROWSET (' SQLNCLI ', ' server= (local); Trusted_connection=yes ', ' select name from TB.dbo.school where Id=1 ') set name= ' Kkkkkk '

Delete from OPENROWSET (' SQLNCLI ', ' server= "(local); Trusted_connection=yes ', ' select name from TB.dbo.school where Id=1 ')