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

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

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
)
)
');

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