在Excel中从VBA中的SQL Server捕获错误消息 [英] Catching error message from SQL Server in VBA in Excel

问题描述

我正在做一个excel宏，以便自动化一些查询，最终我在SQL Server中运行。我的问题是，我不知道服务器如果一个查询没有成功，可以提醒excel。

例如，我正在导入一个文件，并且没有语法错误，但是如果批量插入语句未正确设置，则可能会导致错误。对于SQL连接，我使用以下内容：

  Dim conn As ADODB.Connection 
 Dim rs As ADODB.Recordset 
 Dim sConnString As String 
 
'创建连接字符串。 
 sConnString =Provider = SQLOLEDB; Data Source = localhost; &安培; _ 
初始目录=& MyDatabase& ; &安培; _ 
集成安全= SSPI 
 
'创建Connection和Recordset对象。 
 Set conn = New ADODB.Connection 
设置rs =新建ADODB.Recordset 
 conn.Open sConnString 
 
设置rs = conn.Execute（Myquery）
  

如果我在编译代码时出现语法错误，那么它停止是好的。但如果我有另一个问题，e。 G。数据库名称不好，表已经存在，然后程序运行没有错误，我只能检测到我在SQL Server中检查。我真的想知道查询运行是否导致错误，然后将一些警报消息编码到我的宏中。

解决方案

p> ADO连接对象有一个错误集合，您可以在运行SQL后检查：

 
 conn.Errors.Clear 
设置rs = conn.Execute（Myquery）
 
如果conn.Errors.Count> 0然后
     For i = 0 To conn.Errors.Count 
         Debug.Print conn.Error （i）.Number 
         Debug.Print conn.Error（i）.Source 
          Debug.Print conn.Error（i）.Description 
     next i 
 End If 
 

这应该让你开始。你可能会发现你正在看到一个错误零，这实际上是一个状态消息;如果是这样，你会有一些额外的代码来做。

I am doing an excel macro in order to automate some query what eventually I run in SQL Server. My problem is that I don't know how the server could alert excel if a query did not succeed.

For example, I am importing a file, and there is no syntax error, but it might result in error if bulk insert statement is not set properly. For the SQL connection I use the following:

Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sConnString As String

' Create the connection string.
sConnString = "Provider=SQLOLEDB;Data Source=localhost;" & _
              "Initial Catalog=" & MyDatabase & ";" & _
              "Integrated Security=SSPI;"

' Create the Connection and Recordset objects.
Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset
conn.Open sConnString

Set rs = conn.Execute(Myquery)

If I have a syntax error while compiling the code it stops which is good. But if I have another problem, e. g. the database name is not good, the table already exists, then the program runs with no error, I only can detect when I check it in SQL Server. I really want to know somehow whether the query run has resulted in error and then code some alerting message then into my macro. How can I do that?

Every help is much appreciated!

解决方案

The ADO connection object has an Errors collection, which you can check after running your SQL:

conn.Errors.Clear
Set rs = conn.Execute(Myquery) 

If conn.Errors.Count > 0 Then
    For i = 0 To conn.Errors.Count
        Debug.Print conn.Error(i).Number 
        Debug.Print conn.Error(i).Source 
        Debug.Print conn.Error(i).Description
    next i
End If

That should get you started. You may find that you're seeing an 'error zero' that's actually a status message; if so, you'll have some additional coding to to do.

这篇关于在Excel中从VBA中的SQL Server捕获错误消息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！