Http://blog.chinaunix.net/u2/65729/showart_1728182.html
Data accessing other remote databases in ms SQL Server can be achieved through the openquery (TRANSACT-SQL) function. First, create a linked server and maintain remote data like normal tables. How to Create a ms SQL server linked server:
Exec Sp_addmediaserver
'Ltest' ,
'' ,
'Msdasql' ,
Null ,
Null ,
'Driver = {SQL Server}; server = 192.168.0.20.; uid = sa; Pwd = sa ;' Check the availability of the linked server:
Select * From
Openquery (Lktest, 'Select * From pubs.dbo.jobs' If the remote ms SQL Server database cannot be accessed, the following error occurs: [Microsoft] [odbc SQL Server Driver] [SQL Server] [OLE/DB Provider returned message:
[Microsoft] [odbc SQL Server Driver] [SQL Server] user 'sa 'Logon Failed. Cause: it is not associated with a trusted SQL server connection.] The connection still fails after checking that the network and SA passwords are correct. You can try to install SP3 of ms SQL Server.
Openquery () Describes how to execute a specified transfer query on a given linked server. This server is an ole db data source. Openquery can be referenced in the from clause of the query, as if it is a table name. Openquery can also be referenced as the target table of the insert, update, or delete statement. However, this depends on the functions of the ole db access interface. Although a query may return multiple result sets, openquery returns only the first result set.
Syntax
Openquery (pai_server, 'query ')
Parameters
Pai_server
The identifier of the linked server name. 'Query'
Query string executed on the linked server. The maximum length of this string is 8 KB.
Remarks
Openquery does not accept its parameter variables. In SQL Server 2000 and later versions, openquery cannot be used to execute extended stored procedures on linked servers. However, you can run the extended stored procedure on the linked server by using the four-part name. For example:
Exec seattlesales. master. DBO. xp_msver
Permission
Any user can execute openquery. The permissions used to connect to the remote server are obtained from the settings defined for the linked server.
Example
A. Execute select to pass the query
The following example uses "Microsoft Access interface for Oracle" to create a connection server named oraclesvr for Oracle databases. Then, this example uses select to pass the query to the linked server. Note:
This example assumes that an Oracle database alias named orcldb has been created. Exec sp_add1_server 'oraclesvr ',
'Oracle 7.3 ',
'Msdaora ',
'Orcldb'
Go
Select *
From openquery (oraclesvr, 'select name, ID from Joe. Titles ')
Go B. Execute update transfer Query
Update openquery (pai_server, 'select title, content from msgs where id = 1 ')
Set Title = 'newtitle', content = 'newcontent'; C. Execute insert to pass the query
Insert openquery (pai_server, 'select title, content from msgs ')
Values ('title', 'content'); D. Execute Delete to pass the query
The following example uses Delete to pass query deletion.
Delete openquery (oraclesvr, 'select name from Joe. Titles where name = ''newtitle ''');
Reference
Sp_linkedservers: Check the current linked server and run the command to view the result.
Sp_addmediaserver
Manage linked servers in Enterprise Manager
