[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 849
  • Last Modified:

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!  



0
DrLechter
Asked:
DrLechter
  • 2
1 Solution
 
jorge_torizResearch & Development ManagerCommented:
This link covers the most common problems with distributed transactions: http://groups.google.com/group/microsoft.public.sqlserver.server/msg/127255ab5a3a1fe0
0
 
DrLechterAuthor Commented:
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
 
DrLechterAuthor Commented:
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now