Linked Server

Hi,

I have created a linked server using Excel file. When I run the query again this linked server, it works fine as I have all kind of authorities. But when user run it gives the following message (Queries are running from ASP.Net Page)

System.Data.SqlClient.SqlException: Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "LPG".

Here is how linked server is configured

EXEC master.dbo.sp_addlinkedserver @.server = N'LPG', @.srvproduct=N'Excel', @.provider=N'Microsoft.Jet.OLEDB.4.0', @.datasrc=N'E:\MyFolder\LPG.xls', @.provstr=N'Excel 8.0'

Please Guide.

Should I have to add a Proxy Account so that Domain User can also access the information from excel file?

Select * from LPG...Sheet1$

Please Guide.

|||

Please make sure you have configured the remote login and password for the provider using sp_addlinkedsrvlogin stored procedure.

The BOL topic

Accessing and Changing Database Data > Distributed Queries > OLE DB Providers Tested with SQL Server > OLE DB Provider for Jet

has more details.

Excerpts from that topic:

To access a nonsecured Access database, SQL Server logins trying to access an Access database should have a login mapping defined to the user name Admin without a password. The following example enables access for the local user Joe to the linked server named Nwind.

|||

I did and still not working. I am working with Microsoft to resolve this issue. They have also duplicated the same issue. Waiting for there response.

Thanks

|||

After getting support help from Microsoft, I have found this solution.

Any Non-sys admin user wants to get the access to linked server (Excel ) must have the Read/Write access to SQL Server Service account's temp folder.

C:\Document and Settings\Administrator\Local Settings\Temp

or

C:\Document and Settings\Administrator.DomainName\Local Settings\Temp

Thanks

Shafiq

|||

Hi,

Temp folder has granted the proper permission but sometime it does not work. So only solution right now is to reboot the server. After rebooting the server it works fine. Any one has any idea why it behaves differenlty?

Error is shown below:

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "LPG" returned message "Unspecified 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 "LPG".