Database Programming: The OPENROWSET Trick, Revisited

Artikkeli
03/20/2009

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?

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:

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

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

Note that this approach will allow re-use of a cached query plan. But, as the comment in the SQL stream notes..

-- this is all well and good,
-- but we didn't pass a parameter to the stored procedures,
-- we just filtered the results

To this point, I’ve succeeded in using dynamic SQL to build a parameterized call:

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

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!

-wp

Code Block

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

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 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 https://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.

Comments

Anonymous
January 01, 2003
Neil, type twice the single quote for the where value. In this example, MaritalStatus = ' ' M ' ' is using single quotes twice: Select --Columns: ProdEmployees.* From OpenRowSet ( 'SqlNCli', 'Server=(local);Trusted_Connection=yes;', 'Select * From AdventureWorks.HumanResources.Employee Where MaritalStatus = ''M'' And VacationHours > 90' ) As ProdEmployees
Anonymous
January 01, 2003
For the error with 'sp_HelpDB' please try: Select --Columns: TheDataBases.* From OpenRowSet ( 'SqlNCli', 'Server=(local);Trusted_Connection=yes;', 'Set FMTOnly Off;Execute sp_HelpDb' ) As TheDataBases
Anonymous
January 01, 2003
@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. Thanks! -wp
Anonymous
June 06, 2009
The comment has been removed
Anonymous
November 04, 2009
The comment has been removed
Anonymous
January 19, 2010
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. Any ideas? Thanks

Jaa

Database Programming: The OPENROWSET Trick, Revisited

Comments

Lisäresursseja