March 21, 2016 at 9:34 pm
Comments posted to this topic are about the item Insert Stored Procedure Results Into New Table Without Using OPENQUERY
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 22, 2016 at 6:01 am
You offer a very creative solution that has the ability to have dynamic columns. However, practically since you almost always need to know the columns names any way to work with the results wouldn't the following be more straight forward
CREATE TABLE #results
(
spid INT
,ecid INT
,status VARCHAR(255)
,loginname VARCHAR(128)
,hostname VARCHAR(128)
,blkINT
,dbname VARCHAR(128)
,cmd VARCHAR(255)
,request_id INT
)
INSERT INTO #results
( spid ,
ecid ,
status ,
loginname ,
hostname ,
blk ,
dbname ,
cmd ,
request_id
)
EXEC sys.sp_who;
SELECT * FROM #results
March 22, 2016 at 6:51 am
Mike Is Here (3/22/2016)
You offer a very creative solution that has the ability to have dynamic columns. However, practically since you almost always need to know the columns names any way to work with the results wouldn't the following be more straight forward
CREATE TABLE #results
(
spid INT
, ecid INT
, status VARCHAR(255)
, loginname VARCHAR(128)
, hostname VARCHAR(128)
, blk INT
, dbname VARCHAR(128)
, cmd VARCHAR(255)
, request_id INT
)
INSERT INTO #results
( spid ,
ecid ,
status ,
loginname ,
hostname ,
blk ,
dbname ,
cmd ,
request_id
)
EXEC sys.sp_who;
SELECT * FROM #results
Thanks for the feedback.
Consider the case where you do not own the procedure code (e.g. sys.sp_who, a stored procedure in a third-party vendor's database, a stored procedure in a database owned by a different development team, etc.) and a new column is added to the output interface of that stored procedure without you being notified, after your code has been deployed in production. The code that follows the pattern where the table is declared up front will encounter this error:
[font="Courier New"]Msg 213, Level 16, State 7, Procedure some_stored_procedure, Line 5
Column name or number of supplied values does not match table definition.[/font]
Using the technique I showed, since the code should not need to reference the new column to function properly given it was produced before the column was added, the code will more readily withstand change. This behavior is also true of columns added at any ordinal position within the resultset, not only at the end.
I agree declaring the table up front is a straightforward approach and I use it where I own both sides of the code. It will perform better in the large and is a bit simpler to follow. The technique I showed is an alternative approach that offers some advantages over using OPENQUERY to do things dynamically but I concede it introduces some drawbacks as well.
One other area where I will use the dynamic approach is when I am too lazy to gather the necessary metadata to be able to build the declared table up front, e.g. when I am doing admin work and want to quickly capture the results of a stored procedure into a table.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 22, 2016 at 7:11 am
If you want to make this super-robust a guard that tests if sys.dm_exec_describe_first_result_set_for_object can determine the metadata could be worthwhile. For example, using this technique for sp_who2 does not work:
error_numbererror_severityerror_state error_messageerror_typeerror_type_desc
11526161The metadata could not be determined because statement 'delete #tb1_sysprocesses
where lower(status) = 'sleeping'
and upper(cmd) in (' in procedure 'sp_who2' uses a temp table.10TEMPORARY_TABLE
March 22, 2016 at 7:37 am
Orlando Colamatteo (3/22/2016)
Mike Is Here (3/22/2016)
You offer a very creative solution that has the ability to have dynamic columns. However, practically since you almost always need to know the columns names any way to work with the results wouldn't the following be more straight forward
This is what the article is trying to prevent.
FM
March 22, 2016 at 8:07 am
gshouse (3/22/2016)
If you want to make this super-robust a guard that tests if sys.dm_exec_describe_first_result_set_for_object can determine the metadata could be worthwhile. For example, using this technique for sp_who2 does not work:error_numbererror_severityerror_state error_messageerror_typeerror_type_desc
11526161The metadata could not be determined because statement 'delete #tb1_sysprocesses
where lower(status) = 'sleeping'
and upper(cmd) in (' in procedure 'sp_who2' uses a temp table.10TEMPORARY_TABLE
Agreed. Sometimes, no matter which technique we choose, the stored procedure will defy any dynamic handling. sp_who2 is one of those cases where we must declaratively define the table we want to insert into. Thanks for making the point.
As for making the code super-robust, I agree it can detect this case and return a nicer error message. I have submitted an edit for the stored procedure code in the article to add a check and display a nicer error message.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 22, 2016 at 8:28 am
Nice trick, thanks for sharing.
Hakim Ali
www.sqlzen.com
March 22, 2016 at 8:42 am
To quote Phil McCracken...
"SQL Spackle" is a collection of short articles written based on multiple requests for similar code or to share short methods for getting certain things done. These short articles are NOT meant to be complete solutions. Rather, they are meant to "fill in the cracks."
--Phil McCracken
The only thing the author didn't do was to explain that fact clearly enough until he got to the "Conclusion" of the article and I consider this article to be an awesome "SQL Spackle" article even if that's not clearly stated up front or in the title.
To be honest, I can't see why anyone would rate this article at less than "Must Read (5 stars)". It's an introduction to a powerful alternative to OPENROWSET whether a Linked Server is used or not. It's a highly effective "basic tool" that has been introduced and, like any other good tool, opens up possibilities that one may have not previously thought of. And, like any other basic tool in the world of computers, "Computers are an imagination-limited tool. If you have a limited imagination, it will be a limited tool. -- Jeff Moden circa 1980".
For example, it's been suggested that it's much more effective to actually find out what the return meta data consists of and to hard code the creation of the table. I couldn't agree more but let me ask you... how did people do that in the past? By reading documentation (not always available), deep diving the code in the proc, by running a hard-coded OPENROWSET (it can't take dynamic parameters), or by (gasp!) writing some dynamic SQL around OPENROWSET. You couldn't put it into a general purpose function because OPENROWSET can't take dynamic parameters and you can't execute dynamic SQL within a function without some other grand and totally non-obvious methods that also use OPENROWSET.
This article explains a method that you could easily encapsulate in a utility function to make it possible to easily determine what the meta data is and to write the CREATE TABLE statement for you if that's what you desired.
The article also doesn't explain how to solve the travesty produced by some stored procedures such as the problem with 2 columns being returned with the same name as sp_WHO2 does... use your imagination because this basic tool allows for a solution where even OPENROWSET does not. 😉
The technique in this article is a tool and, like any other tool, it requires your brain and imagination to use it. The article was never intended to present a panacea set of solutions for these kinds of problems. It was meant to present a tool for the imagination.
My only bitch about the article is that I couldn't give it 10 stars. 😀
Very well done. Thanks for taking the time to write up this very useful and awesome technique. Heh... it took some imagination. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
March 22, 2016 at 8:54 am
Great read and thanks for taking the time to write it up and sharing.
Getting Jeff Moden's blessing observation and support is fantastic, as I value his opinions tremendously.
(modified: see Jeff's response below :-D)
March 22, 2016 at 9:05 am
qbrt (3/22/2016)
Great read and thanks for taking the time to write it up and sharing.Getting Jeff Moden's blessing and support is fantastic, as I value his opinions tremendously.
Thanks for the feedback but it wasn't a "blessing"... Rather, it was an "observation". The author did this on his own and it opened up some possibilities for me, as well. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
March 22, 2016 at 9:24 am
This article is GREAT! I read it on the train on my way to the office, I couldn't wait to get to the office (:w00t: yes I said that) to try it. I even tried to login remotely to my desk to try it, but my signal on a moving train was not stable enough.
FM
March 22, 2016 at 10:29 am
This doesn' work:
SELECT
*
INTO #result
FROM
OPENQUERY([LOCALHOST], 'EXEC sys.sp_who;')
select * from #result
Msg 7202, Level 11, State 2, Line 2
Could not find server 'LOCALHOST' in sys.servers. ..
This one, however, works:
SELECT
*
INTO #result
FROM
OPENROWSET('SQLNCLI', 'server=(local);trusted_connection=yes',
'set fmtonly off; exec sp_who;')
select * from #result
If you replace sp_who with sp_who2 it doesn't work, not even with the solution in the article. For the second query:
Msg 11526, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1
The metadata could not be determined because statement 'delete #tb1_sysprocesses
where lower(status) = 'sleeping'
and upper(cmd)' in procedure 'sp_who2' uses a temp table.
For the article query:
(0 row(s) affected)
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'ADD'.
(0 row(s) affected)
Msg 8101, Level 16, State 1, Line 32
An explicit value for the identity column in table '#result' can only be specified when a column list is used and IDENTITY_INSERT is ON.
Don't just give the hungry man a fish, teach him how to catch it as well.
the sqlist
March 22, 2016 at 10:35 am
Excellent article! My only additional suggestion would be to put your code in the master db, and start the proc name with sp_, so that the code can easily be called from any db / db context.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 22, 2016 at 10:38 am
And because the whole "trick" is using the dm_exec_describe_first_result_set_for_object function I directly tried it with sp_who and sp_who2 and again, for sp_who2 doesn't work, it returns on empty row with the error:
select * from sys.dm_exec_describe_first_result_set_for_object(OBJECT_ID('sys.sp_who2'), 0)
The metadata could not be determined because statement 'delete #tb1_sysprocesses
where lower(status) = 'sleeping'
and upper(cmd)' in procedure 'sp_who2' uses a temp table.
So I guess there is no difference between using this or what I have been using for a log time now:
SELECT
*
INTO #result
FROM
OPENROWSET('SQLNCLI', 'server=(local);trusted_connection=yes',
'set fmtonly off; exec sp_who2;')
select * from #result
Don't just give the hungry man a fish, teach him how to catch it as well.
the sqlist
March 22, 2016 at 11:15 am
Damned Temp Tables... they're both the boon and the bane of stored procedures. :pinch:
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 42 total)
You must be logged in to reply to this topic. Login to reply