Learn more about SQL Server tools


Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories


Using SQL Servers OPENROWSET to break the rules

By:   |   Read Comments (3)   |   Related Tips: More > Import and Export

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?

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
sp_configure 'Ad Hoc Distributed Queries', 1

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

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.

DROP VIEW vwGetDateTest

SELECT * FROM sysfiles A
OPENROWSET('SQLNCLI', 'server=(local);trusted_connection=yes', 'select getdate() AS CurrentDate')

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

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

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

Last Update:

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

View all my tips


Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 

Get free SQL tips:

*Enter Code refresh code     

Saturday, March 02, 2013 - 6:46:04 PM - marcelo miorelli Back To Top

-- 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_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) ,

insert into @TABLE_SPACE
exec ( @sql )

select *

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

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:

from openrowset('SQLOLEDB', 'SQLservA'; 'xxxxxxx'; 'yyyyyyyyyyyy',
  'SELECT max(rec_id) as max_recid_zip from xxxxxx.dbo.tableA') as a;
from openrowset('SQLOLEDB', 'SQLservA'; 'xxxxxxx'; 'yyyyyyyyyyyy',
  'SELECT max(rec_id) as max_recid_city from xxxxxx.dbo.tableB') as a;
from openrowset('SQLOLEDB', 'SQLservA'; 'xxxxxxx'; 'yyyyyyyyyyyy',
  'SELECT max(rec_id) as max_recid_county from xxxxxx.dbo.tableC') as 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
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.

Learn more about SQL Server tools