繁體中文当前位置: 首页 > 图文教程 > 数据库 > MSSQL > MSSQL数据库不能手动创建新的连接
- MSSQL
- SQLServer数据库和Access数据库的各自特点
- SQLite数据库最适合做网站内容管理系统（CMS）
- 彻查SQL Server数据库查询速度慢
- BUILTIN\Administrators登陆账号
- PHP简单学习,保证入门学会
- 网站主机教程(7):网站主机的数据库技术
- SQL入门：SQL Server 2000企业版安装
- SQL入门：MSDE 2000无人职守自动安装
- 如何连接注册远程SQL Server数据库
- 提高SQL Server安装安全性要做的10件事
- SQL Server连接体系结构的客户端
- 为SQL Server提供更多的内存
- 检测SQL Server数据库服务器异常现象
- SQL Server入门教程(1):SQL简介和SQL语法
- SQL Server入门教程(2):Select和DISTINCT语句
- SQL2008新特性Resource Governor
- 教你配置安全稳定的SQL Server数据库
- SQL Server教程:学习SELECT
- 如何替换SQL Server数据库内容
- Sql-Server应用程序的高级Sql注入
MSSQL数据库不能手动创建新的连接
出处:互联网 整理: 软晨网（RuanChen.com） 发布: 2009-09-30 浏览: 106 ::
收藏到网摘: n/a
相信在使用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".
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".
评论 (0) All
