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