Solved

MSDTC related SQL 2008 script error

Posted on 2011-02-14
3
825 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
ID: 34893001
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
ID: 34899190
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
ID: 34899213
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
CONVERT date time to a different time zone. 2 42
Contained Database Collations 6 27
Retention Policy for Backups 1 24
Upgrading Integration Services 3 28
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This is a video that shows how the OnPage alerts system integrates into ConnectWise, how a trigger is set, how a page is sent via the trigger, and how the SENT, DELIVERED, READ & REPLIED receipts get entered into the internal tab of the ConnectWise …
Delivering innovative fully-managed cloud services for mission-critical applications requires expertise in multiple areas plus vision and commitment. Meet a few of the people behind the quality services of Concerto.

929 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

21 Experts available now in Live!

Get 1:1 Help Now