SQL Server connections across servers

From:https://www.cnblogs.com/railgunman/archive/2010/12/25/1916780.html

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

SQL Server connections across servers