Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Distributed transaction within a stored procedure

Posted on 2004-10-21
3
Medium Priority
?
304 Views
Last Modified: 2008-01-09
Hi, I have stored proc which goes something like this:

CREATE PROC sch_update_ccnd ( @ssn varchar(10), @bd datetime)
AS

DECLARE @trncnt int
SELECT @trncnt = @@trancount

IF @trncnt = 0
      BEGIN DISTRIBUTED TRAN succnd
ELSE
      SAVE TRAN succnd

IF EXISTS (SELECT ssn FROM int_lukebox.db.dbo.T WHERE ssn=@ssn)
BEGIN
      UPDATE int_lukebox.db.dbo.T
          SET bd=@bd
          WHERE ssn = @ssn
END
ELSE
BEGIN
      INSERT INTO int_lukebox.db.dbo.T(ssn,bd)
      VALUES(@ssn,@bd)
END

IF (@@ERROR != 0)
BEGIN
      ROLLBACK TRAN succnd
      RETURN -999
END

IF @trncnt = 0
      COMMIT TRAN succnd

RETURN 0
GO


This thing just hangs and never returns. Any pointers will be appreciated. If I remove all the transactional stuff from it, it executes fine. So something to do with distributed transactions and linked servers. BTW, the security context of the linked server has both read and write priviledges on both the servers.
0
Comment
Question by:dbdoshi
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 13

Accepted Solution

by:
danblake earned 750 total points
ID: 12370502
You should be able to cut-down the code, slightly with the following structure:

IF @@TRANCOUNT = 0
  BEGIN DISTRIBUTED TRAN succnd
ELSE
  SAVE TRAN succnd

..
..
IF (@@ERROR != 0) COMMIT TRAN
ELSE
BEGIN
   ROLLBACK TRAN
   RETURN -9999
END

RETURN 0

When you place a few PRINT statements in this procedure, where does it stop or hang?
Check the SQL Server Log for any error messages from the MS DTC Component.
Also does the MS DTC have permissions on both servers ?  (Is MS DTC Running at all -- this is what actually runs the and manages the distributed transactions).
0
 

Author Comment

by:dbdoshi
ID: 12373615
I searched through the web and on one of the microsoft KB articles they say that savepoints are not allowed in distributed transactions, unless you start the sql server with a particular trace flag.
0
 

Author Comment

by:dbdoshi
ID: 12380157
I finally solved the problem. It was a windows 2003 server issue and netbios name resolution problem.
0

Featured Post

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question