BEGIN TRAN . . . WITH MARK . . .

Hallo All,

Can somebody explain why the same function works different with MS SQL 2000 and MS SQL 2005?



On both systems 2000 and 2005 I have 2x databases named ACCT and PROD (actually only a test environment).



On both systems I try to execute the following statements:



BEGIN TRAN TRAN_01 WITH MARK 'My TRAN_01'

USE PROD

INSERT INTO [PROD].[dbo].[_PROT]([STR_COMMENT], [R_NUM_T1], [R_NUM_T2])

VALUES('PROT_COMMENT', 1004, 1004)

USE ACCT

INSERT INTO [ACCT].[dbo].[_PROT]([STR_COMMENT], [R_NUM_T1], [R_NUM_T2])

VALUES('PROT_COMMENT', 1004, 1004)

COMMIT TRAN TRAN_01



After executing the statements I start the following query:



SELECT * FROM [msdb].[dbo].[logmarkhistory]



On MS SQL 2000 I get as results:



PROD TRAN_01 My TRAN_01 SUPPORTAdministrator 3944000000107600001 2006-03-29 17:15:13.930

ACCT TRAN_01 My TRAN_01 SUPPORTAdministrator 8000000009200001 2006-03-29 17:15:13.930



Seems to be correct. I think it is the way it should work according to the documentation.



On MS SQL 2005 I only get the following results:



PROD TRAN_01 My TRAN_01 SU 29000000107800001 2006-03-29 17:31:32.283



There are no entries in the table for the ACCT database and the account / user_name is shown incorrectly.



It seems to be an ERROR in the processing of such marked transactions in MS SQL 2005.