SQL agent job Error while migrating from 2008 R2 to SQL 2014 for openrowset query

Question

I need to insert metadata into temp table. It was working fine in 2008 R2. Below is my query.

Insert INTO #Temp (SELECT * FROM OPENROWSET('SQLNCLI','Server=.;Trusted_Connection=YES;', 'set fmtonly off; EXEC msdb.dbo.sp_help_job') )

Getting this error and unable to resolve.

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 invokes an extended stored procedure.

Answer 1

Try using WITH RESULTSETS as indicated by this connect item

https://connect.microsoft.com/SQLServer/feedback/details/737341/sql-server-2012-openrowset-on-msdb-dbo-sp-help-job-throws-error

• Puskar: Let me try and i will update