Select SQL Server if you manage to connect to a remote SQL Server instance, or choose Other Data Source to select from the available OLE DB server types from the Provider drop down list other than SQL Server. In the General tab of the New Linked Server window, choose a name for your linked server, then choose the type of the server you need to connect to using that linked server. Right-click on the Linked Server node and choose New Linked Server.
In order to configure a linked server using SQL Server Management Studio, expand the Server Objects node from the Object Explorer window. This request can be a query to execute or a table to be opened on that remote server.Ī Linked Server can be configured by using SQL Server Management Studio or using the sp_addlinkedserver T-SQL statement.
When the user executes a distributed query against a remote data source using a linked server, the SQL Server Engine parses that command and sends the requests to OLE DB. 'SELECT * FROM ' ) AS a Ī SQL Server Linked Server is used to access remote OLE DB data sources such as a SQL Server instance located outside the SQL Server or other systems such as Oracle, Microsoft Access and Excel, and execute the distributed T-SQL queries against them.Ī SQL Server Linked Server is different from ad hoc queries in that ad hoc queries open a temporary connection with the remote server and close it, where the permanent linked server is always available for use. You need to provide the provider name, the connection string and the query as follows: Using OPENROWSET requires enabling the Ad Hoc Distributed Queries advanced configuration option same as the OPENDATASOURCE function. Although the query might return multiple result sets, OPENROWSET returns only the first one. OPENROWSET is used in the FROM clause as a table name in the SELECT, INSERT, UPDATE, or DELETE statements. It also can be used for a bulk operation through the built-in BULK provider to read data from files. In order to use the OPENROWSET ad hoc method, you need to provide all connection information that is required to connect to the remote SQL server and many other resources. The second way to query a database hosted in a remote SQL Server is the OPENROWSET T-SQL function. It can be also used in the EXECUTE statement to run a remote stored procedure. OPENDATASOURCE can replace the server name in the four-part name of the table or view in a SELECT, INSERT, UPDATE, or DELETE statement. Once the Ad Hoc Distributed Queries advanced configuration option is enabled, the previous query will run successfully. If you try to run the below simple query that is using the OPENDATASOURCE T-SQL statement you will get the error: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Providers\Īlso you need to enable the Ad Hoc Distributed Queries advanced configuration option which is disabled by default in SQL Server. To be able to use the OPENDATASOURCE statement, you need to make sure that the DisallowAdhocAccess registry key is set to 0 for the provider you want to connect to other than the SQL Server, which can be found in the below path of the Registry Keys : And the init_string is the connection string of the remote server. Where the provider_name is the OLE DB provider used to access the data source. OPENDATASOURCE ( provider_name as char, init_string ) The first method to query a remote SQL Server database is the OPENDATASOURCE T-SQL function below:
SQL SERVER CONNECTION STRING PROVIDER NAME HOW TO
In this article, we will discuss these four methods and how to use it to query remote SQL Server databases. SQL Server provides us with four useful methods to connect to the remote database servers, even other database server types, and query its data within your T-SQL statement. But there will be situations in which you need to connect to a remote database that is located in a different instance in the same server or in a different physical server, and process its data in parallel with the local data processing. A common activity when writing T-SQL queries is connecting to local databases and processing data directly.
0 kommentar(er)
