StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
primarykey
Id
13708441
data
AcceptedAnswerId
0
AnswerCount
0
ClosedDate
CommentCount
0
CommunityOwnedDate
CreationDate
2012-12-04T17:22:12.927
FavoriteCount
0
LastActivityDate
2012-12-04T17:22:12.927
LastEditDate
LastEditorUserId
0
OwnerUserId
1874012
ParentId
13593591
PostTypeId
2
Score
0
ViewCount
0
LastEditorDisplayName
text
Body
<p>If you can't or don't want to use DTC, and don't want to use CLR, then then you need to call the remote sp last, as you won't be able to rollback the remote sp call.</p> <pre><code>SET NOCOUNT, XACT_ABORT ON SET REMOTE_PROC_TRANSACTIONS OFF; BEGIN TRY DECLARE @ret INT BEGIN TRAN --Perform these in a transaction, so they all rollback together EXEC [LOCAL].[DB].[table].[sp1] EXEC [LOCAL].[DB].[table].[sp2] EXEC [LOCAL].[DB].[table].[sp3] EXEC [LOCAL].[DB].[table].[sp4] --We call remote sp last so that if it fails we rollback the above transactions --We'll have to assume that remote sp takes care of itself on error. EXEC [REMOTE].[DB].[table].[sp] COMMIT END TRY BEGIN CATCH --We rollback ROLLBACK -- Insert error into log table INSERT INTO [dbo].[log_table] (stamp, errorNumber, errorSeverity, errorState, errorProcedure, errorLine, errorMessage) SELECT GETDATE(), ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE(),ERROR_PROCEDURE(), ERROR_LINE(), ERROR_MESSAGE() END CATCH </code></pre> <p>If the local sp's depend on results from the remote stored procedure, then you can use a CLR sp (will need EXTERNAL_ACCESS permissions) and manage the transactions explicitly (basically, a roll your own DTC, but no two-phase commit. You're effectively delaying the remote commit.)</p> <pre class="lang-cs prettyprint-override"><code>//C# fragment to roll your own "DTC" This is not true two-phase commit, but //may be sufficient to meet your needs. The edge case is that if you get an error //while trying to commit the remote transaction, you cannot roll back the local tran. using(SqlConnection cnRemote = new SqlConnection("<cnstring to remote>")) { try { cnRemote.Open(); //Start remote transaction and call remote stored proc SqlTransaction trnRemote = cnRemote.BeginTransaction("RemoteTran"); SqlCommand cmdRemote = cnRemote.CreateCommand(); cmdRemote.Connection = cnRemote; cmdRemote.Transaction = trnRemote; cmdRemote.CommandType = CommandType.StoredProcedure; cmdRemote.CommandText = '[dbo].[sp1]'; cmdRemote.ExecuteNonQuery(); using(SqlConnection cnLocal = new SqlConnection("context connection=true")) { cnLocal.Open(); SqlTransaction trnLocal = cnLocal.BeginTransaction("LocalTran"); SqlCommand cmdLocal = cnLocal.CreateCommand(); cmdLocal.Connection = cnLocal; cmdLocal.Transaction = trnLocal; cmdLocal.CommandType = CommandType.StoredProcedure; cmdLocal.CommandText = '[dbo].[sp1]'; cmdLocal.ExecuteNonQuery(); cmdLocal.CommandText = '[dbo].[sp2]'; cmdLocal.ExecuteNonQuery(); cmdLocal.CommandText = '[dbo].[sp3]'; cmdLocal.ExecuteNonQuery(); cmdLocal.CommandText = '[dbo].[sp4]'; cmdLocal.ExecuteNonQuery(); //Commit local transaction trnLocal.Commit(); } //Commit remote transction trnRemote.Commit(); } // try catch (Exception ex) { //Cleanup stuff goes here. rollback remote tran if needed, log error, etc. } } </code></pre>
Tags
Title
singulars
PostAcceptedAnswerId
1. This table or related slice is empty.
PostParentId
1. POReturn if remote stored procedure fails
  singulars
  PostTypePostTypeId
  PTQuestion
PostTypePostTypeId
1. PTAnswer
UserLastEditorUserId
1. This table or related slice is empty.
UserOwnerUserId
1. USStrayCatDBA
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. This table or related slice is empty.
PostsParentIdCreationDate
1. This table or related slice is empty.
VotesPostIdCreationDate
1. This table or related slice is empty.
CommentsPostId
1. This table or related slice is empty.

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.