How to handle a linked database remote transaction

Environment: Windows Server 2003 SP2 + MSSQL SP4

Recently in the development of a report query system, the use of linked database technology, because the first contact with the technology, so in the process of development encountered a lot of difficulties, fortunately there are Baidu, fortunately there are enthusiastic colleagues, the problem has been resolved.

Two linking techniques were used throughout the development process:

1.ODBC link Mode

--Establish a linked server

EXEC sp_addlinkedserver ' remote_queuerpt ', ', ' msdasql ', Null,null, ' Driver={sql Server}; server=192.168.1.103; Uid=sa; Pwd=sa; ‘

--Establish a linked server login map

exec sp_addlinkedsrvlogin @rmtsrvname = ' remote_queuerpt ', @useself = ' false ', @locallogin = ' sa ', @rmtuser = ' sa ', @ rmtpassword= ' sa ' go

2.OLEDB link Mode

exec sp_addlinkedserver ' remote_queuerpt ', ', ' SQLOLEDB ', ' 192.168.1.112 '

exec sp_addlinkedsrvlogin ' remote_queuerpt ', ' false ', NULL, ' sa ', ' sa ' go

Note: After testing, the second method is found to be much faster, so it is highly recommended to use OLE DB link mode.

OK, the linked server has been established successfully, here is the stored procedure I want to execute, using the asynchronous transaction

SET QUOTED_IDENTIFIER OFF goset ANSI_NULLS on GO ALTER PROCEDURE wy_upload as SET xact_abort on-this sentence is important begin Distrib Uted TRAN--Start asynchronous transaction Declare @mac_id varchar set @mac_id =n ' 00-25-64-58-18-10 '---if the network card number is present, continue executing the statement, otherwise the statement is empty if exist  S (select Disknumber from remote_queueRpt.Wy_Report.dbo.Region WHERE [email protected]_id) Begin--Upload queued data INSERT Into remote_queueRpt.Wy_Report.dbo.History_002564581810 (Gettickettime, Yw_id,yw,num, Caption,gh,id,opertime,windo W_no,totaltime, approx_awaiting,mac_id,user_name,finsh_time,num_id) SELECT Gettickettime, YW_ID,YW,Num, capt Ion,gh,id,opertime,window_no,totaltime, Approx_awaiting, @mac_id, user_name,finsh_time,num_id from wy_tmp IF @ @ERR  Or<>0 goto quitwithrollback DElETE from wy_tmp IF @ @ERROR <>0 GOTO quitwithrollback--Upload evaluation data INSERT into remote_queueRpt.Wy_Report.dbo.opinion_002564581810 (ID, Numid,num,gh, user_name,service_level,operator_time,o PINION,WINDOW_NO,MAC_ID)        SELECT Id,numid,num,gh, User_name,service_level,operator_time,opinion,window_no, @mac_id from wy_opiniontmp    If @ @ERROR <>0 goto quitwithrollback DElETE from wy_opiniontmp IF @ @ERROR <>0 goto quitwithrollback --Upload login log INSERT into remote_queueRpt.Wy_Report.dbo.loginlog_002564581810 (GH, User_name,window_no,info, LogTime, id,mac_id) SELECT Gh,user_name,window_no,info, Logtime,id, @mac_id from Wy_loginlog IF @ @ERROR <>0 GO To Quitwithrollback DElETE from Wy_loginlog IF @ @ERROR <>0 goto quitwithrollback COMMIT TRAN goto Endsave Quitwithrollback:rollback transactionendsave:print ' 1 ' End goset quoted_identifier OFF goset ANSI_NULLS on GO

　　

The stored procedure is created, and the next step is to execute the stored procedure to send the data.

EXEC Wy_upload

The statement executed nearly a minute later, and finally executed, the result is certainly not so smooth, the error message will pop up as shown:

Server: Msg 7931, Level 16, State 1, line 5

The operation failed to execute because the OLE DB provider ' SQLOLEDB ' could not start the distributed transaction

[OLE/DB provider returned message: The new transaction cannot be ranked into the specified transaction processor]

OLE DB Error tracing [ole/db Provider ' SQLOLEDB ' ITransactionJoin::JoinTransaction returned 0x8004d00a]

The problem arises, Baidu query after the following settings:

Network MSDTC configuration method One, to enable network DTC access

Enabling network DTC is a necessary condition for the control of distributed database things. The setup process should follow the steps below.

1) Click Start, point to Control Panel, and then click Add or Remove Programs;

2) Click "Add/Remove Windows components";

3) Select "Application Server" and click "Details";

4) Select "Enable Network DTC Access", network management, network transactions, XA transactions. In addition, the DTC login account must be set to "NT AUTHORITY\NetworkService" and transaction manager traffic does not require authentication. Then click "OK";

5) Click "Next";

6) Click "Finish";

7) Stop the Distributed Transaction Coordinator service and restart it;

8) Stop Microsoft SQL Server and other resource manager services that participate in distributed transactions, such as Microsoft Message Queuing, and then restart them.

Second, Windows XP Configuring MSDTC the method

Control Panel-----Management Tools-----) Component Services

Select Component Services-----computer--------)My Computer, and then right-click the mouse button.

Click the Security Configuration (I) ... button and select the following:

Note: You should restart the MSDTC service after the setup is successful, and then restart the data service to note the sequencing.

This thought the work is about to Gaocheng, but after the implementation of the stored procedure problem remains, Baidu, will have the following solutions

Start the RPC service, the service should be started by default? Exclude

Turn on port 135 and set the exception (firewall settings),

C:\windows\system32\Driver\etc\Host for IP and Domain name mapping.

　　　　　　　　　　　　　　　　

The following are the delete linked server login mappings and linked servers:

exec sp_droplinkedsrvlogin ' remote_queuerpt ', ' sa '

exec sp_dropserver ' remote_queuerpt '

How to handle a linked database remote transaction