0

I've a T-SQL stored proc running in a Microsoft SQL Server 2019 environment. The proc contains temp tables, dynamic SQL and is 2000+ lines. The proc's output has been verified and tested for completeness and accuracy; re-writing the proc is not an option.

Attempts using sp_describe_first_result_set and OPENROWSET to retrieve metadata have failed due to the use of temp tables and dynamic SQL.

Research links used: The metadata could not be determined because statement invokes an extended stored procedure https://stackoverflow.com/questions/58553455/sql-server-the-metadata-could-not-be-determined-because-statement-execute-mas

Are there any other approaches to retrieve metadata?

Thoughts?

1
  • Two questions (please edit your question to add details): do you only need metadata about the final output or other steps in the procedure? Do you need this once or on a regular basis? Mar 26 at 14:43

3 Answers 3

2

Just one question: why do you need it on the server???

For the client (ODBC/ADO) it is very easy and reliable to read the metadata of the resultset once that resultset is ready for fetching. There is no need to a pre-analyze stored procedure. Just execute it, and before the very first fetch or right after jumping to the next resultset all meta data for result set is available and you can easily prepare buffers for fetching.

I am really confused on what task you are trying to do with sp_describe_first_result_set? I always thought this procedure is redundant and useless.

4
  • You asked "Why do you need it on the server?"
    – TPV
    Jun 15 at 12:51
  • You asked "Why do you need it on the server?" This app is a boutique financial application subject to multiple governmental regulations. Every new release requires a report reconciliation between the previous and current release to ensure no unintended changes have been made. Executing 2 versions of several hundred reports from client device is chatty. Executing on a server eliminates overhead.
    – TPV
    Jun 15 at 12:57
  • @TPV My question should have been read as: "Why do you need it inside the database server???" The solution to your problem is a client (script or compiled) which is run outside of the DB server (on the same machine as DB Server or on another - does not matter). Just extract the resultset out of DB, put it into storage (even back to DB, to a special table) and analyze the result as much as you like. If you are afraid that executing SP would take longer than running sp_desribe_first_result_set - probably, but the execute-extract approach is actually reliable unlike that SP.
    – White Owl
    Jun 15 at 19:21
  • Thanks and that's where I landed was a client app. Had to ask the community for input as there are a lot of bright people out there. Thanks for taking the time to engage!
    – TPV
    Jun 15 at 21:57
0

Did you try SET FMTONLY ON (link)?

It basically disables procedural code and returns meta-data based on that.

Below example shows how each execution of the proc returns the same meta-data: two result-sets, as if the IF statement didn't exist:

CREATE OR ALTER PROC p 
@i tinyint
AS
DECLARE @SQL varchar(8000)
IF @i = 1
BEGIN
    SET @SQL = 'SELECT name, object_id FROM sys.tables'
    EXEC(@sql)
END
ELSE
BEGIN
    SET @SQL = 'SELECT name FROM sys.tables'
    EXEC(@sql)
END
GO

SET FMTONLY ON
EXEC p @i = 1
EXEC p @i = 2
EXEC p @i = 3
SET FMTONLY OFF
0

I too have a similar issue; I need to be able to interrogate the output of a query/procedure that I don't own (customer provided); that output is then aggregated and charted. Ultimately, I need to take the output of someone else's procedure, drop it in my own table, and then manipulate the data from there. This is actually easy to do, unless, the procedure uses temp tables.

Tibor, if I take your answer, tweak it to match the #TempTable is a special case issue, then the problem is visible. Neither FMTONLY nor sp_describe_first_result_set will survive if the procedure includes a temp table.

ex:

CREATE OR ALTER PROC p 
@i tinyint
AS
SELECT object_id, name
    into #TempTable 
    FROM sys.objects 
    where type = 'S';

IF @i = 1
BEGIN
    SELECT top 10 * from #TempTable
END
ELSE
BEGIN
    SELECT * from #TempTable 
END
GO

--lets examine the meta data... 
exec sp_describe_first_result_set N'exec p @i = 1',null,0

SET FMTONLY ON
EXEC p @i = 1
SET FMTONLY ON

Now, If I take that example and expand it to the "take the output of your procedure and dump that into my table", you can see where the temp table issue takes things sideways:

declare @Cmd varchar(1000),
        @ExecCmd varchar(100);
if object_id(N'..JustForFun') is not null
drop table JustForFun;

set @ExecCmd = 'EXEC master.dbo.sp_who';

set @Cmd = 'SELECT * INTO JustForFun FROM OPENROWSET(''SQLNCLI'',''SERVER=' + @@SERVERNAME + ';Trusted_Connection=yes;'','
          + '''' + @ExecCmd + ''')'
exec(@Cmd);

select * from JustForFun
go

--Now lets try that with the procedure with a temp table... 
declare @Cmd varchar(1000),
        @ExecCmd varchar(100);
if object_id(N'..JustForFun') is not null
drop table JustForFun;

set @ExecCmd = 'Exec ' + db_name() + '.dbo.p @i = 1'
set @Cmd = 'SELECT * INTO JustForFun FROM OPENROWSET(''SQLNCLI'',''SERVER=' + @@SERVERNAME + ';Trusted_Connection=yes;'','
          + '''' + @ExecCmd + ''')'
exec(@Cmd);

The sp_who call works as expected. The call to procedure p with a temp table: Meta data cannot be determined

The only workaround I've found is as White Owl suggested; exec the procedure in code, grab the schema def from the ADO class objects, explicitly create a temp table, then insert the results of the procedure (executed again) into that table with an insert into tabname exec(@Cmd);

In short, there appears to be no way to redirect the output of a procedure,if that procedure uses a temp table, into a table like you can with conventional INSERT INTO ... syntax. You also cannot interrogate the meta data with any of the mentioned schema tools.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.