"Mssqlserver_8525" SQL Server database engine error

"Mssqlserver_8525" SQL Server database engine error

Author: Zheng Zuo Date: I was working on a BizTalk Server 2006 project some time ago. When using SQL adapter, I encountered that "distributed transactions have been completed. Register this session to a new transaction or null transaction. The operating system used by the author is Windows Server 2003 Enterprise Edition + SP2. The database management system uses SQL Server 2005 Enterprise Edition + SP2. Biztalk Server uses the 2006 Enterprise Edition and uses the BizTalk generate item Wizard to convert the stored procedure to XML Schema. during deployment, BizTalk SQL adapter accesses the stored procedure by specifying the corresponding XML namespace. Biztalk Server and SQL Server are deployed on different servers. If the server is not in the Windows domain, configure MSDTC for each server and set "Transaction Manager communication" to "verification not required ". I checked the msdn document and found the "mssqlserver_8525" database engine error. Msdn describes the situations in which the "mssqlserver_8525" error occurs. The programming model used in combination with the Distributed Transaction Processing coordinator and SQL server requires the application to explicitly register the distributed transaction or remove it from it. This error occurs when the following four conditions are met: 1. the application has been registered to a distributed transaction. 2. The transaction has ended (committed or rolled back) for whatever reason ). 3. user applications are not explicitly registered from distributed transactions to new distributed transactions. 4. The application attempts to execute any transaction operations other than the existing distributed transaction or register a new distributed transaction, such as sending a query or starting a local transaction. Error status 1 is used when the application executes the operation to create a local transaction, and status 2 is used when the application attempts to register with the bound session. Msdn describes related user operations. After an application registers in a distributed transaction, the application must be explicitly detached from or registered in another distributed transaction. This will be implicitly detached from the previous registered transaction. For the accurate syntax for detaching or registering from a distributed transaction, see the programming interface manual for this application. For more information, visit http://technet.microsoft.com/zh-cn/library/bb326310.aspx.

The SQL Server Stored Procedure accessed by the BizTalk SQL adapter is described as follows:

Input Multiple parameters through the stored procedure, update the data to the specified data table, and then return the successful or failed operation results to the SQL adapter using the for XML auto and elements statements. For data table update operations, colleagues use "Tran in TRAN" to start a transaction. The problem lies in the transactions added in the stored procedure. In the BizTalk project, I used orchestration to access the SQL adapter, set orchestration to "long-term transactions", and some functional blocks accessing the SQL adapter are placed in a scope, set the scope to "Atomic transaction ". The preceding error occurs after the BizTalk application is deployed. Since the stored procedure is written by colleagues in the way of SQL 2000, the first thing I think of is to use the new T-SQL statement function in SQL Server 2005-try/Catch Block instead. The try catch structure is similar to the classic structure in C. For more information about try/catch, see "use try/catch statements to solve the SQL Server 2005 deadlock ". The following are some SQL snippets: Begin try begin transaction; -- Update statement commit transaction; Set @ reutrnvalue = '01'; end trybegin catch

If @ trancount> 0

Begin rollback transaction; endend catch; returns XML at the end of the stored procedure:

Select Code as returnvalue from manifeststatus manifest

Where code = @ reutrnvaluefor XML Auto, the elements result does not display this prompt for processing a single piece of data, but deadlock rollback is found during concurrent execution. The statement may be written elsewhere. Simple analysis. "The distributed transaction has been completed. Register this session to a new transaction or null transaction. "The error occurs at the database layer and a distributed transaction is initiated in the BizTalk Server business process, the stored procedure of the Distributed Transaction call just contains its own transactions, and the transaction becomes an embedded transaction. In the case of deadlock, all transactions will be rolled back. I searched on the Internet and found that this error occurs when I access SQL server during program debugging.