We are trying to migrate to SQL Server 2014. There are several cases where we need to use OpenQuery for returning dynamic results. Result Sets will be different according to different input parameters. It was working in sql 2008R2 and I need to keep it working But there is the following error which I can not resolve.
The metadata could not be determined because statement 'EXEC (@sQry)' in procedure 'spTest' contains dynamic SQL. Consider using the WITH RESULT SETS clause to explicitly describe the result set.
I have tried With Result sets undefined but there is still the same error.
SELECT * INTO tblTest
FROM OPENQUERY(LinkedServer, 'SET FMTONLY OFF EXEC spTest ''27'', null, null, null, ''%, Employed'' WITH RESULT SETS UNDEFINED')
Is there any alternatives or work around this.
Thank you in advance.
There is a work around, but you're not going to like it. You need to create a wrapper procedure, that will define the meta data and execute the orginal procedure based on what columns are supplied to the wrapper. Here is a Link to a MSDN blog where they discuss doing this for sp_help_job;
http://blogs.msdn.com/b/sqlagent/archive/2012/07/12/workaround-sql-server-2012-openrowset-on-msdb-dbo-sp-help-job-throws-error.aspx
It worked in 2008 because SSIS and it's ilk would look in the procedure to find the data types, 2012 doesn't play that way, if there is any ambiguity whatsoever it wants the metadata. The wrapper will help you limp along but I'd suggest bringing the procedure more into line with modern coding standards. Anyway hope that helps!