SQL 2012 ERROR - The metadata could not be determined because statement contains dynamic SQL

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2012 » SQL Server 2012 - T-SQL » SQL 2012 ERROR - The metadata could not be...

SQL 2012 ERROR - The metadata could not be determined because statement contains dynamic SQL

Rate Topic

Display Mode

Topic Options

Author

Message

dtibz01

Posted Wednesday, March 13, 2013 8:58 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, May 20, 2013 11:41 AM
Points: 5, Visits: 134

Hi,

This post is related to an error I have discovered through the execution of a SSIS package that has been migrated and upgraded in a SQL Server 2012 instance. The package has failed in initial testing in 2012 and the root error/issue seems to be this message:

sg 11514, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1
The metadata could not be determined because statement 'EXEC sp_executesql @Sql, @ParamDetails,
@IdParam = @Id,
@Sta'
in procedure 'PROCNAMECHANGEDTOPROTECTTHEGUILTY' contains dynamic SQL. Consider using the WITH RESULT SETS clause to explicitly describe the result set.

I am not the author of the package, so I don't know the full details of what it does but in the end it calls a proc that generates and executes dynamic SQL. And it runs fine in our 2008R2 instances.

I have been able to track a similar message related to an inability to determine metadata when referencing a temp table, but I have not come across a message in my searches that specifically mention metadata not being determined due to a proc executing dynamic SQL as noted.

Not really expecting a fix (although that would be sweet!) from you folks, I'm hopeful that some discussion might be sparked around functional changes related to gathering metadata in 2012 that are contributing to the problem because I frankly don't get what's happening and I would like to gather as much information as possible to troubleshoot further.

So has anyone seen this message in the course of migrating to 2012? Any insight, online references, or experiences are much appreciated.

D

Post #1430451

happycat59

Posted Wednesday, March 13, 2013 9:17 PM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 9:55 PM
Points: 2,471, Visits: 2,066

Coincidentally, I had a similar problem just this morning with a sproc that calls msdb.dbo.sp_help_job

Have a look at http://connect.microsoft.com/SQLServer/feedback/details/737341/sql-server-2012-openrowset-on-msdb-dbo-sp-help-job-throws-error

In particular, the syntax used in the first workaround. The part of the query of interest to you, I think, is the syntax of the WITH RESULTS SETS where the definition of the columns that are returned by the sproc are explicitly listed.

Hope this help you

Post #1430720

Phil Parkin

Posted Thursday, March 14, 2013 8:06 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 9:20 AM
Points: 4,242, Visits: 9,494

happycat59 (3/13/2013)

Yep - WITH RESULT SETS is the way to fix this. It's not really a workaround, in my opinion, rather an improvement to the way in which stored procs and SSIS interact which means that SSIS does not have to go through the proc column sniffing.

____________________________________________________________________________________________

Help us to help you. For better, quicker and more focused answers to your questions, consider following the advice in this link:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.

Post #1430987

dtibz01

Posted Monday, March 18, 2013 11:50 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, May 20, 2013 11:41 AM
Points: 5, Visits: 134

Thanks for the feedback. Using WITH RESULT SETS seems to have gotten me past error. I'm not super excited about the prospect of combing through current stored procedures, determining which are touched by SSIS, and altering them accordingly. Actually, it won't be me most likely so I guess it's not that bad.

Just for reference with respect to the topic, I had mentioned that a proc called from SSIS that includes the execution of dynamic sql was the issue in this case. I had never seen or used "WITH RESULT SETS" syntax and was a little unclear as to how to implement it in my case. Here's a before and after example based loosely on the original block of problem code, and what I changed it to:

Blows up:

EXEC sp_executesql @Sql, @Parms,
@IdParam = @Id,
@IdParam2 = @TransactionId,
@OriginalDataValueOut = @FieldDataValue OUTPUT,
@ResultOut = @ResultFrom OUTPUT;

Doesn't blow up:

EXEC sp_executesql @Sql, @Parms,
@IdParam = @Id,
@IdParam2 = @TransactionId,
@DataValue = @FieldDataValue OUTPUT,
@Result = @ResultFrom OUTPUT

WITH RESULT SETS
((
[Sql] nvarchar(1000),
Parms nvarchar(500),
IdParam uniqueidentifier,
IdParam2 uniqueidentifier,
DataValue BIT,
Result BIT
))
;

The part that I find to be a little frustrating, is that the data types for the parameters defined for WITH RESULT SETS are defined elsewhere in the SP to begin with..... But I guess I'm just complaining. And probably still don't fully understand what the newer metadata gathering functionality actually buys (big picture). As it relates to me on this day though, it sucks.

Thanks again for your help, I really appreciate you taking time to respond.

Post #1432288

Sergiy

Posted Monday, March 18, 2013 6:46 PM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 5:47 AM
Points: 4,550, Visits: 8,201

dtibz01 (3/18/2013)The part that I find to be a little frustrating, is that the data types for the parameters defined for WITH RESULT SETS are defined elsewhere in the SP to begin with..... But I guess I'm just complaining. And probably still don't fully understand what the newer metadata gathering functionality actually buys (big picture). As it relates to me on this day though, it sucks.

Let's take procedure sp_help.

It returns different recordsets depending on if parameter is not supplied, if the name suapplied as parameter is a DB object or if it's a DB type.

Very different resultsets.

Now, how SSIS package suppose to know which of the data sets will be returned on any particular run?

I know, you do not write procedures like that.
But can they over there in Redmond really rely on it while designing a product?

Post #1432428

Phil Parkin

Posted Tuesday, March 19, 2013 6:08 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 9:20 AM
Points: 4,242, Visits: 9,494

Good answer Sergiy.

____________________________________________________________________________________________

Help us to help you. For better, quicker and more focused answers to your questions, consider following the advice in this link:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.

Post #1432590

dtibz01

Posted Tuesday, March 19, 2013 8:36 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, May 20, 2013 11:41 AM
Points: 5, Visits: 134

Sergiy (3/18/2013)

Fair enough Sergiy, and thank you for the reply. The comparison to sp_help seems like one of apples to oranges in this case, or at least Granny Smith to Red Delicious. But I get your point in general about potentially different or varying data sets in the context of SSIS. I don't deal with SSIS much, outside of the infrastructure to support its use so this really my first deeper dive into the plumbing.

Now, how SSIS package suppose to know which of the data sets will be returned on any particular run?

In response to this, the only answer I have off the cuff is I'm not sure. How is it handled on 2008R2? Is it simply not, or overlooked on purpose somehow in that context? Because in that version it doesn't seem to matter, this is however just an assumption based on the fact that the same package in 2008R2 doesn't fail and not much else.

A previous comment from Phil about SSIS column sniffing a given proc, and his general feeling that forcing the definition of result sets as a departure from "sniffing" is an improvement (which makes a whole mess of sense) gives me the impression that maybe previous versions are somehow lacking in this area. Or maybe at least operating with fewer rules. This new metadata method does seem to set one up to enforce a greater level of precision in regard to data in general, as well as an added level of flexibility to play reindeer games with result sets (at least by name) if desired. Assuming I have an accurate general understanding of WITH RESULT SETS....

I know, you do not write procedures like that.
But can they over there in Redmond really rely on it while designing a product?

I appreciate you giving me the benefit of doubt:). But "they over there in Redmond" absolutely cannot rely on this from me. At least they couldn't until last week. Selfishly I would rather not experience busted procs through improvement, but it is what it is. Again, I thank you for your time and willingness to reply.

D

Post #1432688

« Prev Topic | Next Topic »

Permissions