Re: getting only first error when executing multiple sql statements

From: Val Mazur (group51a_at_hotmail.com)
Date: 07/01/04 

Date: Thu, 1 Jul 2004 15:56:29 -0400

Hi Ronny,

It means that provider does not return more errors. I think if it fails on a
first statement, then process will be cancelled and this is why you get only
one error, not four of them, because next statements were not executed 

-- 
Val Mazur
Microsoft MVP
"Ronny Zuschke" <NoSpamPlease@dummy.org> wrote in message 
news:OEZ%23Bi3XEHA.3988@tk2msftngp13.phx.gbl...
> Hello everyone,
>
> I am using ADO with VB.NET to connect to a SQL Server 2000. I use the 
> Command object to execute multiple SQL statements in one batch. The 
> problem I have is that as soon as any errors occur in that statement I can 
> only get the first error out of Errors collection.
>
> For example:
> In my DB I have a table called 'myTable' with a column named 'name' which 
> is the primary key. The 'name' column is filled with dummy values 'test1', 
> 'test2', etc. If I would execute the following SQL statements separately I 
> would get 4 errors each time. That's clear.
>
> insert into myTable( name ) values ('test1')
> insert into myTable( name ) values ('test2')
> insert into myTable( name ) values ('test3')
> insert into myTable( name ) values ('test4')
>
> But when I put these statments into one batch there's only the first error 
> in the Errors collection.
>
> Here is some code:
>
>         Dim conn1 As New ADODB.Connection
>         conn1.ConnectionString = "Provider=SQLOLEDB.1;Integrated 
> Security=SSPI;Persist Security Info=False;Initial Catalog=myDatabase;Data 
> Source=myDataSource"
>         conn1.Open()
>
>         Dim cmd1 As New ADODB.Command
>         Dim sr As New StreamReader("sql_statements.txt")
>         Dim sSQL As String = sr.ReadToEnd()
>
>         Try
>             With cmd1
>                 .ActiveConnection = conn1
>                 .CommandText = sSQL
>                 .Execute()
>             End With
>         Catch
>             Dim errors As ADODB.Errors = cmd1.ActiveConnection.Errors()
>             For i As Integer = 0 To (errors.Count - 1) Step 1
>                 MessageBox.Show(errors.Item(i).Description)
>             Next
>         End Try
>
> I am reading the SQL statements out of the 'sql_statements.txt' file. The 
> Errors object does actually contain 2 items. The first item is the error 
> that the key could not be created, the second just telling that the 
> statement was aborted. The Exception object does also just contain the 
> first error.
>
> Is this supposed to work like this or am I doing something wrong? Any help 
> would be appreciated.
> Ronny Zuschke
>

Relevant Pages

  • Re: Is there a way to speed up Index Fast Full Scan
    ... when processing those slow SQL statements. ... session by checking V$SESSION. ... execute one of the slow ... then have the session execute the slow SQL statements. ...
    (comp.databases.oracle.server)
  • Re: Is there a way to speed up Index Fast Full Scan
    ... when processing those slow SQL statements. ... session by checking V$SESSION. ... execute one of the slow ... then have the session execute the slow SQL statements. ...
    (comp.databases.oracle.server)
  • Re: comparing tkprof results between cursor_sharing = exact or similar
    ... 90 internal SQL statements in trace file. ... Misses in library cache during execute: ... fact be different from the execution plan in the first case. ...
    (comp.databases.oracle.server)
  • SQL::Statement questions
    ... I have a file containing several SQL statements mainly ... PRIMARY KEY ... ALTER TABLE Tray ... Is SQL::Statement the appropraite module for this or is there a module ...
    (perl.dbi.users)
  • Re: object closed error
    ... when I execute it with Query Analyzer. ... Elizabeth ... post your SQL statements and point to the line causing the ... >> I am trying to execute an SQL statement but get an object closed error. ...
    (microsoft.public.vb.database.ado)