SQL Server 2012 - Openrowset on msdb.dbo.sp_help_job throws error - by Krunalhd

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 737341 Comments
Status Closed Workarounds
Type Bug Repros 8
Opened 4/17/2012 10:01:12 AM
Access Restriction Public


Below query on Sql Server 2012 
select * from
openrowset ('SQLOLEDB','Server=(local);Trusted_Connection=yes','SET FMTONLY OFF EXEC msdb.dbo.sp_help_job')
Msg 11520, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1
The metadata could not be determined because statement 'EXECUTE master.dbo.xp_sqlagent_is_starting @retval OUTPUT' in procedure 'sp_is_sqlagent_starting' invokes an extended stored procedure.

It works for SQL Server 2008 R2
Sign in to post a comment.
Posted by AjayGarg on 2/10/2015 at 7:35 PM
I had the same issue. Homegrown replication monitoring - works fine on SQL Server 2008, fails on SQL Server 2012. Looking at the workarounds, I have been able to fix my code using the WITH RESULT SETS clause.
Posted by mbourgon on 8/8/2013 at 8:19 AM
This affects other MS SPs as well, namely replication. I have a homegrown replication monitor that looks for several failure conditions, and it doesn't work on 2012. Running exec distribution..sp_replmonitorhelpsubscription via OPENROWSET throws the following error message:

Msg 11520, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1
The metadata could not be determined because statement 'exec @result = sys.xp_userlock 0, @dbid, @DbPrincipal, @Resource, @mode, @owner, @LockTimeout' in procedure 'sp_getapplock' invokes an extended stored procedure.
Posted by Karl Grambow on 8/28/2012 at 7:51 AM
I find it hard to believe that this won't be fixed.

The workaround works fine for any stored procedure that returns a single resultset but a stored procedure that can return one of two (or more) different result sets will fail using the provided work around.

I have a customer that has upgraded to SQL 2012 and several procedures that use openrowset to user stored procedures are failing.

Is there a known workaround when the stored procedure looks like this?

create procedure my_test
( @some_bit bit )

select cast(1 as Int) as my_col, cast('column value' as varchar(13)) as column_2, 2.008934 as another_col
into #temp

if @some_bit = 1
select my_col, column_2 from #temp
select another_col from #temp

Posted by Sethu [MSFT] on 7/12/2012 at 1:55 PM
I have posted a workaround in our team blog http://blogs.msdn.com/b/sqlagent/archive/2012/07/12/workaround-sql-server-2012-openrowset-on-msdb-dbo-sp-help-job-throws-error.aspx

Sethu Srinivasan [MSFT]
SQL Server
Posted by JJ78 on 6/10/2012 at 9:02 AM
come accross this same error while migrating an DB from 2008 to 2012, this is blocking currently our plans to migrate to sql2012 :( Any updates how this can be solved without changing TSQL-Code (by adding WITH RESULT SETS..) ?

seems error has something to do with changed metadatadiscovery?! Check --> http://blog.tallan.com/2012/05/29/new-metadata-discovery-features-in-sql-server-2012/

Metadata query on a SQL2012 system doesn't work (at least for the SP_HELP_JOB proc) with any of bellow queries:
--old style metadata query
exec msdb.dbo.sp_help_job
--2012 style metadata query
EXEC sp_describe_first_result_set @tsql = N'exec msdb.dbo.sp_help_job'
Posted by fsugeiger on 6/9/2012 at 5:06 AM
Seeing the same issue on 2012, Sample call, returns the same error message:

SELECT current_execution_status FROM OPENROWSET('SQLNCLI', 'Server=shipreportssql;Trusted_Connection=yes;',
                         'EXEC MSDB.dbo.sp_help_job @job_name = ''JobName'', @job_aspect = ''JOB'' ')
Posted by Alex [MSFT] on 4/18/2012 at 10:10 AM
Thank you for reporting this issue - we are investigating and we will get back to you shortly.


Alex Grach