ADO命令运行多个SQL语句：不能得到错误信息回：使用Connection.Errors集合 [英] ADO Command running multiple SQL statements: can't get error message back: USE THE Connection.Errors collection

问题描述

我在使用RAISERROR告诉他试图做坏事了用户的各种表一些触发器。我想原子运行多个插入和更新，回滚​​如果触发器之一是这么说的。我是邪恶在以前的生活，所以我使用的是Access VBA。

如果第一个说法是错的，我在我的VBA得到一个运行时错误。但是，如果第二次或以后的语句调用RAISERROR话，我看不到任何错误消息。

解决方案：检查connection.Errors集合

。

这是一个Access 2007 ADP前端和SQL 2005数据库。

在VBA下面是最低限度，以显示效果。我得到同样的效果，如果我包装在一个存储过程我所有的原子语句。

我想我是聪明的，把我的验证尽量靠近数据库成为可能。显然不能！

如果你愿意尝试一下code以下，交换的S1和S2的CommandText的顺序，欣赏不同的结果。

公用Sub TestSqlErrors2（） CREATE TABLE [DBO]。[tblStuff]（[ID] [INT] IDENTITY主键NOT NULL，[东东] [VARCHAR（255）NULL） DIM在cmd作为ADODB.Command 昏暗CNN作为ADODB.Connection 昏暗为r龙 昏暗S1作为字符串 昏暗S2作为字符串 S1 =插入tblStuff（东西）VALUES（'AAA'） S2 =如果1 = 1开始RAISERROR（我，i''m只是一个割草机，16,1）ROLLBACK TRANSACTION结束 设置CNN = Application.CurrentProject.Connection 设置CMD =新ADODB.Command 设置cmd.ActiveConnection = CNN cnn.BeginTrans'没什么区别 cmd.CommandText =开始交易和放大器; S2和放大器; S1和放大器; 提交事务 cmd.Executeř cnn.CommitTrans 'cnn.Errors.count是2或0 结束小组

我已经试过各种组合。在存储过程下面，我明确地（重新）抛出一个错误。但是，这个错误不会被传递回VBA。

- CREATE TABLE [DBO] [tblStuff]（[ID] [INT] IDENTITY主键NOT NULL，[东东] [VARCHAR（255）NULL） 改变PROC AddMoreStuff2 如 开始尝试   开始交易   --swap两个语句下面围着看的区别    - - 如果第一条语句是RAISERROR那么运行时错误出现在VBA    - - 如果没有出现任何错误在VBA中，虽然没有记录被添加   插入tblStuff（东西）VALUES（'AAA'）   如果1 = 1开始RAISERROR（我，i''m只是一个割草机，16,1）ROLLBACK TRANSACTION结束   提交事务 年底试 开始抓   声明@errormessage VARCHAR（1024）   设置@errormessage = ERROR_MESSAGE（）   RAISERROR（@errormessage，16，1） 到底抓

在VBA code调用这个新的PROC是...

公用Sub TestSqlErrors2（） DIM在cmd作为ADODB.Command 昏暗CNN作为ADODB.Connection 昏暗为r龙 设置CNN = Application.CurrentProject.Connection 设置CMD =新ADODB.Command 设置cmd.ActiveConnection = CNN cmd.CommandText =EXEC AddMoreStuff2 cmd.Executeř 结束小组

解决方案

请参阅的http：//支持。 microsoft.com/kb/254304

  

PRB：ADO错误集合不包含用户定义的错误   消息

     

通过使用ActiveX数据执行一个SQL Server存储过程（SP）   对象（ADO）不填充的ADO错误集合   Connection对象与上调在SP用户定义的错误。   此行为只使用OLE DB提供程序的SQL时，会出现   服务器（SQLOLEDB）建立ADO连接到SQL Server数据库。

您必须明确设置NOCOUNT在你的SQL脚本的开头。

I have some triggers on various tables that use raiserror to tell the user that he tried to do something bad. I would like to run several inserts and updates atomically, rolling back if one of the triggers says so. I was evil in a former life so I'm using Access VBA.

If the first statement is at fault, I get a run time error in my VBA. However if the second or subsequent statement calls raiserror then I can't see any error message.

SOLUTION: examine the connection.Errors collection.

This is an Access 2007 ADP front-end and a SQL 2005 database.

The VBA below is the bare minimum to show the effect. I get the same effect if I wrap all my atomic statements in a stored proc.

I thought i was being clever, putting my validation as close to the database as possible. Obviously not!

If you care to try out the code below, swap the order of s1 and s2 in CommandText and admire the different results.

Public Sub TestSqlErrors2()
'CREATE TABLE [dbo].[tblStuff]([id] [int] IDENTITY primary key NOT NULL,[stuff] [varchar](255) NULL)
Dim cmd As ADODB.Command
Dim cnn As ADODB.Connection
Dim r As Long
Dim s1 As String
Dim s2 As String

s1 = " insert into tblStuff(stuff) values ('aaa')  "
s2 = " if 1=1 begin raiserror ('me, i''m just a lawnmower',16,1)  rollback transaction end "

Set cnn = Application.CurrentProject.Connection
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = cnn
'cnn.BeginTrans 'makes no difference
cmd.CommandText = "begin transaction " & s2 & s1 & " commit transaction "
cmd.Execute r
'cnn.CommitTrans
'cnn.Errors.count is either 2 or 0
End Sub

I've tried various combinations. In the stored proc below I'm explicitly (re-) raising an error. However, that error doesn't get passed back to VBA.

--CREATE TABLE [dbo].[tblStuff]([id] [int] IDENTITY primary key NOT NULL,[stuff] [varchar](255) NULL)
alter proc AddMoreStuff2
as
begin try
  begin transaction
  --swap the two statements below around to see the difference
  --if the first statement is raiserror then a run-time error appears in VBA
  --if not, no error appears in VBA, although no records are added
  insert into tblStuff(stuff) values ('aaa') 
  if 1=1 begin raiserror ('me, i''m just a lawnmower',16,1)  rollback transaction     end
  commit transaction
end try
begin catch
  declare @errormessage varchar(1024)
  set @errormessage = error_message()
  raiserror ( @errormessage, 16, 1 )
end catch

The VBA code to call this new proc is...

Public Sub TestSqlErrors2()
Dim cmd As ADODB.Command
Dim cnn As ADODB.Connection
Dim r As Long

Set cnn = Application.CurrentProject.Connection
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = cnn

cmd.CommandText = "exec AddMoreStuff2"
cmd.Execute r

End Sub

解决方案

Please see http://support.microsoft.com/kb/254304

PRB: ADO Errors Collection Does Not Contain User-Defined Error Messages

Executing a SQL Server stored procedure (SP) by using ActiveX Data Objects (ADO) does not populate the ADO Errors collection of the Connection object with user-defined errors that are raised in the SP. This behavior only occurs when using the OLE DB Provider for SQL Server (SQLOLEDB) to establish an ADO connection to the SQL Server database.

You have to explicitly set nocount on at the beginning of your sql script.

这篇关于ADO命令运行多个SQL语句：不能得到错误信息回：使用Connection.Errors集合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！