OpenQuery and using dynamic SQL

Question

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.

Did you tried to define the result sets? I mean, listing fields. — user_0
– user_0, Commented Jun 5, 2015 at 9:45
I cannot define a result set. There are lots of reports retrieving data from this stored procedure and as I said with different input parameters I would have different result sets. I need to leave result sets undefined but it won't work. — Leila
– Leila, Commented Jun 6, 2015 at 12:06

Randall · Accepted Answer · 2015-06-10 03:44:52Z

2

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!

answered Jun 10, 2015 at 3:44

Randall

1,54113 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Leila Over a year ago

Thanks Randall, you are right I do not like this approach really, but I guess I have to do this as there is nothing else so far. I wish we could spend some time to re-design it in a more appropriate way.

Gerard Jaryczewski Over a year ago

Unfortunately, in 2022 this link is not available, we have lost this knowledge. I have found some tips here (and this is still available): sqlservercentral.com/forums/topic/…

Collectives™ on Stack Overflow

OpenQuery and using dynamic SQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related