SQL OpenRowSet

In simple words OpenRowSet is to fetch data from other sources which are not linked to the sql server in which a query is executing. When we say other data source it can be another sql server instance which is not linked to our sql server instance.

For example if you want to know the details of available databases in another sql server (local\sqlexpress) from your local instance of sql server, you can use the below query to execute

SELECT  *
FROM OpenRowSet ('SQLOLEDB',
                 'Server=(local)\sqlexpress;TRUSTED_CONNECTION=YES;',
                 'select * from sys.sysdatabases')

But when you execute this most probably you will receive an error as follows


Msg 15281, Level 16, State 1, Line 1

SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.

What should we do now?

As usual google and find out the answer.Its only about enabling ad hoc distributed queries as follows

sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;

Now run the query again.You will see the result.ie you have queried another independent data source from your sql server.You can even query non databases such as excel,xml etc…

This is the mechanism  if you want to communicate between 2 sql databases which are residing in 2 different server instances without using linked servers.Interesting.Isn’t it.

Note : If the fmtonly is on it will return only the metadata.If you want to get the real data ,make sure fmtonly is off using the below statement in the server where you are running the above sql.

set fmtonly off