cross server update problem

The system has worked for two years.

SQL Server 2000 runs on Windows 2000 Server “A”.

Another instance of SQL Server 2000 was moved from Windows 2000 Server “B” old to Windows 2003 Server “B” new, by restoring a backed up copy from old to new.

The system has cross server updates, where Server “A” is updating records in very large tables on Server “B”.

After the move from Server “B” old to Server “B” new, suddenly the cross server updates fail, but only when they are conducted within a Begin and Commit Transaction batch.

The system works when I remove the Begin and Commit Transaction, and no longer have them run in a batch.

Also, I had a cross server update, having “A” update records in “B”, from a delete trigger residing on “A” table.Deletes against the “A” table fail so long as the trigger remains.Once I remove the delete trigger from the “A” table, I can now delete records on the “A” table.

Thanks. Dotnet Fellow

Have you checked to make sure that you have a linked server set up on the machines, When moveing you might not have moved the config for the linked server.

|||

First step is to make sure that the Distributed Transaction Coordinator service is started on both servers.

Next, if they are, can you run a statement in QA and send the error message?

|||

DTC is running on both computers. There is no error message returned. Just hangs for hours until a timeout is encountered. Looking at it through Profiler Trace it appears that no activity is occurring on either server once the transaction is begun. Or, in the case of the delete triggers, it appears that everything comes to a complete standstill once the delete statement is executed. Again, once I remove the begin transaction, or once I remove the trigger, things move along normally.

Hey Davidson, what happened to Chuck Hawkins?

|||

All servers involved, old and new, have a default collation of SQL_Latin1_General_CP1_CI_AS.

My Cross Server update that I execute on Server "A" is below, where

@.p_str_source_workspace VARCHAR(50) = 'vit16.ais70.dbo.', and
@.p_str_destination_workspace VARCHAR(50) = 'vit2.opus.dbo.'

Again, it doesn't work when I uncomment the BEGIN TRANSACTION

/*
Update the source database, ensure that the archived flag is accurately marked as 'N' if the record does not yet exist in Opus.
*/

SELECT @.l_str_update = ' UPDATE ' + @.p_str_source_workspace + 'pr_history SET archived = ''N'' FROM ' + @.p_str_source_workspace + 'pr_history as pr0 WHERE archived = ''Y'' AND pr0.pr_hist_search NOT IN (SELECT pr_hist_search FROM ' + @.p_str_destination_workspace + 'pr_history)'

--BEGIN TRANSACTION

EXEC (@.l_str_update)

Thanks for keeping me in mind Erland.

|||There are new network DTC security "features" in Windows 2003 Server. See
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cossdk/html/2627a956-60b3-4d26-bc04-e0676ec97786.asp

This might you get the specific settings right, if this is indeed the problem: http://www.prophecie.co.uk/Default.aspx?dc=200509

Steve Kass
Drew University
http://www.stevekass.com