> I am attempting to push data updates via linked server from a master
> database (running in a SQL Server 2005 instance) to a local database
> (running in a SQL Server 2000 instance) via linked server.
>
> I am performing the updates via stored procedures.
>
> I know I need to configure the linked server security appropriately on
> both the master and local databases (I did), and to SET XACT_ABORT ON
> within the stored procedure (I am).
>
> I did get this configuration working successfully when I used a SQL2000
> instance on the same machine as the SQL2005 instance - it completely
> worked. Now I am attempting to move to the production scenario where
> the local database is on a server in a completely different facility
> (but part of the WAN) - now I get this error:
>      Error#: 7391 -- The operation could not be performed because OLE
> DB
>      provider "SQLNCLI" for linked server "10.1.12.8" was unable to
> begin a distributed
>      transaction.
>
> Turning DBCCTraceon showed the complete error message:
>       OLE DB provider "SQLNCLI" for linked server "10.1.12.8" returned
> message "The
>       transaction manager has disabled its support for remote/network
> transactions.".
>
>
> I completely expect something needs to be enabled/turned on on this
> SQL2000 server in the different facility, but what? That server is
> running Windows 2000 Server SP4. The SQL2005 server is running Windows
> Server 2003 Std SP1.
>

"ccjjharmon" <ccjjhar***@gmail.com> wrote in message
news:1151069345.975788.272470@p79g2000cwp.googlegroups.com...
> That helped, but now I am in a different scenario. What I did:
> Add/Remove Windows Components - under Application Server only checked
> "Enable Network DTC Access" (nothing else checked). For the DTC
> Security Configuration, I enabled "Network DTC Access" and "Allow
> Outbound" under Transaction Manager Communication.
>
> When I execute one of my stored procedures now, I get this error:
>
> OLE DB provider "SQLNCLI" for linked server "LINE1800" returned message
> "No transaction is active.".
>

> http://blogs.msdn.com/mab/archive/2005/12/30/508273.aspx
>
> Also did you set REMOTE_PROC_TRANS to 1?
>

"ccjjharmon" <ccjjhar***@gmail.com> wrote in message
news:1151072400.899544.197740@u72g2000cwu.googlegroups.com...
> But this is Win2003SP1+SQL2005 to Win2000SP4+SQL2000... I have gone
> through the stuff as far as I know for the Win2003SP1 server... but am
> wondering if I need to do something on the Win2000SP4 server... ?
>
> I was trying to set it - what's the syntax?
>
> in my stored procedure, I do this:
>
> IF @enablePressSynchronization=1 BEGIN
> SET xact_abort on
> --SET remote_proc_trans on
> END -- required for distributed transactions using linked servers!
>
> but I get 'remote_proc_trans' is not a recognized SET option.
>
>
>
>
>> http://blogs.msdn.com/mab/archive/2005/12/30/508273.aspx
>>
>> Also did you set REMOTE_PROC_TRANS to 1?
>>
>

> http://msdn2.microsoft.com/en-us/library/ms178549.aspx
>

