March 22, 2016 at 11:40 am
Thank you so much for sharing, this is great! As I am new to XML in SSMS, I was able to understand it but I am not sure if I would use that FOR XML.
This would be my work around of your XML logic, I hope you don't mind 🙂
-- variable used to store list of columns output by stored procedure
DECLARE @column_list NVARCHAR(MAX)='';
;with Columnlist as
(
SELECT QUOTENAME(name) + N' ' + system_type_name + N' NULL' Columns, p2.column_ordinal
FROM sys.dm_exec_describe_first_result_set_for_object(OBJECT_ID(@object_name), 0) AS p2
)
select @column_list=@column_list+','+Columns
from Columnlist
ORDER BY column_ordinal
select @column_list=stuff(@column_list, 1,1,'')
exec (N'ALTER TABLE ' + @table_name + ' ADD ' + @column_list);
March 22, 2016 at 10:56 pm
Jeff Moden (3/22/2016)
@Orlando,Very well done. Thanks for taking the time to write up this very useful and awesome technique. Heh... it took some imagination. 😀
The praise is much appreciated, Jeff. It did take a bit of imagination and blending a few techniques picked up along the way to bring it all together. You were spot on with your assessment as well. This was more spackle than it was a complete solution. I just wanted to demo the one option using the technique and hopefully open up a bit more options for folks. I am extremely pleased it has been well-received.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 22, 2016 at 11:05 pm
the sqlist (3/22/2016)
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. ..
And it won't, unless you already had or setup a new Linked Server named LOCALHOST first. The code in the article was meant to show the query-form for the solution commonly offered and was not meant to be a working query on everyone's machine. If you want to try it out you can create the Linked Server this way...
EXEC sys.sp_addlinkedserver
@server = 'LOCALHOST',
@srvproduct = 'SQLSERVER',
@provider = 'SQLNCLI',
@datasrc = @@SERVERNAME;
...and then try the query that uses OPENROWSET from in the article.
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.
The error message you see from the article query is likely due to you having run all the code in one batch. SQL Server will read ahead and fail with the error you showed if the temp table already exists from a previous run. You should start in a new query window (i.e. with a new session) or run the code from the article in blocks where the temp table is recreated and then populated in a second batch.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 22, 2016 at 11:23 pm
the sqlist (3/22/2016)
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.
Because of how sp_who2 was written, namely that the resultset it delivers comes out of a dynamic SQL statement and therefore the shape cannot be determined until runtime, no technique is capable of inferring the metadata prior to execution.
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
In terms of the results, arguably not. However there are still quite a few differences between the two techniques:
1. Performance. Having to leave T-SQL to enter a TCP/IP context and then loopback into a T-SQL context using either a Linked Server with OPENQUERY (as shown in the article) or an ad hoc connection using OPENROWSET (as you have shown) will not scale as well as the dynamic solution in the article since we never leave the T-SQL context.
2. Security. OPENQUERY and OPENROWSET require the caller to have more permissions than the solution in the article which should not be dismissed if you are managing an instance and want to adhere to least privilege.
3. Flexibility. Jeff touched on this point in his earlier post and I will elaborate on it a bit here. The technique in the article allows us the opportunity to control the creation of the temporary table separate from the inserting of data into that temp table using INSERT...EXEC, unlike the OPENQUERY and OPENROWSET techniques which must do both in one SELECT...INTO statement. Having the option to separate those operations gives us more flexibility to deal with stored procedures that return a resultset where multiple columns have the same name.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 22, 2016 at 11:42 pm
ScottPletcher (3/22/2016)
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.
Thanks for the positive feedback Scott. I like your idea and think it would be a nice addition to master. I played with the wrapper proc for a few minutes and am leaning towards it possibly needing to go purely dynamic due to the fact that database-qualifying sys.dm_exec_describe_first_result_set_for_object does not seem to work as I would expect. While in the context of master, this query is not delivering the metadata for a proc in tempdb:
USE master;
SELECT *
FROM tempdb.sys.dm_exec_describe_first_result_set_for_object(OBJECT_ID('tempdb.dbo.some_stored_procedure', 'P'), 0) AS p2;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 22, 2016 at 11:51 pm
OceanDrop (3/22/2016)
Thank you so much for sharing, this is great! As I am new to XML in SSMS, I was able to understand it but I am not sure if I would use that FOR XML.
It is my pleasure. Thanks for the positive feedback.
This would be my work around of your XML logic, I hope you don't mind 🙂
-- variable used to store list of columns output by stored procedure
DECLARE @column_list NVARCHAR(MAX)='';
;with Columnlist as
(
SELECT QUOTENAME(name) + N' ' + system_type_name + N' NULL' Columns, p2.column_ordinal
FROM sys.dm_exec_describe_first_result_set_for_object(OBJECT_ID(@object_name), 0) AS p2
)
select @column_list=@column_list+','+Columns
from Columnlist
ORDER BY column_ordinal
select @column_list=stuff(@column_list, 1,1,'')
exec (N'ALTER TABLE ' + @table_name + ' ADD ' + @column_list);
I do not mind at all. I am always looking for different and interesting ways to do things. I would just be very careful with the technique you showed to concatenate strings by appending column-values to a variable. The technique is not technically documented by Microsoft and there are proofs online showing how column values can be skipped when applying an order by, which is what you have shown. Erland Sommarskog has many posts out there cautioning against using the technique you showed. I used to favor the technique you showed but his arguments swayed me away from the technique and towards the XML technique even though I feel the XML technique is not as readable or approachable for folks unfamiliar with XML in general or how it is integrated into T-SQL.
Further reading: http://www.sqlservercentral.com/Forums/FindPost1753157.aspx
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 23, 2016 at 2:26 am
Nice trick, it-s safe to add the missing columns before insert.
Thanks for sharing.
Iulian
March 23, 2016 at 3:42 am
Great little article Orlando. And a nice neat trick for what I find is a common requirement.
</ soapbox on
It's just a shame that Microsoft don't have a single command to do this automatically without having to do the "un-natural acts" that you have had to go through.
After all, the requirement is not new, it has been around since the Sybase days. A simple command to analyse output and create a table with automatic column names and data types is a very basic requirement.
I love sql server and having it in the cloud is great. But they have missed paying attention to some of the very simplest basics. Shame sql server still lags behind in the enterprise class stakes.
/> soapbox off ! 🙂
Rgds, Dave.
March 23, 2016 at 11:37 am
dave hants (3/23/2016)
Great little article Orlando. And a nice neat trick for what I find is a common requirement.</ soapbox on
It's just a shame that Microsoft don't have a single command to do this automatically without having to do the "un-natural acts" that you have had to go through.
After all, the requirement is not new, it has been around since the Sybase days. A simple command to analyse output and create a table with automatic column names and data types is a very basic requirement.
I love sql server and having it in the cloud is great. But they have missed paying attention to some of the very simplest basics. Shame sql server still lags behind in the enterprise class stakes.
/> soapbox off ! 🙂
Rgds, Dave.
Thanks for the feedback Dave. I agree it should be made easier to do what we need to do. OPENQUERY/OPENROWSET using a loopback connection to be bale to use SELECT...INTO seems like a hack to me and is rife with implementation problems. I would not characterize what I am doing as a hack but readily concede it is a workaround for the limitations of Transact-SQL in this area.
I have similar gripes around INSERT...EXEC as it relates to not being able to nest them (Connect Item).
An EXEC...INTO construct that can be nested would solve both issues. Maybe I will submit a new take on the Connect items I have seen on these topics...will put it on the queue.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 23, 2016 at 1:57 pm
Unfortunately this doesn't solve the problem with nested INSERT INTO ... EXEC procedures since it uses same technique.
But it is helpful to read the structure of the dataset, returned by a stored proc.
March 23, 2016 at 2:09 pm
Thank you so much for the response! About the concatenate strings with Order by clause technique,
I am not aware of any cases where this might not be reliable. I went through the link (and the links in that link) but I am still not sure I understand 🙁
If you can think of any quick sample data where this might not work, that would be great!
March 23, 2016 at 2:44 pm
roman.asadovsky (3/23/2016)
Unfortunately this doesn't solve the problem with nested INSERT INTO ... EXEC procedures since it uses same technique.
Agreed. I did not really attempt to achieve that. To my knowledge the engine simply will not support nested INSERT...EXEC and there is no reasonable workaround.
But it is helpful to read the structure of the dataset, returned by a stored proc.
Thanks. That was my intent.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 23, 2016 at 3:23 pm
OceanDrop (3/23/2016)
Thank you so much for the response! About the concatenate strings with Order by clause technique,I am not aware of any cases where this might not be reliable. I went through the link (and the links in that link) but I am still not sure I understand 🙁
If you can think of any quick sample data where this might not work, that would be great!
Repro from the second link:
IF EXISTS( SELECT * FROM sysobjects WHERE name = 'T1' )
DROP TABLE T1
GO
CREATE TABLE T1( C1 NCHAR(1) )
INSERT T1 VALUES( 'A' )
INSERT T1 VALUES( 'B' )
DECLARE @Str0 VARCHAR(4)
DECLARE @Str1 VARCHAR(4)
DECLARE @Str2 VARCHAR(4)
SET @Str0 = ''
SET @Str1 = ''
SET @Str2 = ''
SELECT @Str0 = @Str0 + C1 FROM T1 ORDER BY C1
SELECT @Str1 = @Str1 + C1 FROM T1 ORDER BY LTRIM( RTRIM( C1 ) )
SELECT @Str2 = @Str2 + LTRIM( RTRIM( C1 ) ) FROM T1 ORDER BY C1
SELECT @Str0 'No functions applied to column.'
SELECT @Str1 'LTRIM() and RTRIM() applied to ORDER BY clause.'
SELECT @Str2 'SELECT list with LTRIM(RTRIM()) (Workaround)'
DROP TABLE T1
More info:
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 23, 2016 at 4:06 pm
Very interesting! Good to know! Thanks for the sample and I will definitely keep this in mind from moving forward!
Btw, the technique that I provided, do not use the concatenate column in order by right? Do you think it is still a problem?
select @column_list=@column_list+','+Columns
from Columnlist
ORDER BY column_ordinal
March 23, 2016 at 4:23 pm
OceanDrop (3/23/2016)
Very interesting! Good to know! Thanks for the sample and I will definitely keep this in mind from moving forward!Btw, the technique that I provided, do not use the concatenate column in order by right? Do you think it is still a problem?
select @column_list=@column_list+','+Columns
from Columnlist
ORDER BY column_ordinal
I have been told that referencing a column in the ORDER BY that is not referenced in the string concatenation can also cause problems where values are skipped. So, use the technique at your own risk. Here is the rub, parallelism may be partly to blame for the issue which is why it may not be easily reproducible. Who knows, a Service Pack or new Version upgrade may be the thing to introduce a change to the engine that modifies the behavior of more of these types of queries.
Like I said, when Erland pointed this out to me I was resistant since I had never seen string-concatenation done with a variable-assignment not work. However, after being told about the potential issue and that Microsoft has no official support for the technique I eventually settled into the notion it was not worth it to continue testing fate and therefore I decided to move on and embrace the XML technique.
Now, if you're really adventurous there is a set of SQLCLR aggregates that offer the same functionality as the XML technique with a much more expressive syntax. You can check them out at http://groupconcat.codeplex.com. Note that I wrote these SQLCLR objects. In my own code I leverage the SQLCLR objects but I put the XML technique in the article to keep it to a native solution as not to create an area to send people adrift talking about the risks of enabling SQLCLR which I think would have detracted from the intent of the article.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 15 posts - 16 through 30 (of 42 total)
You must be logged in to reply to this topic. Login to reply