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.


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

Description

Below query on Sql Server 2012 
 
select * from
openrowset ('SQLOLEDB','Server=(local);Trusted_Connection=yes','SET FMTONLY OFF EXEC msdb.dbo.sp_help_job')
 
Throws
 
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 )
as

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
else
select another_col from #temp

go
Posted by Microsoft 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

Thanks
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
SET FMTONLY ON
exec msdb.dbo.sp_help_job
--2012 style metadata query
SET FMTONLY OFF
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 Microsoft 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.

Thanks,

Alex Grach