One of the most popular posts in the history of this little corner of the Internets is one from August, 2005, which describes a method for accessing stored procedure output in a SELECT statement which I’ve come to refer to as “the OPENROWSET trick.”
On the occasion of this blog’s 750th post(!), I thought it would be fun to return to the blog’s roots and present a long post full of T-SQL. This analysis was brought on by a not-so-innocent question on this technique from Giles Collingwood:
Nice - does it work with parameters too?
Nice - does it work with parameters too?
At this point, my answer is.. kind of.
I’ve revisited this code in light of Giles’ question. The current state of my research may be found at the bottom of this post. I’ve included new syntax to discover the local server/instance name and build it into the string, rather than relying on the “(local)” syntax of OPENROWSET. I’ve also included two different approaches to filtering the results of stored procedure output.
Referencing the code block.. once we declare our variables, we then build our instance name. This will include the machine name and, if applicable, the instance name:
We then use this value to build the first OPENROWSET call, define the parameters for it, and call it for spids 1 and 7, as well as the spid running the query:
-- Example 1c: return results for current spid SET @int = @@spid EXEC sp_executesql @SQLString, @Parms, @filter = @int Note that this approach will allow re-use of a cached query plan. But, as the comment in the SQL stream notes..
To this point, I’ve succeeded in using dynamic SQL to build a parameterized call:
Note that you can make this call via either sp_executesql or EXEC(). No query plan re-use here.
I’ve not yet succeeded in getting sp_executesql’s parameters inside the OPRENROWSET call, where I’d have the best of all world’s – parameterized calls to stored procedures with fully cacheable query plans.
I’ll keep playing with this as my workload permits. Thanks, Giles, for a great question!
SET NOCOUNT ON DECLARE @SQLString nvarchar(max), @Server nvarchar(max), @Parms nvarchar(500), @filter int, @int int, @string nvarchar(500)
-- build servername, with instancename as appropriate SET @Server = CAST(SERVERPROPERTY('MachineName') AS nvarchar(128)) + CASE CAST(SERVERPROPERTY('InstanceName') AS nvarchar(128)) WHEN NULL THEN '' ELSE N'\' + CAST(SERVERPROPERTY('InstanceName') AS nvarchar(128)) END -- Example 1: parameterized call to sp_who using sp_executesql SET @SQLString = N' SELECT * FROM OPENROWSET (''SQLOLEDB'',''Server=' + @Server + ';TRUSTED_CONNECTION=YES;'',''set fmtonly off exec master.dbo.sp_who'') AS tbl WHERE spid = @filter' SET @Parms = N'@filter int' -- Example 1a: return results for spid = 1 SET @int = 1 EXEC sp_executesql @SQLString, @Parms, @filter = @int -- Example 1b: return results for spid = 7 SET @int = 7 EXEC sp_executesql @SQLString, @Parms, @filter = @int -- Example 1c: return results for current spid SET @int = @@spid EXEC sp_executesql @SQLString, @Parms, @filter = @int -- this is all well and good, -- but we didn't pass a parameter to the stored procedures, -- we just filtered the results -- Example 2: parameterized calls to sp_who using sp_executesql and exec -- Example 2a: return results for spid = 1 SET @string = '1' SET @SQLString = N' SELECT * FROM OPENROWSET (''SQLOLEDB'',''Server=' + @Server + ';TRUSTED_CONNECTION=YES;'',''set fmtonly off exec master.dbo.sp_who ' + @string + ''') AS tbl' EXEC sp_executesql @SQLString EXEC (@SQLString) -- Example 2b: return results for current spid SET @string = CAST(@@spid as nvarchar(5)) SET @SQLString = N' SELECT * FROM OPENROWSET (''SQLOLEDB'',''Server=' + @Server + ';TRUSTED_CONNECTION=YES;'',''set fmtonly off exec master.dbo.sp_who ' + @string + ''') AS tbl' EXEC sp_executesql @SQLString EXEC (@SQLString) GO
this copyrighted material was originally posted at http://blogs.technet.com/wardpond.
the author and his employer are pleased to provide this content for you at that site, and via rss, free of charge and without advertising.
the author welcomes and appreciates links to and citations of his work. however, if you are viewing the full text of this article at any other website, be aware that its author does not endorse and is not compensated by any advertising or access fees you may be subjected to outside the original web and rss sites.
I have been trying to get this and the orginal to work on my SQL2005SP3 sysytem. Ad Hoc Distributed are enabled. I can get the first example from the orginal post (sp_who) to work.
The second example (joining sp_who and sp_lock) returns the error message
"OLE DB provider "SQLNCLI" for linked server "(null)" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
Msg 7320, Level 16, State 2, Line 2
Cannot execute the query "set fmtonly off exec master.dbo.sp_who" against OLE DB provider "SQLNCLI" for linked server "(null)". The provider could not support a required property.The provider indicates that conflicts occurred with other properties or requirements.
When I try sp_statistics, sp_HelpDB, etc. I get this error
"Msg 7357, Level 16, State 2, Line 2
Cannot process the object "set fmtonly off exec master.dbo.sp_helpdb 'TSysEvData' ". The OLE DB provider "SQLNCLI" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.
I would like to move on to the technique on this page but I want to understand what is happening first.
Appreciate the tip.
@Ray Herring: a couple of possibilities come to mind. I've encountered similar issues on several servers when using the "(local)" syntax; the workaround in this case was to explicitly name the server/instance as above.
If that's not the issue, there might be surface configuration or DBS registration issues for the server/instance, especially if this server houses multiple instances.
Please let me know if either of these fit your scenario; if not, I'll do more research as time permits.
FROM OPENROWSET('SQLOLEDB','Server=''server\instance'';TRUSTED_CONNECTION=YES;','set fmtonly off exec master.dbo.sp_who')
I get the follwoing error:
OLE DB provider "SQLNCLI" for linked server "(null)" returned message "Login timeout expired".
OLE DB provider "SQLNCLI" for linked server "(null)" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.".
Msg 65535, Level 16, State 1, Line 0
SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].
I've been working with this recently, and have a small problem. When I define the SELECT statement as a string from within the OPENROWSET, I am unable to use a WHERE clause that needs quotes.
Eg: OPENROWSET ('SQLOLEDB', 'server'; 'un'; 'pw', 'SELECT * FROM table WHERE field = 'A_VALUE'') as derived_table
This fails at the single quotes around A_VALUE.
Double quotes don't seem to help.
For the error with 'sp_HelpDB' please try:
'Set FMTOnly Off;Execute sp_HelpDb'
) As TheDataBases
Neil, type twice the single quote for the where value. In this example, MaritalStatus = ' ' M ' ' is using single quotes twice:
'Select * From AdventureWorks.HumanResources.Employee Where MaritalStatus = ''M'' And VacationHours > 90'
) As ProdEmployees