The metadata could not be determined because statement invokes an extended stored procedure
Nothing (that I can see) changed between SQL Server 2012 and 2016 with respect to this issue. Not working with temp tables, multiple result sets, extended stored procedures, CLR objects, etc have all been limitations of sp_describe_first_result_set
since it was introduced in SQL Server 2012. So, I am thinking that you actually had this running on a version prior to SQL Server 2012 where it would have worked.
That being said, while you did find a work around via WITH RESULT SETS
, I would still highly recommend that you take a look at the definition of those two system stored procedures and copy the subsection of code that you are actually using into a stored procedure that does the operation directly. This way you won't need the multiple extra layers of abstraction that you are currently using (e.g. OPENROWSET
, etc).
Try the following to get the definition. Copy and paste the resulting XML into the text editor and replace <
and >
with their respective <
and >
characters:
USE [msdb];
SELECT OBJECT_DEFINITION(OBJECT_ID(N'msdb.dbo.sp_help_job')) FOR XML PATH('');