Hi,
suppose I've got 3 stored procedures: P1, P2, P3. I would like to run them
in some order and roll back the whole series of actions if an error occurs
in any of them. (Basically I would like to achieve the same effect as things
happen in an Oracle environment.) So:
BEGIN TRANSACTION tran_1
EXEC P1;
EXEC P2;
EXEC P3;
IF [there was an error somewhere] -- how?
ROLLBACK TRANSACTION tran_1
ELSE
COMMIT TRANSACTION tran_1
So, what is the proper way to produce this behavior?Agostan,
I would handle this situation by testing the error status of each of the
procedures using a return code. The following code fragment would sit after
each SQL statement in the procedure:
IF @.@.ERROR <> 0
BEGIN
RETURN 1
END
You would also need to include a return code at the end of the procedure for
successful completion:
RETURN 0
Then your procedure calls would need to collect this return code into a
variable and and test the result:
EXEC @.RC1 = P1
EXEC @.RC2 = P2
EXEC @.RC3 = P3
The convention I use is to have a positive non-zero return code for failure
and a zero return code for success, so to test for success or failure in your
example I would use:
IF @.RC1 + @.RC2 + @.RC3 > 0
BEGIN...etc
Hope this helps,
"Agoston Bejo" wrote:
> Hi,
> suppose I've got 3 stored procedures: P1, P2, P3. I would like to run them
> in some order and roll back the whole series of actions if an error occurs
> in any of them. (Basically I would like to achieve the same effect as things
> happen in an Oracle environment.) So:
> BEGIN TRANSACTION tran_1
> EXEC P1;
> EXEC P2;
> EXEC P3;
> IF [there was an error somewhere] -- how?
> ROLLBACK TRANSACTION tran_1
> ELSE
> COMMIT TRANSACTION tran_1
> So, what is the proper way to produce this behavior?
>
>|||You has to check @.@.ERROR and grab also the return value from the sps.
Implementing Error Handling with Stored Procedures
http://www.sommarskog.se/error-handling-II.html
Error Handling in SQL Server â' a Background
http://www.sommarskog.se/error-handling-I.html
AMB
"Agoston Bejo" wrote:
> Hi,
> suppose I've got 3 stored procedures: P1, P2, P3. I would like to run them
> in some order and roll back the whole series of actions if an error occurs
> in any of them. (Basically I would like to achieve the same effect as things
> happen in an Oracle environment.) So:
> BEGIN TRANSACTION tran_1
> EXEC P1;
> EXEC P2;
> EXEC P3;
> IF [there was an error somewhere] -- how?
> ROLLBACK TRANSACTION tran_1
> ELSE
> COMMIT TRANSACTION tran_1
> So, what is the proper way to produce this behavior?
>
>
Showing posts with label actions. Show all posts
Showing posts with label actions. Show all posts
Monday, March 19, 2012
How to rollback properly
Hi,
suppose I've got 3 stored procedures: P1, P2, P3. I would like to run them
in some order and roll back the whole series of actions if an error occurs
in any of them. (Basically I would like to achieve the same effect as things
happen in an Oracle environment.) So:
BEGIN TRANSACTION tran_1
EXEC P1;
EXEC P2;
EXEC P3;
IF [there was an error somewhere] -- how?
ROLLBACK TRANSACTION tran_1
ELSE
COMMIT TRANSACTION tran_1
So, what is the proper way to produce this behavior?Agostan,
I would handle this situation by testing the error status of each of the
procedures using a return code. The following code fragment would sit after
each SQL statement in the procedure:
IF @.@.ERROR <> 0
BEGIN
RETURN 1
END
You would also need to include a return code at the end of the procedure for
successful completion:
RETURN 0
Then your procedure calls would need to collect this return code into a
variable and and test the result:
EXEC @.RC1 = P1
EXEC @.RC2 = P2
EXEC @.RC3 = P3
The convention I use is to have a positive non-zero return code for failure
and a zero return code for success, so to test for success or failure in you
r
example I would use:
IF @.RC1 + @.RC2 + @.RC3 > 0
BEGIN...etc
Hope this helps,
"Agoston Bejo" wrote:
> Hi,
> suppose I've got 3 stored procedures: P1, P2, P3. I would like to run them
> in some order and roll back the whole series of actions if an error occurs
> in any of them. (Basically I would like to achieve the same effect as thin
gs
> happen in an Oracle environment.) So:
> BEGIN TRANSACTION tran_1
> EXEC P1;
> EXEC P2;
> EXEC P3;
> IF [there was an error somewhere] -- how?
> ROLLBACK TRANSACTION tran_1
> ELSE
> COMMIT TRANSACTION tran_1
> So, what is the proper way to produce this behavior?
>
>|||You has to check @.@.ERROR and grab also the return value from the sps.
Implementing Error Handling with Stored Procedures
http://www.sommarskog.se/error-handling-II.html
Error Handling in SQL Server – a Background
http://www.sommarskog.se/error-handling-I.html
AMB
"Agoston Bejo" wrote:
> Hi,
> suppose I've got 3 stored procedures: P1, P2, P3. I would like to run them
> in some order and roll back the whole series of actions if an error occurs
> in any of them. (Basically I would like to achieve the same effect as thin
gs
> happen in an Oracle environment.) So:
> BEGIN TRANSACTION tran_1
> EXEC P1;
> EXEC P2;
> EXEC P3;
> IF [there was an error somewhere] -- how?
> ROLLBACK TRANSACTION tran_1
> ELSE
> COMMIT TRANSACTION tran_1
> So, what is the proper way to produce this behavior?
>
>
suppose I've got 3 stored procedures: P1, P2, P3. I would like to run them
in some order and roll back the whole series of actions if an error occurs
in any of them. (Basically I would like to achieve the same effect as things
happen in an Oracle environment.) So:
BEGIN TRANSACTION tran_1
EXEC P1;
EXEC P2;
EXEC P3;
IF [there was an error somewhere] -- how?
ROLLBACK TRANSACTION tran_1
ELSE
COMMIT TRANSACTION tran_1
So, what is the proper way to produce this behavior?Agostan,
I would handle this situation by testing the error status of each of the
procedures using a return code. The following code fragment would sit after
each SQL statement in the procedure:
IF @.@.ERROR <> 0
BEGIN
RETURN 1
END
You would also need to include a return code at the end of the procedure for
successful completion:
RETURN 0
Then your procedure calls would need to collect this return code into a
variable and and test the result:
EXEC @.RC1 = P1
EXEC @.RC2 = P2
EXEC @.RC3 = P3
The convention I use is to have a positive non-zero return code for failure
and a zero return code for success, so to test for success or failure in you
r
example I would use:
IF @.RC1 + @.RC2 + @.RC3 > 0
BEGIN...etc
Hope this helps,
"Agoston Bejo" wrote:
> Hi,
> suppose I've got 3 stored procedures: P1, P2, P3. I would like to run them
> in some order and roll back the whole series of actions if an error occurs
> in any of them. (Basically I would like to achieve the same effect as thin
gs
> happen in an Oracle environment.) So:
> BEGIN TRANSACTION tran_1
> EXEC P1;
> EXEC P2;
> EXEC P3;
> IF [there was an error somewhere] -- how?
> ROLLBACK TRANSACTION tran_1
> ELSE
> COMMIT TRANSACTION tran_1
> So, what is the proper way to produce this behavior?
>
>|||You has to check @.@.ERROR and grab also the return value from the sps.
Implementing Error Handling with Stored Procedures
http://www.sommarskog.se/error-handling-II.html
Error Handling in SQL Server – a Background
http://www.sommarskog.se/error-handling-I.html
AMB
"Agoston Bejo" wrote:
> Hi,
> suppose I've got 3 stored procedures: P1, P2, P3. I would like to run them
> in some order and roll back the whole series of actions if an error occurs
> in any of them. (Basically I would like to achieve the same effect as thin
gs
> happen in an Oracle environment.) So:
> BEGIN TRANSACTION tran_1
> EXEC P1;
> EXEC P2;
> EXEC P3;
> IF [there was an error somewhere] -- how?
> ROLLBACK TRANSACTION tran_1
> ELSE
> COMMIT TRANSACTION tran_1
> So, what is the proper way to produce this behavior?
>
>
How to rollback properly
Hi,
suppose I've got 3 stored procedures: P1, P2, P3. I would like to run them
in some order and roll back the whole series of actions if an error occurs
in any of them. (Basically I would like to achieve the same effect as things
happen in an Oracle environment.) So:
BEGIN TRANSACTION tran_1
EXEC P1;
EXEC P2;
EXEC P3;
IF [there was an error somewhere] -- how?
ROLLBACK TRANSACTION tran_1
ELSE
COMMIT TRANSACTION tran_1
So, what is the proper way to produce this behavior?
Agostan,
I would handle this situation by testing the error status of each of the
procedures using a return code. The following code fragment would sit after
each SQL statement in the procedure:
IF @.@.ERROR <> 0
BEGIN
RETURN 1
END
You would also need to include a return code at the end of the procedure for
successful completion:
RETURN 0
Then your procedure calls would need to collect this return code into a
variable and and test the result:
EXEC @.RC1 = P1
EXEC @.RC2 = P2
EXEC @.RC3 = P3
The convention I use is to have a positive non-zero return code for failure
and a zero return code for success, so to test for success or failure in your
example I would use:
IF @.RC1 + @.RC2 + @.RC3 > 0
BEGIN...etc
Hope this helps,
"Agoston Bejo" wrote:
> Hi,
> suppose I've got 3 stored procedures: P1, P2, P3. I would like to run them
> in some order and roll back the whole series of actions if an error occurs
> in any of them. (Basically I would like to achieve the same effect as things
> happen in an Oracle environment.) So:
> BEGIN TRANSACTION tran_1
> EXEC P1;
> EXEC P2;
> EXEC P3;
> IF [there was an error somewhere] -- how?
> ROLLBACK TRANSACTION tran_1
> ELSE
> COMMIT TRANSACTION tran_1
> So, what is the proper way to produce this behavior?
>
>
|||You has to check @.@.ERROR and grab also the return value from the sps.
Implementing Error Handling with Stored Procedures
http://www.sommarskog.se/error-handling-II.html
Error Handling in SQL Server – a Background
http://www.sommarskog.se/error-handling-I.html
AMB
"Agoston Bejo" wrote:
> Hi,
> suppose I've got 3 stored procedures: P1, P2, P3. I would like to run them
> in some order and roll back the whole series of actions if an error occurs
> in any of them. (Basically I would like to achieve the same effect as things
> happen in an Oracle environment.) So:
> BEGIN TRANSACTION tran_1
> EXEC P1;
> EXEC P2;
> EXEC P3;
> IF [there was an error somewhere] -- how?
> ROLLBACK TRANSACTION tran_1
> ELSE
> COMMIT TRANSACTION tran_1
> So, what is the proper way to produce this behavior?
>
>
suppose I've got 3 stored procedures: P1, P2, P3. I would like to run them
in some order and roll back the whole series of actions if an error occurs
in any of them. (Basically I would like to achieve the same effect as things
happen in an Oracle environment.) So:
BEGIN TRANSACTION tran_1
EXEC P1;
EXEC P2;
EXEC P3;
IF [there was an error somewhere] -- how?
ROLLBACK TRANSACTION tran_1
ELSE
COMMIT TRANSACTION tran_1
So, what is the proper way to produce this behavior?
Agostan,
I would handle this situation by testing the error status of each of the
procedures using a return code. The following code fragment would sit after
each SQL statement in the procedure:
IF @.@.ERROR <> 0
BEGIN
RETURN 1
END
You would also need to include a return code at the end of the procedure for
successful completion:
RETURN 0
Then your procedure calls would need to collect this return code into a
variable and and test the result:
EXEC @.RC1 = P1
EXEC @.RC2 = P2
EXEC @.RC3 = P3
The convention I use is to have a positive non-zero return code for failure
and a zero return code for success, so to test for success or failure in your
example I would use:
IF @.RC1 + @.RC2 + @.RC3 > 0
BEGIN...etc
Hope this helps,
"Agoston Bejo" wrote:
> Hi,
> suppose I've got 3 stored procedures: P1, P2, P3. I would like to run them
> in some order and roll back the whole series of actions if an error occurs
> in any of them. (Basically I would like to achieve the same effect as things
> happen in an Oracle environment.) So:
> BEGIN TRANSACTION tran_1
> EXEC P1;
> EXEC P2;
> EXEC P3;
> IF [there was an error somewhere] -- how?
> ROLLBACK TRANSACTION tran_1
> ELSE
> COMMIT TRANSACTION tran_1
> So, what is the proper way to produce this behavior?
>
>
|||You has to check @.@.ERROR and grab also the return value from the sps.
Implementing Error Handling with Stored Procedures
http://www.sommarskog.se/error-handling-II.html
Error Handling in SQL Server – a Background
http://www.sommarskog.se/error-handling-I.html
AMB
"Agoston Bejo" wrote:
> Hi,
> suppose I've got 3 stored procedures: P1, P2, P3. I would like to run them
> in some order and roll back the whole series of actions if an error occurs
> in any of them. (Basically I would like to achieve the same effect as things
> happen in an Oracle environment.) So:
> BEGIN TRANSACTION tran_1
> EXEC P1;
> EXEC P2;
> EXEC P3;
> IF [there was an error somewhere] -- how?
> ROLLBACK TRANSACTION tran_1
> ELSE
> COMMIT TRANSACTION tran_1
> So, what is the proper way to produce this behavior?
>
>
Friday, March 9, 2012
How to retrieve the T-SQL for a particular subscription?
In SQL 2005 NS, I have created a custom subscription management interface that allows for condition actions. I can currently create, update and delete subscriptions without a problem.
However, I would like to be able to retrieve the T-SQL that will be run when the event is fired to test it for correct syntax etc. I am hoping to notify the user of any possible issues as soon as the subscription is created as opposed to waiting for an error when the event fires. Since many matching fields are simply text entries, the user may enter some data values that cause the matching statement to fail and I would like to catch that error and notify the user at subscription creation.
To do this, I plan to create the subscription with a "disabled" status. The next step would be to retrieve and run the actual T-SQL that will be executed when an event is fired. If there are no errors then I will update the subscription to "enabled".
I cannot seem to find a class member or stored procedure that returns the resulting T-SQL for a single subscription.
Any help would be appreciated.
Thanks
Maybe, I didn't understand exactly what you are trying to accomplish, but as far as I can see, you do not need to retrieve T-SQL for a subscription creation. Instead, you can just do your validations in your SMI class. And you can maximize your GUI usage for validations.
For example, use as many drop-down menus or listboxes as possible instead of combo boxes, textboxes, etc. Let users choose the predefined values.
Also, say, for example, that there is a field in your subscription class which is a DateTime value. Don't let the user enter the date. Give him a Calendar or DateTimePicker controls.
If the user still has to enter some values manually (such as money values), do validations in GUI before calling a SMI method, such as
try {
double amount = Convert.ToDouble(textBox1.Text);
// invoke SMI method for creating or modifying the subscription
} catch (Exception ex) { // show error message to the user }
|||Thank you for your response.
I am already performing these types of validations and using dropdowns/calendars where appropriate.
However, I also wanted to take it a step further in case something is missed in the validations. The bottom line is; you never know what a user is going to enter in a textbox.
I would like to retrieve the actual SQL that will be run for an individual subscription and test it.
Wednesday, March 7, 2012
How to retrieve the actions using MDSCHEMA_ACTIONS?
Hello,
When I run the following code, I get an empty schema.
Dim drAction As DataRow
Dim dsActions As DataSet
dsActions = MyConnection.GetSchemaDataSet(AdomdSchemaGuid.Actions, _
New Object() {szDatabase, Nothing, szCubeName, Nothing, Nothing, szCubeName, 1})
The result:
dsActions.Tables.Count = 1 but dsActions.Tables(0).Rows.Count = 0
Even though my cube contains two actions as it is shown in the following:
<Actions>
<Action xsi:type="DrillThroughAction">
<ID>Drillthrough Action</ID>
<Name>Drillthrough Action</Name>
<TargetType>Cells</TargetType>
<Target>MeasureGroupMeasures("Values Measure")</Target>
<Type>DrillThrough</Type>
<Default>true</Default>
<MaximumRows>3</MaximumRows>
</Action>
<Action xsi:type="DrillThroughAction">
<ID>Drillthrough Action 1</ID>
<Name>Drillthrough Action 1</Name>
<TargetType>Cells</TargetType>
<Target>MeasureGroupMeasures("Values Measure")</Target>
<Type>DrillThrough</Type>
<Default>true</Default>
<MaximumRows>2</MaximumRows>
</Action>
</Actions>
Thanks,
yones
hello Yones,
i think the problem is with the restrictions you specify. I think coordinate and coordinate type in this case should be: Coordinate type should be Cell (6) and Coordinate should be a tuple defining your cell. (right now it looks like they are for cube object). So for example the query could look like (for Adventure Works)
dsActions = MyConnection.GetSchemaDataSet(AdomdSchemaGuid.Actions, _
New Object() {
"Adventure Works DW", // CATALOG_NAME
Nothing, // SCHEMA_NAME
"Adventure Works", // CUBE_NAME
Nothing, // ACTION_NAME
Nothing, // ACTION_TYPE
"([Customer].[Customer Geography].[Country].&[Canada],[Measures].[Internet Extended Amount])", // COORDINATE
6}) // COORDINATE_TYPE : MDACTION_COORDINATE_CELL (6)
hope this helps,
|||Hello Mary,
First thank you very much for your help.
I tried Cell actions instead of Cube actions as it is shown in the following code:
dsActions = MyConnection.GetSchemaDataSet(AdomdSchemaGuid.Actions, _
New Object() {"AmoAdventureWorks", Nothing, "Adventure Works", Nothing, Nothing, "[Date].[Calendar Month Name].[All Periods],[Customer].[City].[All Customers],[Measures].[Reseller Sales Amount]", 6})
But the result was the same:
dsActions.Tables.Count = 1
dsActions.Tables(0).Rows.Count = 0
And my cube contains the following action:
<Actions>
<Action xsi:type="DrillThroughAction">
<ID>Drillthrough Action 1</ID>
<Name>Drillthrough Action</Name>
<TargetType>Cells</TargetType>
<Target>MeasureGroupMeasures("Reseller Sales")</Target>
<Type>DrillThrough</Type>
<Default>true</Default>
<MaximumRows>4</MaximumRows>
</Action>
</Actions>
Please let me know if you notice any wrong in my code.
Again thank you
Yones|||hello Yones,
i think you might be missing () inside a coordinate. I.e. i think it should be "([Date].[Calendar Month Name].[All Periods],[Customer].[City].[All Customers],[Measures].[Reseller Sales Amount])".
hope this helps,
How to retrieve the actions using MDSCHEMA_ACTIONS?
Hello,
When I run the following code, I get an empty schema.
Dim drAction As DataRow
Dim dsActions As DataSet
dsActions = MyConnection.GetSchemaDataSet(AdomdSchemaGuid.Actions, _
New Object() {szDatabase, Nothing, szCubeName, Nothing, Nothing, szCubeName, 1})
The result:
dsActions.Tables.Count = 1 but dsActions.Tables(0).Rows.Count = 0
Even though my cube contains two actions as it is shown in the following:
<Actions>
<Action xsi:type="DrillThroughAction">
<ID>Drillthrough Action</ID>
<Name>Drillthrough Action</Name>
<TargetType>Cells</TargetType>
<Target>MeasureGroupMeasures("Values Measure")</Target>
<Type>DrillThrough</Type>
<Default>true</Default>
<MaximumRows>3</MaximumRows>
</Action>
<Action xsi:type="DrillThroughAction">
<ID>Drillthrough Action 1</ID>
<Name>Drillthrough Action 1</Name>
<TargetType>Cells</TargetType>
<Target>MeasureGroupMeasures("Values Measure")</Target>
<Type>DrillThrough</Type>
<Default>true</Default>
<MaximumRows>2</MaximumRows>
</Action>
</Actions>
Thanks,
yones
hello Yones,
i think the problem is with the restrictions you specify. I think coordinate and coordinate type in this case should be: Coordinate type should be Cell (6) and Coordinate should be a tuple defining your cell. (right now it looks like they are for cube object). So for example the query could look like (for Adventure Works)
dsActions = MyConnection.GetSchemaDataSet(AdomdSchemaGuid.Actions, _
New Object() {
"Adventure Works DW", // CATALOG_NAME
Nothing, // SCHEMA_NAME
"Adventure Works", // CUBE_NAME
Nothing, // ACTION_NAME
Nothing, // ACTION_TYPE
"([Customer].[Customer Geography].[Country].&[Canada],[Measures].[Internet Extended Amount])", // COORDINATE
6}) // COORDINATE_TYPE : MDACTION_COORDINATE_CELL (6)
hope this helps,
|||Hello Mary,
First thank you very much for your help.
I tried Cell actions instead of Cube actions as it is shown in the following code:
dsActions = MyConnection.GetSchemaDataSet(AdomdSchemaGuid.Actions, _
New Object() {"AmoAdventureWorks", Nothing, "Adventure Works", Nothing, Nothing, "[Date].[Calendar Month Name].[All Periods],[Customer].[City].[All Customers],[Measures].[Reseller Sales Amount]", 6})
But the result was the same:
dsActions.Tables.Count = 1
dsActions.Tables(0).Rows.Count = 0
And my cube contains the following action:
<Actions>
<Action xsi:type="DrillThroughAction">
<ID>Drillthrough Action 1</ID>
<Name>Drillthrough Action</Name>
<TargetType>Cells</TargetType>
<Target>MeasureGroupMeasures("Reseller Sales")</Target>
<Type>DrillThrough</Type>
<Default>true</Default>
<MaximumRows>4</MaximumRows>
</Action>
</Actions>
Please let me know if you notice any wrong in my code.
Again thank you
Yones|||hello Yones,
i think you might be missing () inside a coordinate. I.e. i think it should be "([Date].[Calendar Month Name].[All Periods],[Customer].[City].[All Customers],[Measures].[Reseller Sales Amount])".
hope this helps,
Subscribe to:
Comments (Atom)