Re: Does Bulk Insert require different permission to a SQL Sever database?


"Pradeep" <pradeepvarmaj@xxxxxxxxx> wrote in message
news:1157955666.698020.147420@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Hi,

I am trying to transfer data from Excel to a table in SQL Server. Hence
created two connection objects. They are:

Set objConnTest = New ADODB.Connection

With objConnSQL
.Provider = "SQLOLEDB"
.ConnectionString = "Data Source=XYZ;Initial Catalog=Data;User
Id=sa;Password=data;"
.Open
End With

With this it is succesfully connecting to SQL Server.

The other connection object which connects successfully to the Excel
file is:

With objConnExcel
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data source=" & App.Path
&"\Test.xls;Extended
Properties=Excel 8.0;"
.Open
End With

I am able to fetch data from the Excel file with this connection.

Iam able to fetch records one by one from Excel and insert it into the
SQL Server table. But if it comes up with the error "Cannot update.
Database or object is
read-only" when i execute the following statement.

strSQL = "Insert Into [" & objConnSQL & "].[TabEmp] Select * From
[Sheet1$]"

Does Bulk Insert require different permission to a SQL Sever database?

Set based inserts are not necessarily bulk inserts; your problem is that
Sheet1$ is not a valid table-valued object in the context of the connection
to SQL Server. You could define a linked server in SQL Server to reference
the Excel files. Otherwise, use DTS.


-Mark



Please suggest.

Thanks,

Pradeep



.