Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SQL 2012 ERROR - The metadata could not be determined because statement contains dynamic SQL Expand / Collapse
Author
Message
Posted Wednesday, March 13, 2013 8:58 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 8:04 AM
Points: 12, Visits: 194
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
Posted Wednesday, March 13, 2013 9:17 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 1:00 AM
Points: 2,993, Visits: 2,590
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
Posted Thursday, March 14, 2013 8:06 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:19 AM
Points: 5,078, Visits: 11,857
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1430987
Posted Monday, March 18, 2013 11:50 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 8:04 AM
Points: 12, Visits: 194
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
Posted Monday, March 18, 2013 6:46 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, September 18, 2014 11:10 PM
Points: 4,573, Visits: 8,351
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
Posted Tuesday, March 19, 2013 6:08 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:19 AM
Points: 5,078, Visits: 11,857
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1432590
Posted Tuesday, March 19, 2013 8:36 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 8:04 AM
Points: 12, Visits: 194
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 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
Posted Thursday, September 26, 2013 6:58 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 8:41 AM
Points: 39, Visits: 275
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 :P )

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.
Post #1498806
Posted Thursday, September 26, 2013 7:19 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 8:04 AM
Points: 12, Visits: 194
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 :P )

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.
Post #1498826
Posted Thursday, September 26, 2013 7:39 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:19 AM
Points: 5,078, Visits: 11,857
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1498843
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse