Link to home
Start Free TrialLog in
Avatar of DrLechter
DrLechter

asked on

MSDTC related SQL 2008 script error

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!  



ASKER CERTIFIED SOLUTION
Avatar of jorge_toriz
jorge_toriz
Flag of Mexico image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of DrLechter
DrLechter

ASKER

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.
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.