April 15, 2014 at 3:45 am
To get the results of a stored proc into a table you always had to know the structure of the output and create the table upfront.
CREATE TABLE #Tmp (
Key INT NOT NULL,
Data Varchar );
INSERT INTO #Tmp
EXEC dbo.MyProc
Has SQL 2012 (or SQL 2014) got any features / new tricks to let me discover the table structure of a stored procedure output, - i.e treat it as a table
EXEC dbo.MyProc
INTO #NewTmp
or
SELECT *
INTO #NewTmp
FROM ( EXEC dbo.MyProc )
April 15, 2014 at 6:02 am
Tom As far As I know, the trick to getting the column definitions is getting them into a temp table, so that piece is correct...
the real trick is to call the procedure using a loopback linked server, so you can use openquery.
EXEC master..sp_addlinkedserver
@server = 'loopback',
@srvproduct = '',
@provider = 'SQLNCLI',
@datasrc = @@SERVERNAME;
EXEC master..sp_serveroption
@server = 'loopback',
@optname = 'DATA ACCESS',
@optvalue = 'TRUE';
SELECT * INTO #Tmp FROM OPENQUERY(loopback, 'EXEC CurrentDB.dbo.MyProc;');
then you can select the data types form tempdb:
SELECT
colz.name,
type_name(colz.system_type_id),
colz.max_length, --doubled if this is an nvarchar/nchar ie 512 is nvarchar(256)
colz.precision, --needed if decimal/numeric/money, mostly optional if float/real
colz.scale
from tempdb.sys.columns colz
where object_id = object_id('tempdb.dbo.#Tmp')
order by colz.column_id
Lowell
April 15, 2014 at 7:04 am
Thanks
I must be getting old because I've used OPENROWSET before in other projects, and didn't remember at all when posting this question.
However on this particular project they don't like linked servers/openquery etc - So I don't have the Access level required - and almost certainly the DBA here would not allow it into production.
SELECT * INTO #Tmp
FROM OPENROWSET('SQLNCLI', 'server=INSTANCE;trusted_connection=yes', 'exec DB.dbo.MyProc')
'
Msg 15281, Level 16, State 1, Line 1
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', search for 'Ad Hoc Distributed Queries' in SQL Server Books Online.
But I was rather hoping Microsoft had built-in something new - without the need to shell out of T-SQL and reconnect.
Maybe I'll write a CLR and try to squeeze that past the DBA :w00t:
April 16, 2014 at 11:04 am
I vaguely recalled something about SQL Server 2012 adding some additional system stored procedures that return a resultset describing metadata that would be returned from a specified batch of T-SQL, which could be a stored procedure.
sp_describe_first_result_set
http://technet.microsoft.com/en-us/library/ff878602.aspx
For example:
exec sp_describe_first_result_set @tsql = N'sp_who';
However, it can be tripped up by dynamic sql. If you try it with sp_who2, it will return this:
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.
Also in 2012 the EXEC command has a new WITH RESULT SETS clause that can be used to alias column names returned from a stored procedure.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
April 20, 2014 at 5:14 pm
There where two table value functions introduced in 2012, sys.dm_exec_describe_first_result_set and sys.dm_exec_describe_first_result_set_for_object, the former takes a query string as a parameter, the latter an object id. The result sets are the same.
select * from sys.dm_exec_describe_first_result_set('exec sp_who',NULL,1);
select * from sys.dm_exec_describe_first_result_set_for_object(object_id('sys.sp_who'),1);
April 20, 2014 at 6:12 pm
Ok... I have to ask. What do either of those functions do for creating a table other than returning some meta-data that you could write some dynamic T-SQL to build a CREATE TABLE statement?
--Jeff Moden
Change is inevitable... Change for the better is not.
April 20, 2014 at 6:29 pm
Tom Brown (4/15/2014)
However on this particular project they don't like linked servers/openquery etc - So I don't have the Access level required - and almost certainly the DBA here would not allow it into production.
Shifting subjects a bit, you'd love me as a DBA. 🙂 I give the Devs "SA" privs on the Dev box (ONLY) and tell them to "Go for it, the sky's the limit. Just don't do any DBA stuff. If you need something like a linked server, come see me and we'll hammer it out together." I also tell them "You write it, I'll figure out a way to run it safely or a way to rewrite it so it is". I also do 100% code reviews that I use as "one-on-one mentoring" time and, I can assure you, the Devs aren't the only ones learning during such sessions. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
April 21, 2014 at 4:57 am
Jeff Moden (4/20/2014)
Ok... I have to ask. What do either of those functions do for creating a table other than returning some meta-data that you could write some dynamic T-SQL to build a CREATE TABLE statement?
From the top of my head:w00t:
As a coding and s documentation aide, fastest method for optaining the metadata and structure (my favorite).
For tracking / monitoring / auditing changes and asserting design compliance. Dump the results in a table...
Optaining the metadata translation for SSIS packages, which i.e. create staging tables based on the metadata. Far better than writing a translation routine in the package.
Adding a level of security to dynamic sql routines, similar to the INFORMATION_SCHEMA. An object or a column cannot be used in any way, shape or form unless it exists in the function's result set.
BTW: The functions use an undocumented source definition (TABLE) within OPENROWSET, haven't looked further into that part:cool:
April 21, 2014 at 7:37 am
Heh... Thanks. I guess I'd just have to curse any DBA that doesn't allow the use of OPENROWSET for such things. The end user of the proc don't need anything other than PUBLIC privs if it's done right.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 21, 2014 at 7:58 am
Jeff Moden (4/20/2014)
Tom Brown (4/15/2014)
However on this particular project they don't like linked servers/openquery etc - So I don't have the Access level required - and almost certainly the DBA here would not allow it into production.Shifting subjects a bit, you'd love me as a DBA. 🙂 I give the Devs "SA" privs on the Dev box (ONLY) and tell them to "Go for it, the sky's the limit. Just don't do any DBA stuff. If you need something like a linked server, come see me and we'll hammer it out together." I also tell them "You write it, I'll figure out a way to run it safely or a way to rewrite it so it is". I also do 100% code reviews that I use as "one-on-one mentoring" time and, I can assure you, the Devs aren't the only ones learning during such sessions. 😉
What is a DBA to do when the Dev or BI team insists that they need access to the "SA" account, and managment agrees with them?
Below is one solution. 😉
use MASTER
go
alter login sa DISABLE;
go
alter login sa with name = [sa_bak];
go
create login sa with PASSWORD = '<strong password>', DEFAULT_DATABASE = master;
go
use CorpDb;
go
drop user sa;
go
create USER sa for login sa;
go
exec sp_addrolemember 'db_datareader', 'sa';
-- etc. whatever other minimal persmissions they need.
go
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
April 21, 2014 at 8:53 am
Eric M Russell (4/21/2014)
Jeff Moden (4/20/2014)
Tom Brown (4/15/2014)
However on this particular project they don't like linked servers/openquery etc - So I don't have the Access level required - and almost certainly the DBA here would not allow it into production.Shifting subjects a bit, you'd love me as a DBA. 🙂 I give the Devs "SA" privs on the Dev box (ONLY) and tell them to "Go for it, the sky's the limit. Just don't do any DBA stuff. If you need something like a linked server, come see me and we'll hammer it out together." I also tell them "You write it, I'll figure out a way to run it safely or a way to rewrite it so it is". I also do 100% code reviews that I use as "one-on-one mentoring" time and, I can assure you, the Devs aren't the only ones learning during such sessions. 😉
What is a DBA to do when the Dev or BI team insists that they need access to the "SA" account, and managment agrees with them?
Below is one solution. 😉
use MASTERgo
alter login sa DISABLE;
go
alter login sa with name = [sa_bak];
go
create login sa with PASSWORD = '<strong password>', DEFAULT_DATABASE = master;
go
use CorpDb;
go
drop user sa;
go
create USER sa for login sa;
go
exec sp_addrolemember 'db_datareader', 'sa';
-- etc. whatever other minimal persmissions they need.
go
Oh you sneaky devil you! 😛 Nice!
Fortunately for me and for any of the jobs I've had, all I have to do is remind management that they won't pass a SOC 2, SOX, SEC, PCI, or any other type of audit if they grant users/apps "SA" privs on the servers that I'm responsible for (and that would be all of them) and they suddenly backoff and listen to what actually needs to be done. 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
April 21, 2014 at 9:17 am
Jeff Moden (4/21/2014)
Eric M Russell (4/21/2014)
Jeff Moden (4/20/2014)
Tom Brown (4/15/2014)
However on this particular project they don't like linked servers/openquery etc - So I don't have the Access level required - and almost certainly the DBA here would not allow it into production.Shifting subjects a bit, you'd love me as a DBA. 🙂 I give the Devs "SA" privs on the Dev box (ONLY) and tell them to "Go for it, the sky's the limit. Just don't do any DBA stuff. If you need something like a linked server, come see me and we'll hammer it out together." I also tell them "You write it, I'll figure out a way to run it safely or a way to rewrite it so it is". I also do 100% code reviews that I use as "one-on-one mentoring" time and, I can assure you, the Devs aren't the only ones learning during such sessions. 😉
What is a DBA to do when the Dev or BI team insists that they need access to the "SA" account, and managment agrees with them?
Below is one solution. 😉
use MASTERgo
alter login sa DISABLE;
go
alter login sa with name = [sa_bak];
go
create login sa with PASSWORD = '<strong password>', DEFAULT_DATABASE = master;
go
use CorpDb;
go
drop user sa;
go
create USER sa for login sa;
go
exec sp_addrolemember 'db_datareader', 'sa';
-- etc. whatever other minimal persmissions they need.
go
Oh you sneaky devil you! 😛 Nice!
Fortunately for me and for any of the jobs I've had, all I have to do is remind management that they won't pass a SOC 2, SOX, SEC, PCI, or any other type of audit if they grant users/apps "SA" privs on the servers that I'm responsible for (and that would be all of them) and they suddenly backoff and listen to what actually needs to be done. 😛
Yes, fortunately I havn't had to do this in my current job thanks to SOX and HIPAA, and also I'm on the Dev/Ops side of things now. However, I have reccomended it to a lot DBAs here and at user group meetings. Often times the question isn't "Should I grant developers access to 'SA' account?" but rather "How do I deal with developers who have access to 'SA' account?". It's especially useful in small organizations where the DBA might not have any political clout.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
April 21, 2014 at 11:50 am
Well our current DBA has been referred to (and I quote) as "slash dev slash null" - (which seems to have multiple meanings ) - but the one in point is he doesn't listen to or even acknowledge any requests for anything outside your current permission set. [developer request] > /dev/null
I think the company got burned over a security breach a few years back, and now everything is locked down tight as a kangaroo's Khyber.
Meanwhile, back to the original question, this seems to serve my purposes for now - thanks to those who knew about the new dm
DECLARE @SPNAME sysname;
SET @SPNAME = 'usp_GetUsers';
DECLARE @TMPTABLE sysname;
SET @TMPTABLE = N'#' + @SPNAME;
DECLARE @DELIM NVARCHAR(4) = N', ' + NCHAR(10);
DECLARE @SQL NVARCHAR(MAX);
SET @SQL = N'CREATE TABLE ' + @TMPTABLE + N' ( ';
WITH rowDefs AS (
select TOP(100) PERCENT name + ' ' + system_type_name AS RowDef, column_ordinal
FROM sys.dm_exec_describe_first_result_set(N'exec ' + @SPNAME,NULL,1)
WHERE is_hidden = 0
ORDER BY column_ordinal
)
SELECT DISTINCT
@SQL = @SQL + stuff ( ( SELECT @DELIM + RowDef
FROM rowDefs t1
FOR XML PATH ( '' ) ) , 1 , 1 , '' )
FROM rowDefs t2
SELECT @SQL = @SQL + N' );'
PRINT @SQL;
However, today being a bank holiday, I haven't had change to test this at work yet - surely they wouldn't have locked out permissions to the sys.dm tools - actually they may have, if any of those sys.dm procedures allow you to do anything remotely suspicious from a security point of view, I can see a blanket ban being imposed on all of them.
April 21, 2014 at 12:27 pm
Given the cirsunstances, it's probably best to just hard code the definition of the temp table, which is the typical way of doing it. I wouldn't even dick around with doing this dynamically in the first place.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
March 2, 2015 at 2:58 am
Hi,
I started crashing my head on this topic, so I ask some further advice to the people that, seemingly, tried to solve the same problem.
In this situation I'm a report designer that writes the query used in the report relying on a stored procedure (that uses temporary tables inside it) written by someone else.
I must take the result of the stored procedure, put it into (another) temporary table, JOIN it with other tables ...
I this "someone else" adds some columns to the output, the report query breaks :angry:.
Until now I was unable to find any solution to this problem.
Did anybody of you ?
Thanks
Teodoro Marinucci
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply