Home Dashboard Directory Help
Search

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


Status: 

Closed
 as Won't Fix Help for as Won't Fix


9
0
Sign in
to vote
Type: Bug
ID: 737341
Opened: 4/17/2012 10:01:12 AM
Access Restriction: Public
3
Workaround(s)
view
5
User(s) can reproduce this bug

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
Details
Sign in to post a comment.
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
Sign in to post a workaround.
Posted by Sethu Srinivasan on 7/12/2012 at 1:59 PM
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 Chuck Lucking on 6/18/2012 at 8:18 AM
If you use OpenRowSet to call sp_Help_Job (With no parameters), you will get a error when executing on SQL 2012.
If you look at the procedure sp_Help_Job, you will see that with no parameters what actually is executed is sp_get_composite_job_info. If you change the syntax to call this proc instead, the call will work.

select * from OPENROWSET('SQLOLEDB', 'Server=MyServer;TRUSTED_CONNECTION=YES;',
                     'set fmtonly off exec msdb..sp_get_composite_job_info')

I have tested this change and it also works on SQL2000, 2005 and 2008

Cheers

Chuck Lucking
Posted by Krunalhd on 4/17/2012 at 10:03 AM
select * from
openrowset ('sqlncli','Server=(local);Trusted_Connection=yes',
'
EXEC msdb.dbo.sp_help_job
WITH RESULT SETS
(
(
    job_id uniqueidentifier,
    originating_server nvarchar(30),
    name sysname,
    enabled tinyint,
    description nvarchar(512),
    start_step_id int,
    category sysname,
    owner sysname,
    notify_level_eventlog int,
    notify_level_email int,
    notify_level_netsend int,
    notify_level_page int,
    notify_email_operator sysname,
    notify_netsend_operator sysname,
    notify_page_operator sysname,
    delete_level int,
    date_created datetime,
    date_modified datetime,
    version_number int,
    last_run_date int,
    last_run_time int,
    last_run_outcome int,
    next_run_date int,
    next_run_time int,
    next_run_schedule_id int,
    current_execution_status int,
    current_execution_step sysname,
    current_retry_attempt int,
    has_step int,
    has_schedule int,
    has_target int,
    type int
)
)
');