0

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.

Puskar
  • 181
  • 1
  • 3
  • 15

1 Answers1

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

Scott Hodgin - Retired
  • 23,854
  • 2
  • 26
  • 48