Using SQL Servers OPENROWSET to break the rules
Written By: Ken Simmons -- 7/30/2008
-- read/post comments
-- print --
Rating:
(not rated yet)
Rate
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
Readers Who Read This Tip Also Read
Free Live Webcast
Comment or Ask Questions About This Tip
|
