Using OPENROWSET Remote Data Function in SQL-Server

To use the `OPENROWSET` function for accessing remote data in SQL Server 2019, follow these steps:

1. Enable Ad Hoc Distributed Queries: Before you can use `OPENROWSET`, ensure that the Ad Hoc Distributed Queries option is enabled. This is done by executing the following T-SQL command:
   

   sp_configure 'show advanced options', 1;
   RECONFIGURE;
   sp_configure 'Ad Hoc Distributed Queries', 1;
   RECONFIGURE;
   

2. Syntax of OPENROWSET: The basic syntax of `OPENROWSET` is as follows:
   

   OPENROWSET 
   ( 
   'provider_name', 
   'datasource';'user_id';'password',
   'query'
   )
   

   • `provider_name`: The name of the OLE DB provider.
   • `datasource`: The connection string to the remote data source.
   • `user_id`: The user ID for authentication.
   • `password`: The password for authentication.
   • `query`: The query to be executed on the remote data source.
3. Example Usage: Here's an example of how to use `OPENROWSET` to access a remote SQL Server database:
   

   SELECT * 
   FROM OPENROWSET('SQLNCLI', 
      'Server=remote_server_name;Trusted_Connection=yes;', 
      'SELECT column1, column2 FROM remote_database.dbo.remote_table')
   

   
   In this example, `SQLNCLI` is the SQL Server Native Client OLE DB provider. The connection string specifies the remote server name and uses trusted connection for authentication. The query retrieves specific columns from a table in the remote database.
4. Considerations:
   • Security: Be cautious with credentials in your queries. Avoid hardcoding sensitive information in scripts.
   • Performance: `OPENROWSET` is intended for ad hoc connections. For frequent connections to the same data source, consider linked servers instead.
   • Provider Support: Ensure that the OLE DB provider you are using is compatible with SQL Server 2019.
   • Error Handling: Be prepared to handle errors related to network issues, access permissions, and data compatibility.
5. Troubleshooting: Common issues with `OPENROWSET` include connection failures, permission errors, and provider not found errors. Check your connection string, credentials, and the availability of the OLE DB provider.
6. Documentation: Always refer to the latest SQL Server documentation for any specific details or updates regarding the usage of `OPENROWSET`.
   Remember, while `OPENROWSET` provides a flexible way to access remote data, it should be used judiciously considering the aspects of security, performance, and maintainability.

OPENROWSET allows you to construct a query against a remote OLE DB data source without linking a server. As opposed to a linked server, the OPENROWSET statement does not retain any information, and it must be used as a one-time-only query. The OPENROWSET statement can be used in the following situations:

1. From within a FROM clause of a query, which means that it is useful for performing quick look-ups of data located remotely
2. When you allow users to specify where the data is located and do not know in advance what servers will be accessed

The OPENROWSET function uses the syntax shown in the following display to access a SQL Server 2000 OLE DB data source:

OPENROWSET ('provider_name' 
   , { 'datasource' ; 'user_id' ; 'password' 
     | 'provider_string' } 
   , { [ catalog. ] [ schema. ] object 
     | 'query' } 
   ) 

Note that there is an extended syntax available to use for OLE DB data sources other than SQL Server 2012.
The syntax shown above is for SQL Server 2012 only, because the first argument is the string literal 'SQLOLEDB'.

SELECT t.*

FROM OPENROWSET('SQLOLEDB','Boston';'amann';'amannpwd',
'SELECT * FROM Timesheets') AS t

As you can see, the OPENROWSET function is used in the FROM clause and aliased so that it can be easily used within the rest of your query. Therefore, you can treat the results of the OPENROWSET function as if they were a table.
In the next lesson, you will learn how to use the OPENQUERY function.