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 Fellowdotnetfellow@.yahoo.com wrote:
> 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
>
Transactions that involve multiple servers are called "distributed"
transactions, and rely on the MSDTC service to function. On Windows
2003 Server, MSDTC refuses network connections by default. Start here:
http://support.microsoft.com/kb/899191/
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||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.|||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.'
/*
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)