Apply the Openrowset Function to Acquire Rowsets

In the previous blog on SQL Server’s OPENROWSET function we observed that while OPENROWSET is most often encountered in relation to linked servers, it is also valuable for directly reading flat files. It’s worth taking a moment to mention another less common application of OPENROWSET ( ).

If you wanted to write code that returned a table, it would be sensible to create a table-values function. However, sometimes you must deal with a legacy code the way it is, rather than the way you would like it to be. By linking a server to itself (we’ll see that shortly) you can use OPENROWSET to SELECT from a rowset returned by a T-SQL stored procedure. The T-SQL code presented can be downloaded here.

Openrowset

As is always the case when using OPENROWSET to access a server, we must ensure that the system configuration parameter “Ad Hoc Distributed Queries” is turned on, i.e. set to 1. To examine the value of or change this parameter, the configuration setting “show advanced options” must first be set to 1.

EXEC sp_configure ‘show advanced’, 1

RECONFIGURE WITH OVERRIDE

EXEC sp_configure ‘Ad Hoc Distributed Queries’, 1

RECONFIGURE WITH OVERRIDE

Once ad hoc distributed queries are allowed, we can write our SELECT query on a rowset returned by a stored procedure. In this example, we use [Ten Most Expensive Products] from the Northwind sample database.

SELECT *

FROM OPENROWSET(‘SQLNCLI11’,‘SERVER=Sylvester;Trusted_Connection=yes;’,

‘EXEC Northwind.dbo.[Ten Most Expensive Products]’) AS T

WHERE TenMostExpensiveProducts LIKE ‘C%’

storedprocresuts

In this example, we specify SQLLNCLI11 as the provider.You may see OPENROWSET queries using SQLOLEDB as the provider; this is the legacy method but still works in many cases. If you are version conscious, you can see the providers available to your server in the Server Objects | Linked Servers folder in the Management Studio object explorer. If you are not version conscious, SQLNCI without a version number will work.

linkedserverproviders

Stored procedures, of course, can return multiple rowsets. In this case, OPENROWSET will return only the results of the first select statement in the procedure.

Conclusion

The problem of how to capture a rowset from a stored procedure can be solved. All you have to do is use the OPENROWSET function and linking the server to itself.

Type to search blog.learningtree.com

Do you mean "" ?

Sorry, no results were found for your query.

Please check your spelling and try your search again.