We help IT Professionals succeed at work.

Create linked server to excel sql 2008

I am trying to create a likned serv to excel but everything i try i get errors.
Gave full persmissions to account to directory
I am using the correct path becuase I can cu and paste into browser and get there
I originally installed 32 bit provider but backed it out and installed 64 bit and it shows up and i can pick it.  Attached are my screen shots or errors, I have googled alot and tried many things but nothing seems to get me past these errors.
help.docx
Comment
Watch Question

Commented:
what version of office do you have installed in the system?
Can you past the script you are using to create the linked server. In case you are doing it using GUI, can you generate script and paste it here?

Author

Commented:
TempDBA,
We do not have Office installed on the sql server, on our computers we have office 2007.

If I try this I get the following error.  But I know it has because i restarted and can pick it when I try to create a linked server.

Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.12.0', 'Excel 12.0;Database=\\Servername\autod\Database Imports\Personnel.xlsx;HDR==YES', 'SELECT * FROM [Sheet1$]')

Msg 7403, Level 16, State 1, Line 2
The OLE DB provider "Microsoft.Jet.OLEDB.12.0" has not been registered.


I use the GUi but here is the info I put in
Linked Server: Personnel
Provider: Microsoft Office 12.0 Access Database Engine OLEDb provider
Product name:Excel
Datasource:\\Servername\autod\Database Imports\Personnel.xlsx
Provider string:Excel 12.0

Commented:
I think the jet.oledb.12.0 is a typo

It should be something like
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=C:\Documents\reports\abc123.xlsx', 'Sheet1$')

Author

Commented:
Hi,

Ok next error, i am guessing this has something to do with permissions but I am logged in and have Full Permissions to the folder.  Any more ideas about the linked server instead as this would be preferable.

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

Author

Commented:
TempDBA:,
Everythign I look up on this error says that the file is open, its not open and I tried to import another file from another location and got the same thing.
Commented:
Does the account on which sql server runs also has permissions on the folder containing your excel sheet?

Here is a wonderful link I wanted to share which will help you how to debug the issue. though it is with jet 4.0, but the reason must be same.:-
http://blogs.msdn.com/b/spike/archive/2008/07/23/ole-db-provider-microsoft-jet-oledb-4-0-for-linked-server-null-returned-message-unspecified-error.aspx

Can you also try running the below script:-

USE [master]
GO

EXEC dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
GO

EXEC dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
GO

Author

Commented:
TempDBA:,

Ok ran than same script yesterday but ran agian to make sure.  I added the permission for full control to my account I am connecting to the sheet with and the sql account I am using as well.  Both still got me the same error.  Something that should be so easy MS makes so darn hard.

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

Author

Commented:
TempDBA:,
Ok I  added Authenticated users, domain users, users to this folder and still get the smae error.  i logged onto sql sever under my admin provledges and ran it and it worked once but then i keep getting the same error above.  But now when I try to add a linked server I get the following.

the OLe Db provider reported error authentication failed cannot initalize linked server, cannot start your application. the following workgroup file is missing or opned exclusively by another user.  (MS error 7399)

Author

Commented:
TempDBA:,
Ok for some reason if I log ontot he sql server with admin rights, i was able to create the linked server and do a query from here, but if I log on with my account remotely its a no go, not even if I start SSMS with admin privledges and query this way will it work, I have to be on the sql server to get it to work.

Author

Commented:
Ok its working if I log onto server and I created jobs for it which I can run remotely so its working but only when logged on.  Later if I have MS tickets to use before year end I will have to waste one on finishing this up.  thanks for the help.