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
