SQL Server connections across servers
Connect remote SQL or insert data with OPENROWSET
--if it's just temporary access, you can use OPENROWSET directly
--query Example
SELECT * FROM OPENROWSET (' SQLOLEDB ', ' SQL Server name '; ' User name '; ' Password ', database name. dbo. Table name)
--import Example
SELECT * Into table from OPENROWSET (' SQLOLEDB ', ' SQL Server name '; ' User name '; ' Password ', database name. dbo. Table name)
--Create a linked server
exec sp_addlinkedserver ' srv_lnk ', ' ', ' SQLOLEDB ', ' Remote server name or IP address '
exec sp_addlinkedsrvlogin ' Srv_lnk ', ' false ', NULL, ' username ', ' password '
Go
--query Example
SELECT * from Srv_lnk. Database name. dbo. Table name
--import Example
SELECT * into table from Srv_lnk. Database name. dbo. Table name
--Remove linked server when no longer in use
exec sp_dropserver ' srv_lnk ', ' droplogins '
Go
--The following example accesses data from a table that is in another instance of SQL Server.
SELECT *from opendatasource (' SQLOLEDB ', ' Data source=servername; User Id=myuid; Password=mypass '). Northwind.dbo.Categories
The following is an example of a query that queries an EXCEL spreadsheet by using the OLE DB provider for Jet.
Select *from opendatasource (' microsoft.jet.oledb.4.0 ', ' Data source= ' C:financeaccount.xls '; User id=admin; password=; Extended properties=excel 5.0 ') ... xactions
As the project requires, the development process encounters the problem of accessing data across database servers.
A search on the network, the data is indeed a lot, but to solve their own problems also spent most of the day, here to spend some time to summarize.
1. Establishing the problem
Because of the need for cross-database server query operations, so I personally locate it in the distributed query data problems.
2. Solution
The first concept. Linked servers.
A linked server is typically used to handle distributed queries. When a client application executes a distributed query over a linked server, SQL Server parses the command and sends a rowset request to OLE DB. A rowset request can be in the form of a query or opening a base table from a provider.
Distributed queries can access data from a variety of heterogeneous data sources, which can be stored on the same or different computers. Microsoft®sql Server? 2000 supports distributed queries by using OLE DB (Microsoft Universal Data Access Application Interface (API) specification).
Does the linked server configuration allow Microsoft®sql server? Executes commands against OLE DB data sources on other servers. Linked servers have the following advantages:
Remote server access.
The ability to perform distributed queries, updates, commands, and transactions across heterogeneous data sources throughout the enterprise.
The ability to identify different data sources in a similar way.
Linked server components
The definition of a linked server specifies an OLE DB provider and an OLE DB data source.
An OLE DB provider is a dynamic-link library (DLL) that manages a specific data source and interacts with a specific data source. The OLE DB data source identifies a specific database that can be accessed through OLE DB. Although the data source queried through the definition of a linked server is typically a database, there are OLE DB providers for a variety of file and file formats, including text files, spreadsheet data, and full-text content retrieval results. The following table describes the OLE DB providers and data source samples that are most commonly used with SQL Server.
OLE DB provider OLE DB data source
Microsoft OLE DB Provider SQL Server instance for SQL Server
Path name of the Microsoft OLE DB provider MDB database file for Jet
ODBC data source name that is used by the Microsoft OLE DB Provider for ODBC to point to a specific database
Microsoft OLE DB Provider for Oracle points to the sql*net alias of the Oracle database
Content files for which the Microsoft OLE DB provider for Indexing Service can perform property search or full-text indexing
Description SQL Server is only tested for Microsoft OLE DB providers that are used for SQL Server, Jet, Oracle, Indexing Service, and ODBC, respectively. However, SQL Server distributed queries are intended to be used with any OLE DB provider that implements the required OLE DB interface.
In order for the data source to return data through the linked server, the OLE DB provider (DLL) for that data source must be on the same server as the SQL Server.
A linked server is typically used to handle distributed queries. When a client application executes a distributed query over a linked server, SQL Server parses the command and sends a rowset request to OLE DB. A rowset request can be in the form of a query or opening a base table from a provider.
Managing the definition of linked servers
When setting up a linked server, register the connection information and data source information for SQL Server. Once registered, the data source can always be referenced with a single logical name (that is, the instance name).
You can use stored procedures or SQL Server Enterprise Manager to create or remove definitions for linked servers.
Using Stored procedures:
Use sp_addlinkedserver to create a linked server definition. To view information about a linked server that is defined in a given instance of SQL Server, use Sp_linkedservers. For more information, see sp_addlinkedserver and Sp_linkedservers.
Use sp_dropserver to remove the definition of a linked server. You can also use this stored procedure to remove a remote server.
Using SQL Server Enterprise Manager:
Use the SQL Server Enterprise Manager console tree and the linked Servers node (within the Security folder) to create a linked server definition. Define the name, provider properties, server options, and security options for the linked server. For more information about the various ways to set up linked servers for different OLE DB data sources and the parameter values to use, see sp_addlinkedserver.
You can edit the definition of a linked server by right-clicking the linked server and clicking the Properties command.
You can delete the definition of a linked server by right-clicking the linked server and clicking the Delete command.
When you execute a distributed query against a linked server, specify a fully legal, four-part table name for each data source you want to query. The four-part name format should be: Linked_server_name.catalog.schema.object_name.
As we all know, in large-scale database system design, in order to improve the efficiency, it is unavoidable to put the unused business on different data instances, so we use the linked server, linked server for everyone in different services distributed data operation provides convenience. This example is based on the Windows 2003 operating system, SQL Server (SP4) database.
The first thing to do is to open your local server and the remote server's MSDTC:
Start-->microsoft SQL server--> Service Manager and select distributed Transaction Coordinator (this is DTC) in the service, click the "Start" button to start MSDTC.
After opening, register the remote server's information on the local server, create the LinkedServer, and manipulate the code as follows:
exec sp_addlinkedserver ' far_mdb ', ', ' SQLOLEDB ', ' YOIIO005 ', ', ', ' MDB '
Then use Sp_linkedservers to see if this server has successfully registered as a linked server, and if it has been registered successfully, use the
sp_addlinkedsrvlogin ' Far_mdb ', ' false ', NULL, ' sa ', ' * * * ', register the login account, then you can execute an SQL statement and try to see if you can manipulate the data from the linked server.
For example:
SELECT *
From OPENQUERY (Far_mdb, ' SELECT * from Mdb_member ')
This is the data from my linked server to query the Mdb_member table.
Note:
1. For more information on linked servers, see sp_addlinkedserver, sp_addlinkedsrvlogin, sp_addserver, sp_dropserver, sp_serveroption, sp_ in Books Online System-level stored procedures such as linkedservers.
2, about the creation of this linked server and its use in different environments (all intranet servers, one intranet, a public network server, all public network server) test, are passed.
3, sometimes in the stored procedure to access the linked server may encounter the following prompts, you must set the ANSI warning and ANSI nulls, the following method is set up as follows:
Set the properties of the local database--connect--Select ANSI warning and ANSI nulls
How to access the SQL Server database
Whether you are accessing a SQL Server database from a desktop Windows computer or on a Pocket PC device, you first need to establish a database connection. When you use Visual Studio 2005 to develop desktop Windows apps, developers can drag data tables from Server Explorer onto forms, but the. NET Compact Framework does not support data designers. This means that developers need to programmatically implement a database connection. The System.Data.SqlClient namespace of the. NET Compact Framework also does not support cross-server transactions and connection pooling. When designing a mobile application solution, the database transaction limit should occur in a separate database on the server.
Because the System.Data.SqlClient namespace of the. NET Compact Framework does not support database connection pooling, a delay occurs when establishing a SQL Server database connection on a Pocket PC device. In desktop Windows programming, it is required to minimize the time it takes to connect to the database and reduce the number of database connections. When developing a database application on a Pocket pc, the best strategy is to create a database connection in advance and try to keep the database connections throughout the application life cycle to reduce the number of connections.
The following design develops a versatile Pocket PC database application that describes how to access server-side data. This Pocket PC application needs to reference the System.Data.Common, System.Xml, and System.Data.SqlClient namespaces.
How to use SQL statements for cross-Library queries
Create a temporary table #tmp in the database database2 on machine B,
The content is the Table1 in the database database1 on machine A.
How do I do it with SQL statements? (Do not borrow DTS, etc.)
SQL code--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 Example
SELECT * into table from ITSV. Database name. dbo. Table name
--Remove linked server when no longer in use
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 local tables to remote tables
Insert OPENROWSET (' SQLOLEDB ', ' SQL Server name '; User name '; ' Password ', database name. dbo. Table name)
Select *from Local surface
--Update the local 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 to 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 local tables to remote tables
Insert OpenQuery (ITSV, ' SELECT * from database. dbo. Table name ')
SELECT * FROM local surface
--Update the local 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 local tables to remote tables
Insert OpenDataSource (' SQLOLEDB ', ' Data source=ip/servername; User id= Login name password= password '). database. dbo. Table name
SELECT * FROM local surface
With a linked server
OPENROWSET
OpenDataSource
All can
Linked servers. Database1.dbo.table1
Linked servers. Database1.dbo.table1
SQL Code
SELECT * Into #
From OPENROWSET (' SQLOLEDB ', ' IP '; ' Sa '; ', ' select * from Pubs.dbo.jobs ')
Build linked server with Opendatasource/openrowset fixed frequent use if you use it only occasionally
Issue: SQL Server connections across servers; Result: server connections across servers