> Thanks for the responses and continuing with me, but the hole continues
> downward... :)
>
> I added that SQL code, and changed my BEGIN TRANSACTION to BEGIN
> DISTRIBUTED TRANSACTION. Now the error is
>
> Msg 7391....
> The operation could not be performed because OLE DB provider "SQLNCLI"
> for linked server "LocalPress" was unable to begin a distributed
> transaction.
>
> So I still wonder about the "local press" database server... don't I
> need to at least check the DTC on that end too? How can I do that?
>
> At this point, I really should provide a complete stored procedure
> definition in case it would be helpful, so here is one:
>
>
>
> ALTER PROC [dbo].[uspUpdateDieSuff]
>   @dieNumber      AS sysname,        -- Die #
>   @dieRevision      AS sysname,    -- die revision
>   @dieSuffix      AS sysname,    -- die suffix
>   @status          AS varchar(50)    -- status
> AS
>
> DECLARE @enablePressSynchronization bit
> SET @enablePressSynchronization = 1 /* 0 --> don't sync      1 -->
> sync!  */
>
> DECLARE @foundDieSuffKey AS int
> DECLARE @foundDieRevKey AS int
> DECLARE @statusBit as int
> SET @statusBit = 0
> IF @status='SCRAP' OR @status='HOLD' SET @statusBit=1
>
> DECLARE @dieSuffSQLStmt varchar(5000)
> SET @dieSuffSQLStmt = ''
>
> SELECT @foundDieSuffKey = ds.SUFFIX_KEY, @foundDieRevKey =
> ds.REVISION_KEY FROM DieSuffix ds
> INNER JOIN DieRevision dr ON ds.REVISION_KEY=dr.REVISION_KEY
> WHERE dr.DIE_NUMBER=@dieNumber AND dr.REVISION=@dieRevision AND
> ds.DIE_SUFFIX=@dieSuffix
> PRINT @foundDieSuffKey
> IF NOT @foundDieSuffKey IS NULL BEGIN
>
>    IF @enablePressSynchronization=1 BEGIN
>       SET xact_abort on
>       SET REMOTE_PROC_TRANSACTIONS ON
>    END -- required for distributed transactions using linked servers!
>    BEGIN DISTRIBUTED TRANSACTION
>
>    --update the record
>    UPDATE DieSuffix SET STATUS=@status WHERE
> SUFFIX_KEY=@foundDieSuffKey
>
>    -- press sync if enabled
>    IF @enablePressSynchronization=1 BEGIN
>
>         SET @dieSuffSQLStmt = '
>         UPDATE [LocalPress].[PressDB].dbo.diesuffix SET ' +
>             ' STATUS=' + CAST(@statusBit AS varchar(10)) +
>             ' WHERE SUFFIX_KEY=' + @foundDieSuffKey + '; '
>         BEGIN TRY
>             PRINT(@dieSuffSQLStmt)
>             EXEC(@dieSuffSQLStmt)
>         END TRY
>         BEGIN CATCH
>             ROLLBACK TRANSACTION
>             -- return an error - be specific on why it wasn't recorded.
>         END CATCH
>
>    END -- end press sync
>    COMMIT TRANSACTION
> END
>
>
>
>
>
>
>
> Mike C# wrote:
> > http://msdn2.microsoft.com/en-us/library/ms178549.aspx
> >

> ccjjharmon wrote:
> > Tracy? Any idea on the DTC side of what I may have missed?
> >
>
> I agree, you should check the DTS properties on the "LocalPress" server.
>   I can't come up with anything else that would cause this error...

> ccjjharmon wrote:
> > That's what I don't know anything about... how do I check the DTS
> > properties? The same settings mentioned before only apply for
> > Win2003/XP - not Win2000!
> >
> > Is there info about configuring this same stuff for Win2000???
> >
>
> Are you certain the MSDTC service running?

>
> Can you post a screenshot of the security configuration window for DTC
> from your Windows 2003 server?

> Well here are a couple more suggestions (you might have already tried these,
> but hey...)  I don't have SQL 2005 here at work, so I can't test anything
> myself.  Sorry...
>
> http://support.microsoft.com/default.aspx?scid=kb;en-us;306843
> http://support.microsoft.com/default.aspx?scid=kb;en-us;329332
> http://support.microsoft.com/kb/306212/en-us
> http://support.microsoft.com/kb/827805
>
> One thing that caught my eye is the end of the second article "Note: In some
> cases you must start the DTC service before you start the SQL Server service
> so that linked server distributed queries work well."
>
> The fourth article mentions problems with mutual authentication if the
> computers aren't in the same domain.
>
>
>
> "ccjjharmon" <ccjjhar***@gmail.com> wrote in message
> news:1151090265.510443.25200@u72g2000cwu.googlegroups.com...
> > Take a look at http://www.flickr.com/photos/ccjjharmon/173394978/
> >
> >>
> >> Can you post a screenshot of the security configuration window for DTC
> >> from your Windows 2003 server?
> >

> Thanks for the ideas.
>
> The last thing I did before the weekend was to simulate the entire
> environment locally (no WAN involved - only a network switch between
> the computers) with a SQL2005 running on Win2003 and a SQL2000 running
> on WinXP. It worked just fine... making me think there are two
> possibilities:
>
> firewall issues
> or
> differences because the SQL2000 instance is running on a server that
> isn't on the same domain as the Win2003 SQL2005 instance....
>
> My research will continue - and I will post what the cause ends up
> being for anyone's reference.
>