Using SQL Servers OPENROWSET to break the rules

By: Ken Simmons | Updated: 2008-07-30 | Comments (3) | Related: More > Import and Export

Problem

Many times I would like to insert the results of a stored procedure into a table so I can do some extra processing with the result set. I always have to create the table first in order to perform an Insert Into Exec on the desired stored procedure since Exec Into is not an option. Is there a way to do this without having to manually create the table each time?

Solution

Having to manually create the table can be avoided by using the OPENROWSET function. In fact, there are a couple of other ways you can use OPENROWSET that will allow you to do things in SQL Server that would otherwise be against the rules.

The first thing you have to do is enable OPENROWSET and OPENDATASOURCE. You can do this by executing the following script.

sp_configure 'show advanced options', 1
reconfigure
go
sp_configure 'Ad Hoc Distributed Queries', 1
reconfigure

Now lets take a look at using OPENROWSET to perform a Select Into operation.

In these examples we are using the (local) server, so if you have a named instance you will need to change this to the appropriate server and instance name such as SERVER1\Instance1.

--Drop the table if it exists
IF OBJECT_ID('tempdb.dbo.#JobInfo') IS NOT NULL
DROP TABLE #JobInfo

SELECT * INTO #JobInfo
FROM OPENROWSET('SQLNCLI', 'server=(local);trusted_connection=yes',
'set fmtonly off exec msdb.dbo.sp_help_job')

Now you are able to work with the output of the stored procedure without having to go through the pain of creating the table schema.

SELECT * FROM #JobInfo WHERE enabled = 1

You can also filter the stored procedure results directly from OPENROWSET. The following query produces the same output.

SELECT * FROM OPENROWSET('SQLNCLI', 'server=(local);trusted_connection=yes',
'set fmtonly off exec msdb.dbo.sp_help_job')
WHERE enabled = 1

Using SET FMTONLY OFF before executing the stored procedure ensures that we get the results of the stored procedure instead of just the metadata. Also, it should be noted that OPENROWSET will not work in all cases, for example a stored procedure that has duplicate column names. The following code will generate an error because sp_who2 has the spid column listed twice.

SELECT * FROM OPENROWSET('SQLNCLI', 'server=(local);trusted_connection=yes', 'set fmtonly off exec sp_who2')

Here is the error that is generated.

Msg 492, Level 16, State 1, Line 1
Duplicate column names are not allowed in result sets obtained through OPENQUERY and OPENROWSET. The column name "SPID" is a duplicate.

OPENROWSET is also useful anywhere you are not allowed to use Nondeterministic Functions. The following is an example of using Getdate() inside a view.

IF OBJECT_ID('vwGetDateTest') IS NOT NULL
DROP VIEW vwGetDateTest
GO

CREATE VIEW vwGetDateTest
AS
SELECT * FROM sysfiles A
CROSS JOIN
OPENROWSET('SQLNCLI', 'server=(local);trusted_connection=yes', 'select getdate() AS CurrentDate')
GO

Now you can query the view and it will return the nondeterministic current datetime.

SELECT * FROM vwGetDateTest

The entire query could have been written in the OPENROWSET, but I wanted to show the Join syntax.

Lets say you are testing and need a view to run longer than normal. Here is an example using the Waitfor Delay command. The following will cause the view to run for 10 seconds longer than the original query.

IF OBJECT_ID('vwWaitForDelayTest') IS NOT NULL
DROP VIEW vwWaitForDelayTest
GO

CREATE VIEW vwWaitForDelayTest
AS
SELECT * FROM sysfiles A
UNION ALL
SELECT * FROM OPENROWSET('SQLNCLI', 'server=(local);trusted_connection=yes', 'Select * from sysfiles where 1=1; waitfor delay ''00:00:10''')
GO

Now you can query the view.

SELECT * FROM vwWaitForDelayTest

Next Steps

As you can see OPENROWSET can be very useful when you need to perform actions that would otherwise be impossible by masking the operation as a query.
If you are using SQL 2000 you can change the provider from SQLNCLI to SQLOLEDB. A complete reference on OPENROWSET can be located on MSDN. http://msdn.microsoft.com/en-us/library/ms190312.aspx

About the author

Ken Simmons is a database administrator, developer, SQL Server book author and Microsoft SQL Server MVP.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2008-07-30

Comments For This Article

Saturday, March 2, 2013 - 6:46:04 PM - marcelo miorelli

-- 02-MAR-2013
-- how to insert into a table variable from exec using OPENQUERY
-- marcelo miorelli

declare @sql nvarchar(max)
select @sql = 'SELECT * FROM OPENQUERY(' + quotename(@@servername) + ',' + '''' + ' exec sp_get_size_of_all_tables ' + '''' + ')'
declare @TABLE_SPACE table
(
    ffilegroup_name sysname   ,
    SSCHEMA     sysname        ,
    TABLE_NAME SYSNAME ,
    TABLE_ROWS int ,
    RESERVED int ,
    DATA   int ,
    INDEX_SIZE int ,
    UNUSED   int ,
    USED_MB    numeric(18,4) ,
    USED_GB    numeric(18,4) ,
    AVERAGE_BYTES_PER_ROW  numeric(18,5) ,
    AVERAGE_DATA_BYTES_PER_ROW numeric(18,5) ,
    AVERAGE_INDEX_BYTES_PER_ROW numeric(18,5) ,
    AVERAGE_UNUSED_BYTES_PER_ROW numeric(18,5)
)

insert into @TABLE_SPACE
exec ( @sql )

select *
from @TABLE_SPACE

Wednesday, January 25, 2012 - 5:52:32 PM - it_is_me

how can I include multiple queries via openrowset? I have multiple servers, and each has tableA, tableB, tableC, and tableD. ALL the servers must have identical data in the above mentioned tables. To that end, I am selecting the Max(rec_id) in each of the tables. I am using the following:

SELECT a.*
from openrowset('SQLOLEDB', 'SQLservA'; 'xxxxxxx'; 'yyyyyyyyyyyy',
  'SELECT max(rec_id) as max_recid_zip from xxxxxx.dbo.tableA') as a;
SELECT a.*
from openrowset('SQLOLEDB', 'SQLservA'; 'xxxxxxx'; 'yyyyyyyyyyyy',
  'SELECT max(rec_id) as max_recid_city from xxxxxx.dbo.tableB') as a;
SELECT a.*
from openrowset('SQLOLEDB', 'SQLservA'; 'xxxxxxx'; 'yyyyyyyyyyyy',
  'SELECT max(rec_id) as max_recid_county from xxxxxx.dbo.tableC') as a;
SELECT a.*
from openrowset('SQLOLEDB', 'SQLservA'; 'xxxxxxx'; 'yyyyyyyyyyyy',
  'SELECT max(rec_id) as max_recid_state from xxxxxx.dbo.tableD') as a;

and then repeat the process for SQLservB, SQLservC, etc.

is there some way to automate this process???

thnx to one and all for any suggestions......

Thursday, August 14, 2008 - 6:26:45 AM - unclebiguns	Back To Top (1633)
An interesting article that gives some non-intuitive ways to solve some common problems. The only issue I would take with it, is that you have to open up your surface area in order to use it. So I would likely look for another way to solve each of these issues, unless I had another reason to enable ad-hoc queries.