Using OPENROWSET Remote Data Function in SQL-Server

OPENROWSET is a special Transact-SQL statements that help you write queries against remote data sources. It allows you to execute a pass-through query against an OLE DB data source. Here's how you can use it in a basic SQL query:

 
SELECT * 
FROM OPENROWSET('ProviderName', 'DataSource'; 'User'; 'Password', 'Query')

Some key points about OPENROWSET:

• ProviderName: This specifies the OLE DB provider to use. For example, 'SQLNCLI' for SQL Server Native Client.
• DataSource: This is the name of the server or data source to connect to.
• User and Password: Credentials for the connection. For security reasons, it's best to use integrated security when possible rather than hardcoding credentials.
• Query: The SQL statement you want to execute on the remote server.

Here's a practical example:

 
SELECT a.*
FROM OPENROWSET('SQLNCLI', 'Server=RemoteServerName;Trusted_Connection=yes;',
    'SELECT col1, col2 FROM RemoteDatabase.dbo.RemoteTable') AS a;

Security Considerations:

• Using OPENROWSET with hardcoded credentials in code is not recommended for security reasons. Instead, use Windows Authentication where possible.
• Be cautious with permissions; only users who need to access external data should have the rights to execute OPENROWSET.
• Performance Consideration:
  • OPENROWSET can be slower than linked servers for frequent queries since it establishes a connection each time it's called. For regular data access, consider setting up a linked server instead.

Remember, OPENROWSET requires the Ad Hoc Distributed Queries server configuration option to be enabled to use it without setting up a linked server. You can enable this with:

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

This configuration change might require server restart or RECONFIGURE command depending on the SQL Server version.

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

• OPENROWSET syntax 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'.
OPENROWSET Example
To use the OPENROWSET function, use the following parameters to access the Timesheets table:

Table name	Timesheets
Server	Boston
Login	amann
Password	amannpwd

Use the following Transact-SQL statement:

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.