Solved

MSDTC related SQL 2008 script error

Posted on 2011-02-14
3
824 Views
Last Modified: 2012-05-11
I am using a script (generated by RedGate SQL compare) to synchronize a  stored procedure on two local SQL 2008 64-bit servers running under Windows 2008 R2.   Each of these local SQL servers have identical linked servers setup using MS OLE DB Provider for ODBC with associated system DSN's to a third MS SQL target server using a SQL Server driver.

As far as I can tell, the DSN's and the SQL linked server setups are identical.

Here is the script I am trying to run:  

/*
Run this script on:

        QA_Server.MyDB    -  This database will be modified

to synchronize it with:

        DEV_Server.MyDB

You are recommended to back up your database before running this script

Script created by SQL Compare version 8.1.0 from Red Gate Software Ltd at 2/14/2011 10:45:46 AM

*/
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors
GO
CREATE TABLE #tmpErrors (Error int)
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
GO
PRINT N'Altering [dbo].[spMyDBTempTblImport]'
GO

ALTER  procedure [spMyDBTempTblImport]
as
begin

      exec spMyDBTempTblTruncate
      
      insert TempTbl
      (
            MyFld1,
            MyFld2
      )

      SELECT *
      from OPENQUERY(MyLinkedServer,
      '
      select
            [MyFld1],
            [MyFld2]
      FROM MyTbl
      ')

end
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT>0 BEGIN
PRINT 'The database update succeeded'
COMMIT TRANSACTION
END
ELSE PRINT 'The database update failed'
GO
DROP TABLE #tmpErrors
GO



Here is the error I get:

OLE DB provider "MSDASQL" for linked server "MyLinkedServer" returned message "[Microsoft][ODBC SQL Server Driver]Distributed transaction error".
Msg 7391, Level 16, State 2, Procedure spCsiTempSubmissionImport, Line 15
The operation could not be performed because OLE DB provider "MSDASQL" for linked server "MyLinkedServer" was unable to begin a distributed transaction.


Interestingly, when I run a simple "alter proc" script on my QA_Server, I don't get any error.

I believe there is some difference in the setup of the MSDTC stuff on the two servers but I'm not sure how to check all the various settings.  

I would appreciate any help you can offer to solve the problem!  



0
Comment
Question by:DrLechter
  • 2
3 Comments
 
LVL 15

Accepted Solution

by:
jorge_toriz earned 500 total points
Comment Utility
This link covers the most common problems with distributed transactions: http://groups.google.com/group/microsoft.public.sqlserver.server/msg/127255ab5a3a1fe0
0
 
LVL 4

Author Comment

by:DrLechter
Comment Utility
Jorge:

Thanks for the article.  I ran through all the stuff and discovered two problems:  one was that I needed to make an entry in the hosts file to resolve an aliasing issue.  The second was that DTC is actually not enabled on one of the servers.
0
 
LVL 4

Author Comment

by:DrLechter
Comment Utility
Oh:  one other thing:  if I change script from

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

to

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

then the script runs correctly.  This is a pain if you're using Red Gate.  They told me they're going to make this an optional setting in a future release.
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now