0

Is querying the backup metadata with sp_describe_first_result_set system sp supported?

For example the query:

SELECT * FROM sys.dm_exec_describe_first_result_set (N'RESTORE HEADERONLY FROM DISK =''C:\SQLBackups\SomeDBbackup.bak''',NULL,0)

returns NULL for each field.

error_message field:

The metadata could not be determined because statement RESTORE HEADERONLY .... does not support metadata discovery.

error_type_desc field:

UNSUPPORTED_STATEMENT

Is there a way to make this query work?

Thank you

1

The metadata could not be determined because statement RESTORE HEADERONLY .... does not support metadata discovery.

Error message tells you clearly that RESTORE HEADERONLY is unsupported statement, it does not support metadata discovery.

So there is NO WAY to make this work. It is not implemented.

UPDATE

There is a solution in the link you posted, but the question there was NOT how to make work sp_describe_first_result_set with smth that is not supported (that is YOUR question), but how to get back only some columns from RESTORE HEADERONLY resultset, i.e. how to get the result of RESTORE HEADERONLY in "queryable" form.

There is a solution like this:

SELECT BackupStartDate 
FROM OPENQUERY(LOCALSERVER, 
               'SET FMTONLY OFF;
               EXEC(''
               RESTORE HEADERONLY 
               FROM DISK = ''''C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQL2008\MSSQL\Backup\DB1.bak''''
'')')

But as you see the Server in question was 2008.

Starting with SQL Server 2012 sp_describe_first_result_set was introduced, and now if you try to execute the query above, another thing is executed (you can see it using Profiler):

exec [sys].sp_describe_first_result_set N'SET FMTONLY OFF;
               EXEC(''
               RESTORE HEADERONLY 
               FROM DISK = ''''C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQL2008\MSSQL\Backup\DB1.bak''''
'')',NULL,1

It's wrong to think that OPENQUERY/OPENROWSET always use sp_describe_first_result_set, you can prove it to yourself executing simple SELECT queries and monitoring execution using Profiler (even on 2012). But in your particular case the query is transformed as described above and you cannot do nothing with it.

  • Thank @sepupic but it seems like it should work according to this highly voted accepted answer here? Both OPENROWSET and OPENQUERY utilises sp_describe_first_result_set. My comment regarding the error is just below that answer. Thanks. – Stackoverflowuser Nov 15 '17 at 10:22
  • >>>Both OPENROWSET and OPENQUERY utilises sp_describe_first_result_set<<< Why do you think they use this procedure? It's documented somewhere or it's just what you think? – sepupic Nov 15 '17 at 10:30
  • sp_describe_first_result_set first appeared in SQL Server 2012. It just CANNOT BE USED in OPENROWSET/OPENQUERY that are present in earlier versions. – sepupic Nov 15 '17 at 10:43

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