ADO problem with multiple SQL statements using execute method 
Author Message
 ADO problem with multiple SQL statements using execute method

Hi,

I am using SQL7 and ADO 2.5. I am trying to pass sql statment
consisting 500 or more insert statements. But surprisingly ADO with
default ole-db provider for odbc driver insert less number of records.
It works fine with sqloledb provider. Here is the code that
uses employee table in pubs database.

Dim i As Integer
Dim sqlStmt As String
Dim conn As ADODB.Connection
Dim emp As Long

    emp = 10000
    Set conn = CreateObject("ADODB.Connection")
    conn.ConnectionTimeout = 0
    conn.CommandTimeout = 0
    'This does not insert all records
        conn.Open "driver={SQL
Server};server=(local);database=pubs;UID=;Pwd=;"
    'This works
        conn.Open "Provider=SQLOLEDB;Data Source=(local);Initial
Catalog=pubs;UID=sa;Pwd=;"

    For i = 1 To 500
    emp = emp + 1
    sqlStmt = sqlStmt & "INSERT INTO employee  (emp_id,   fname,  
minit," & _
        "lname ) VALUES  ('AAA" & CStr(emp) & "F','fname','M','LNAME'
) " & vbCrLf & vbCrLf

    Next i

    conn.Execute sqlStmt

Why is this behavior? Any help is appreciated as I am not able to find
soultion anywhere.

Thanks
Vikas



Mon, 11 Jul 2005 02:11:45 GMT
 ADO problem with multiple SQL statements using execute method

Vikas,

Not all providers allow to execute multiple SQL statements. Not sure about
OLEDB for ODBC, but in case of OLEDB fr SQL Server, you need to separate
those statements by semicolon (;) to execute them in one batch

--
Val Mazur
Microsoft MVP


Quote:
> Hi,

> I am using SQL7 and ADO 2.5. I am trying to pass sql statment
> consisting 500 or more insert statements. But surprisingly ADO with
> default ole-db provider for odbc driver insert less number of records.
> It works fine with sqloledb provider. Here is the code that
> uses employee table in pubs database.

> Dim i As Integer
> Dim sqlStmt As String
> Dim conn As ADODB.Connection
> Dim emp As Long

>     emp = 10000
>     Set conn = CreateObject("ADODB.Connection")
>     conn.ConnectionTimeout = 0
>     conn.CommandTimeout = 0
>     'This does not insert all records
>         conn.Open "driver={SQL
> Server};server=(local);database=pubs;UID=;Pwd=;"
>     'This works
>         conn.Open "Provider=SQLOLEDB;Data Source=(local);Initial
> Catalog=pubs;UID=sa;Pwd=;"

>     For i = 1 To 500
>     emp = emp + 1
>     sqlStmt = sqlStmt & "INSERT INTO employee  (emp_id,   fname,
> minit," & _
>         "lname ) VALUES  ('AAA" & CStr(emp) & "F','fname','M','LNAME'
> ) " & vbCrLf & vbCrLf

>     Next i

>     conn.Execute sqlStmt

> Why is this behavior? Any help is appreciated as I am not able to find
> soultion anywhere.

> Thanks
> Vikas



Mon, 11 Jul 2005 02:18:15 GMT
 ADO problem with multiple SQL statements using execute method
Hi Val,

I tried with ; at the end of each sql statement in batch but still it
inserts less number of records than expected 500 records. Is this
problem with ADO 2.5? I checked ADO 2.7 but it no more supports this
ODBC Provider (MSDASQL).

Thanks
Vikas

Quote:

> Vikas,

> Not all providers allow to execute multiple SQL statements. Not sure about
> OLEDB for ODBC, but in case of OLEDB fr SQL Server, you need to separate
> those statements by semicolon (;) to execute them in one batch

> --
> Val Mazur
> Microsoft MVP



> > Hi,

> > I am using SQL7 and ADO 2.5. I am trying to pass sql statment
> > consisting 500 or more insert statements. But surprisingly ADO with
> > default ole-db provider for odbc driver insert less number of records.
> > It works fine with sqloledb provider. Here is the code that
> > uses employee table in pubs database.

> > Dim i As Integer
> > Dim sqlStmt As String
> > Dim conn As ADODB.Connection
> > Dim emp As Long

