Error while reading file from Network Path using openrowset.Plese help.Its urgent

Question

I am trying to do bulk import using OPENROWSET
Here is my code
Excel 8.0;Database=PDF Attachment\format_images1.xls

which works fine in local system

but when i try to read file from network path i get a error

Here is my code
Excel 8.0;Database=\\computerName\PDF Attachment\format_images1.xls

insert INTO Temp_TS_Contract_Document_Staging
SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=\\computername\PDF Attachment\format_images1.xls','SELECT * FROM [Sheet1$]')

"The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)""

PDF Attachment is a shared folder in my machine which contains the required file to import.


I think it is access related issue.
I would like to know what are the permissions we need to give to the folder on the file server and the SQL server so that
the SQL server will access the file from network path using Openrowset and do the bulk import.

also i would like o know can i use ip address of the file server instead of the computer name?

Also getting the error while trying the query from Sql server query browser.

fMsg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

Thanks in advance.

 

---

ARoyDas

Answer 1

hi,

the SQL Server process must have access to your network share. So you need to add the SQL Server Windows Service account to the permissions of that share.

---

Microsoft MVP Office Access
https://mvp.support.microsoft.com/profile/Stefan.Hoffmann

Answer 2

we are using sql express and I found from SQL Server Configuration Manager that "Log on AS" "NT AUTHORITY\NetworkService".

Should we add  this user "NetworkService" in that  share?

Could you please assist.

Actually I don't have too much of knowledge on SQL Server..

---

ARoyDas

Answer 3

hi,

you should create a new Windows account for your SQL Server Windows service and assign the appropriate rights and priveleges:

http://msdn.microsoft.com/en-us/library/ms143504.aspx

---

Microsoft MVP Office Access
https://mvp.support.microsoft.com/profile/Stefan.Hoffmann

Answer 4

Hi Stefan,

 Could you please inform me the steps.

Thanks in advance.

Answer 5

could you pls refer any link which will help to crate a Windows account for your SQL Server Windows service?

---

ARoyDas