Learn more about SQL Server tools

mssqltips logo
giveaway
 

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


ALERT: Share your SQL Server knowledge and become a MSSQLTips author


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


Last Update:


signup button

next tip button



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 


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_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 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:

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
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