Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
dtibz01
dtibz01
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 264
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
happycat59
happycat59
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3563 Visits: 3101
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



Phil Parkin
Phil Parkin
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8320 Visits: 19468
happycat59 (3/13/2013)
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


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.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
dtibz01
dtibz01
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 264
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.
Sergiy
Sergiy
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5828 Visits: 11397
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?
Phil Parkin
Phil Parkin
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8320 Visits: 19468
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.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
dtibz01
dtibz01
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 264
Sergiy (3/18/2013)
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?

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 doubtSmile. 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
Zksod
Zksod
SSC Rookie
SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)

Group: General Forum Members
Points: 49 Visits: 377
Greetings all.

Apologies for awaking a sleeping dog...

There may be a faster way around your challenge than adding the result set definition to all the procs.

Though it is not without it's cons... While using the result set to define the meta data to bypass the column sniffing through SSIS is more efficient, it can be balanced in regards to getting something to a functional state as soon as possible.

Based upon your output, I am assuming that you are using SQL Native Client 11 as your connectivity provider? Try switching to an OLEDB generic driver for SQL Server in the connection managers options. It should allow you to execute dynamic code through ssis without having to declare the meta data. (which isn't necessarily a good thing Tongue )

Though please test it out and do some research to determine which is the best provider for you in this case in terms of performance.
dtibz01
dtibz01
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 264
Zksod (9/26/2013)
Greetings all.

Apologies for awaking a sleeping dog...

There may be a faster way around your challenge than adding the result set definition to all the procs.

Though it is not without it's cons... While using the result set to define the meta data to bypass the column sniffing through SSIS is more efficient, it can be balanced in regards to getting something to a functional state as soon as possible.

Based upon your output, I am assuming that you are using SQL Native Client 11 as your connectivity provider? Try switching to an OLEDB generic driver for SQL Server in the connection managers options. It should allow you to execute dynamic code through ssis without having to declare the meta data. (which isn't necessarily a good thing Tongue )

Though please test it out and do some research to determine which is the best provider for you in this case in terms of performance.


Zksod, thanks for the reply. Speaking for myself alone it's appreciated. Your assumption is correct in regard to the connection provider. Your suggestion to switch it is exactly where we landed. For good or bad. At this point there do not seem to be any negative implications with switching providers.
Phil Parkin
Phil Parkin
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8320 Visits: 19468
At this point there do not seem to be any negative implications with switching providers.


How about this?


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

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search