Solved

MSDTC related SQL 2008 script error

Posted on 2011-02-14
3
836 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
[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 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

622 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