March 13, 2013 at 8:58 am
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
March 13, 2013 at 9:17 pm
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
March 14, 2013 at 8:06 am
happycat59 (3/13/2013)
Coincidentally, I had a similar problem just this morning with a sproc that calls msdb.dbo.sp_help_jobHave 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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 18, 2013 at 11:50 am
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.
March 18, 2013 at 6:46 pm
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?
_____________
Code for TallyGenerator
March 19, 2013 at 6:08 am
Good answer Sergiy.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 19, 2013 at 8:36 am
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
September 26, 2013 at 6:58 am
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 😛 )
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.
September 26, 2013 at 7:19 am
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 😛 )
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.
September 26, 2013 at 7:39 am
At this point there do not seem to be any negative implications with switching providers.
How about this?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 28, 2013 at 9:45 am
Hi.
I had the same problem and I used the Native OLE DB\Microsoft OLE DB Provider for SQL Server as you spoke and now is working. I put also the statement SET FMTONLY OFF on the first line, before procedure call.
Thank you.
January 8, 2016 at 5:54 am
I have an MDX query, which I want to access from RUBY ON RAILS... so that By creating LINKED-SERVER objected I can access MDX query and can get all results (rows/columns).
SP1: Actually I have created StoredProcedure based on OPENQUERY and accessing SSAS-Cube results via LinkedServer.
Then I created another StoredProcedure based on OPENROWSET calling SP1 and can access SSAS-Cube results without passing any parameter.
But when I pass some parameter its creating error and giving me error
'Msg 11514, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1
The metadata could not be determined because statement 'EXEC (@Query)' in procedure 'SP1' contains dynamic SQL. Consider using the WITH RESULT SETS clause to explicitly describe the result set.'
If I try to access SP1 direct then it is giving error.
"TinyTds::Error at /SP1 Cannot initialize the data source object of OLE DB provider "MSOLAP" for linked server "LinkedServerName"
Kindly reply. Regards
January 8, 2016 at 6:04 am
asghar786 (1/8/2016)
I have an MDX query, which I want to access from RUBY ON RAILS... so that By creating LINKED-SERVER objected I can access MDX query and can get all results (rows/columns).SP1: Actually I have created StoredProcedure based on OPENQUERY and accessing SSAS-Cube results via LinkedServer.
Then I created another StoredProcedure based on OPENROWSET calling SP1 and can access SSAS-Cube results without passing any parameter.
But when I pass some parameter its creating error and giving me error
'Msg 11514, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1
The metadata could not be determined because statement 'EXEC (@Query)' in procedure 'SP1' contains dynamic SQL. Consider using the WITH RESULT SETS clause to explicitly describe the result set.'
If I try to access SP1 direct then it is giving error.
"TinyTds::Error at /SP1 Cannot initialize the data source object of OLE DB provider "MSOLAP" for linked server "LinkedServerName"
Kindly reply. Regards
Reply to what? We have to guess your question.
As this thread has already mentioned WITH RESULT SETS, I am assuming that you've tried that already. Please describe what problem you had when you did.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 20, 2017 at 8:00 pm
Just Had the exact same issue Ugrading an old SSIS Package 2008 to SQL2016,
Changing SQL Native Client 11 OVER TO OLEDB Provider for SQL solved it for me.
Regards
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply