April 25, 2012 at 12:26 am
I am getting XML data by executing a stored procedure in Execute SQL Task
And storing the XML data in String variable for using it in Dataflow. This works fine.
But when the stored procedure is unavailable, its not throwing any error , but executes the dataflow with 0 rows.
How to capture this Error?
Should i change the result set? or the way executing the SP?
DECLARE @procname VARCHAR(100)
SET @procname=?
EXEC @procname
Execute SQL Task
Result Set : XML
April 25, 2012 at 1:39 am
What do you mean with "the stored procedure is unavailable"?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 25, 2012 at 1:41 am
E.g: Access Denied on Stored procedure
April 25, 2012 at 1:42 am
The Execute SQL Task doesn't fail on an Access Denied error? Strange...
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 25, 2012 at 3:09 am
Yes.. I have given XML resultset and storing in one variable.
When Access Denied on exec sp, the Variable has value :
<ROOT><?MSSQLError HResult=\"0x80040e09\" Source=\"Microsoft SQL Server Native Client 10.0\" Description=\"The EXECUTE permission was denied on the object ''DevQPF, database 'Master', schema 'dbo'.\"?><?MSSQLError HResult=\"0x80040e09\" Source=\"Microsoft SQL Server Native Client 10.0\" Description=\"The EXECUTE permission was denied on the object DecQPF, database 'Master', schema 'dbo'.\"?></ROOT>\r
??!!
April 25, 2012 at 3:17 am
It seems the error message is stored inside the XML file.
Any reason you don't store the results in a full resultset (object variable) instead of XML?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 25, 2012 at 3:19 am
I need it to use as input variable in XML Source in Dataflow later 🙁
April 25, 2012 at 3:21 am
Even when i use FullResultSet the error is not captured.
May be I need to use "Single Row" result set.
But dont know how to get the Stored procedure result in Single Row ResultSet.
The SP doesnt have any Output parameters set!!
April 25, 2012 at 3:21 am
Gerbera (4/25/2012)
I need it to use as input variable in XML Source in Dataflow later 🙁
Yes. The question is: why?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 25, 2012 at 3:24 am
So how should I get the SP result in single row result set
Thanks
April 25, 2012 at 3:47 am
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 25, 2012 at 3:54 am
Koen,
I am using Execut SQL Task only, but when using Single row result set it throws error.
could you pl. help me on how to get the SP result stored in variable if there is no o/p oaram in SP?
April 25, 2012 at 3:57 am
What do you mean with "there is no o/p oaram in SP"?
Also, post the error message.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 25, 2012 at 4:15 am
[Execute SQL Task] Error: An error occurred while assigning a value to variable "VAR_Data": "The type of the value being assigned to variable "User::Data" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
VAR_Data - String variable
April 25, 2012 at 4:58 am
And what is the result set of your stored procedure?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply