mssql 不能连接mysql_MSSQL数据库不能手动创建新的连接_MySQL

相信在使用MSSQL数据库下使用事务回滚方式操作多表记录的时候，会经常出现“不能在手动或分布事务方式下创建新的连接”的出错提示信息，这个问题也已困扰我多年。

这次在开发一个大型的商务平台的时候，涉及到数据的计算，同时也必须要多表更新(或删除)。借助GOOGLE也没找到一个真能解决的问题。以前收集过一个MSDN的说明，官方的提示必须，只有用SQL语句执行数据库操作才能使用事务处理。

从官方提示上理解，事式处理中涉及到查询(Select)时，会出现这种出错提示。同时也应该与记录指针(Cursors)有关联。试着这样的思路，将事务处理中原出现的Conn.Execute("select ... from ...")修改为使用rs.Open...命令打开记录集，问题解决。

MSDN说明：

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".

本条技术文章来源于互联网，如果无意侵犯您的权益请点击此处反馈版权投诉

本文系统来源：php中文网