相信在使用MSSQL數(shù)據(jù)庫(kù)下使用事務(wù)回滾方式操作多表記錄的時(shí)候，會(huì)經(jīng)常出現(xiàn)“不能在手動(dòng)或分布事務(wù)方式下創(chuàng)建新的連接”的出錯(cuò)提示信息，這個(gè)問(wèn)題也已困擾我多年。
這次在開發(fā)一個(gè)大型的商務(wù)平臺(tái)的時(shí)候，涉及到數(shù)據(jù)的計(jì)算，同時(shí)也必須要多表更新（或刪除）。借助GOOGLE也沒找到一個(gè)真能解決的問(wèn)題。以前收集過(guò)一個(gè)MSDN的說(shuō)明，官方的提示必須，只有用SQL語(yǔ)句執(zhí)行數(shù)據(jù)庫(kù)操作才能使用事務(wù)處理。
從官方提示上理解，事式處理中涉及到查詢（Select）時(shí)，會(huì)出現(xiàn)這種出錯(cuò)提示。同時(shí)也應(yīng)該與記錄指針（Cursors）有關(guān)聯(lián)。試著這樣的思路，將事務(wù)處理中原出現(xiàn)的Conn.Execute("select ... from ...")修改為使用rs.Open...命令打開記錄集，問(wèn)題解決。
MSDN說(shuō)明：
Tips for Working with Cursors
Some providers, such as SQL Server, implement a forward-scrolling, read-only (or ’firehose’) cursor mode, meaning that they can efficiently retrieve data by keeping a connection open. When working with such providers, the connection could be blocked by another user’s transaction. The following examples demonstrate scenarios
that result in errors.
dbConn.Open "DSN=SQLForum;UID=sa;PWD=;"
’Example 1
dbConn.BeginTrans
RS.Open "Select * FROM Message", dbConn
Set dbCmd.ActiveConnection = dbConn
Example 1: The problem is that the command object’s ActiveConnection is being set to a connection that is forward-scrolling and in ’firehose’ mode. This is the same connection involved in the batch mode. The error from the provider will only appear in the Err object, and it will return as unspecified. For example, with the ODBC Provider, you will get "Unspecified error".
dbConn.Open "DSN=SQLForum;UID=sa;PWD=;"
’Example 2
RS.Open "Select * FROM Message", dbConn
dbConn.BeginTrans
Example 2: The problem here is that the connection is forward-scrolling and in firehose mode, so it cannot be put into transaction mode. The error returned in the Errors collection from the provider will indicate that it is operating in firehose mode, and can’t work in transaction mode. For example, with the ODBC Provider against
Microsoft SQL Server, you will get the error "Cannot start transaction while in firehose mode".
dbConn.Open "DSN=SQLForum;UID=sa;PWD=;"
’Example 3
RS.Open "Select * FROM Message", dbConn
Set dbCmd.ActiveConnection = dbConn
dbConn.BeginTrans
Example 3: The problem here is that the connection is in forward-scrolling firehose mode, so it cannot also be involved in a batch mode. The error returned in the Errors collection from the provider will indicate that the transaction could not be started. For example, with the ODBC Provider against Microsoft SQL Server, you will get the error "Cannot start transaction because more than one hdbc is in use".
這次在開發(fā)一個(gè)大型的商務(wù)平臺(tái)的時(shí)候，涉及到數(shù)據(jù)的計(jì)算，同時(shí)也必須要多表更新（或刪除）。借助GOOGLE也沒找到一個(gè)真能解決的問(wèn)題。以前收集過(guò)一個(gè)MSDN的說(shuō)明，官方的提示必須，只有用SQL語(yǔ)句執(zhí)行數(shù)據(jù)庫(kù)操作才能使用事務(wù)處理。
從官方提示上理解，事式處理中涉及到查詢（Select）時(shí)，會(huì)出現(xiàn)這種出錯(cuò)提示。同時(shí)也應(yīng)該與記錄指針（Cursors）有關(guān)聯(lián)。試著這樣的思路，將事務(wù)處理中原出現(xiàn)的Conn.Execute("select ... from ...")修改為使用rs.Open...命令打開記錄集，問(wèn)題解決。
MSDN說(shuō)明：
Tips for Working with Cursors
Some providers, such as SQL Server, implement a forward-scrolling, read-only (or ’firehose’) cursor mode, meaning that they can efficiently retrieve data by keeping a connection open. When working with such providers, the connection could be blocked by another user’s transaction. The following examples demonstrate scenarios
that result in errors.
dbConn.Open "DSN=SQLForum;UID=sa;PWD=;"
’Example 1
dbConn.BeginTrans
RS.Open "Select * FROM Message", dbConn
Set dbCmd.ActiveConnection = dbConn
Example 1: The problem is that the command object’s ActiveConnection is being set to a connection that is forward-scrolling and in ’firehose’ mode. This is the same connection involved in the batch mode. The error from the provider will only appear in the Err object, and it will return as unspecified. For example, with the ODBC Provider, you will get "Unspecified error".
dbConn.Open "DSN=SQLForum;UID=sa;PWD=;"
’Example 2
RS.Open "Select * FROM Message", dbConn
dbConn.BeginTrans
Example 2: The problem here is that the connection is forward-scrolling and in firehose mode, so it cannot be put into transaction mode. The error returned in the Errors collection from the provider will indicate that it is operating in firehose mode, and can’t work in transaction mode. For example, with the ODBC Provider against
Microsoft SQL Server, you will get the error "Cannot start transaction while in firehose mode".
dbConn.Open "DSN=SQLForum;UID=sa;PWD=;"
’Example 3
RS.Open "Select * FROM Message", dbConn
Set dbCmd.ActiveConnection = dbConn
dbConn.BeginTrans
Example 3: The problem here is that the connection is in forward-scrolling firehose mode, so it cannot also be involved in a batch mode. The error returned in the Errors collection from the provider will indicate that the transaction could not be started. For example, with the ODBC Provider against Microsoft SQL Server, you will get the error "Cannot start transaction because more than one hdbc is in use".