> >     emp = 10000
> >     Set conn = CreateObject("ADODB.Connection")
> >     conn.ConnectionTimeout = 0
> >     conn.CommandTimeout = 0
> >     'This does not insert all records
> >         conn.Open "driver={SQL
> > Server};server=(local);database=pubs;UID=;Pwd=;"
> >     'This works
> >         conn.Open "Provider=SQLOLEDB;Data Source=(local);Initial
> > Catalog=pubs;UID=sa;Pwd=;"

> >     For i = 1 To 500
> >     emp = emp + 1
> >     sqlStmt = sqlStmt & "INSERT INTO employee  (emp_id,   fname,
> > minit," & _
> >         "lname ) VALUES  ('AAA" & CStr(emp) & "F','fname','M','LNAME'
> > ) " & vbCrLf & vbCrLf

> >     Next i

> >     conn.Execute sqlStmt

> > Why is this behavior? Any help is appreciated as I am not able to find
> > soultion anywhere.

> > Thanks
> > Vikas



Mon, 11 Jul 2005 17:21:14 GMT
 ADO problem with multiple SQL statements using execute method
Vikas,

As I wrote in previous posting, capabilities to execute multiple SQL
statements depend on provider, not on ADO. If provider has limitation, then
ADO cannot do anything against that. Probably OLEDB for ODBC has limit on
number of SQL statements in one batch. I would suggest to split your task to
several batches or use OLEDB for SQL Server

--
Val Mazur
Microsoft MVP


Quote:
> Hi Val,

> I tried with ; at the end of each sql statement in batch but still it
> inserts less number of records than expected 500 records. Is this
> problem with ADO 2.5? I checked ADO 2.7 but it no more supports this
> ODBC Provider (MSDASQL).

> Thanks
> Vikas




Quote:
> > Vikas,

> > Not all providers allow to execute multiple SQL statements. Not sure
about
> > OLEDB for ODBC, but in case of OLEDB fr SQL Server, you need to separate
> > those statements by semicolon (;) to execute them in one batch

> > --
> > Val Mazur
> > Microsoft MVP



> > > Hi,

> > > I am using SQL7 and ADO 2.5. I am trying to pass sql statment
> > > consisting 500 or more insert statements. But surprisingly ADO with
> > > default ole-db provider for odbc driver insert less number of records.
> > > It works fine with sqloledb provider. Here is the code that
> > > uses employee table in pubs database.

> > > Dim i As Integer
> > > Dim sqlStmt As String
> > > Dim conn As ADODB.Connection
> > > Dim emp As Long

> > >     emp = 10000
> > >     Set conn = CreateObject("ADODB.Connection")
> > >     conn.ConnectionTimeout = 0
> > >     conn.CommandTimeout = 0
> > >     'This does not insert all records
> > >         conn.Open "driver={SQL
> > > Server};server=(local);database=pubs;UID=;Pwd=;"
> > >     'This works
> > >         conn.Open "Provider=SQLOLEDB;Data Source=(local);Initial
> > > Catalog=pubs;UID=sa;Pwd=;"

> > >     For i = 1 To 500
> > >     emp = emp + 1
> > >     sqlStmt = sqlStmt & "INSERT INTO employee  (emp_id,   fname,
> > > minit," & _
> > >         "lname ) VALUES  ('AAA" & CStr(emp) & "F','fname','M','LNAME'
> > > ) " & vbCrLf & vbCrLf

> > >     Next i

> > >     conn.Execute sqlStmt

> > > Why is this behavior? Any help is appreciated as I am not able to find
> > > soultion anywhere.

> > > Thanks
> > > Vikas



Tue, 12 Jul 2005 20:47:14 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. ADO problem with multiple SQL statements using execute method

2. Executing Stored procedure with multiple select statement using RDO

3. accessing stored procedure return value using ADO (2.0) command execute method

4. ADO problem with multiple SQL statements

5. The Like Statement with the Find Method Using ADO

6. Mystery saving multiple statements at once with execute statement

7. Error when using AddNew method in ADO (using ADO 2.7 SP1 from VB 6 SP4)

8. fill recordset using command.execute method...problems!!??

9. Multiple statements in Execute SQL task

10. Multiple SELECT statements using ODBC & ADO

11. Executing Multiple SQL Statements from VB

12. Selecting XML into an ADO Stream using multiple select statements


 
Powered by phpBB® Forum Software