November 3, 2011 at 8:18 am
I am trying to create a linked server to my excel spreadsheet but now I am down to a final error after clearing a bunch before.
What have I done.
I allowed inprocess for Openrowset
I added the sql account and the account I am connecting with through linked server to the directory and given full control
I added the 64 bit MS office data components to server.
Made sure the file is closed and open by noone.
When I try to connect via GUI on linked server I get a
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)".
This is the same error I get when trying to connect via openrowset as well.
At this point I am just lost because nothing has helped but to produce more errors.
November 3, 2011 at 9:16 am
Stubby Bunny (11/3/2011)
I am trying to create a linked server to my excel spreadsheet but now I am down to a final error after clearing a bunch before.What have I done.
I allowed inprocess for Openrowset
I added the sql account and the account I am connecting with through linked server to the directory and given full control
I added the 64 bit MS office data components to server.
Made sure the file is closed and open by noone.
When I try to connect via GUI on linked server I get a
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)".
This is the same error I get when trying to connect via openrowset as well.
At this point I am just lost because nothing has helped but to produce more errors.
Please place your query so that we can see what you are doing. Also, is the file saved as .xlsx or .xls?
Thanks,
Jared
Jared
CE - Microsoft
November 3, 2011 at 9:22 am
It is saved as an xlsx file. here is what I am doing. i found an artilce on the temp directory issue for adding users for read, write on the service account temp directory and I did this and I got it to work once and since then i get error above now agian and when i try to add a linked server i get a different error about
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)
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=C\\servername\autod\database imports\Personnel.xlsx, 'Select * from [Sheet1$]')
Wehn I try to add the linked server I get the error above.
November 3, 2011 at 9:29 am
What are you trying to add as a linked server?
Jared
Jared
CE - Microsoft
November 3, 2011 at 9:31 am
Stubby Bunny (11/3/2011)
It is saved as an xlsx file. here is what I am doing. i found an artilce on the temp directory issue for adding users for read, write on the service account temp directory and I did this and I got it to work once and since then i get error above now agian and when i try to add a linked server i get a different error aboutthe 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)
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=C\\servername\autod\database imports\Personnel.xlsx, 'Select * from [Sheet1$]')
Wehn I try to add the linked server I get the error above.
The excel file should be on a mounted drive. I don't believe you can use \\servername. If the excel file is on another server, you have to mount that share as a specific drive letter and reference that.
Jared
Jared
CE - Microsoft
November 3, 2011 at 9:34 am
I am trying to add that Excel spreadsheet, I logged on to the sql server and opened SSMS from here with my admin privledges and was able to create the linked server. Don't know what I did differently than before, but now the problem is that when I log in remotely via my destop, I am not able to query. it gives me an error of
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "AIRPERSONNEL" 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.ACE.OLEDB.12.0" for linked server "AIRPERSONNEL".
Don't get this I can log onto the sql server under all my admin and create the server and query it but when i log on with my user account it does not work end gives me errors. I gave the sql account full access to the file and folder as well as my account.
November 3, 2011 at 10:01 am
Stubby Bunny (11/3/2011)
I am trying to add that Excel spreadsheet, I logged on to the sql server and opened SSMS from here with my admin privledges and was able to create the linked server. Don't know what I did differently than before, but now the problem is that when I log in remotely via my destop, I am not able to query. it gives me an error ofMsg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "AIRPERSONNEL" 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.ACE.OLEDB.12.0" for linked server "AIRPERSONNEL".
Don't get this I can log onto the sql server under all my admin and create the server and query it but when i log on with my user account it does not work end gives me errors. I gave the sql account full access to the file and folder as well as my account.
Can you script out that linked server and post it here? I am thinking that it is referencing a drive that disconnects when you are not directly logged in.
Thanks,
Jared
Jared
CE - Microsoft
November 3, 2011 at 10:04 am
/****** Object: LinkedServer [AIRPERSONNEL] Script Date: 11/03/2011 11:04:03 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'AIRPERSONNEL', @srvproduct=N'Excel', @provider=N'Microsoft.ACE.OLEDB.12.0', @datasrc=N'\gmnc2-j6ops-a7\autod\Database Imports\Personnel.xlsx', @provstr=N'Excel 12.0'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'AIRPERSONNEL',@useself=N'False',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
GO
EXEC master.dbo.sp_serveroption @server=N'AIRPERSONNEL', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'AIRPERSONNEL', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'AIRPERSONNEL', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'AIRPERSONNEL', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'AIRPERSONNEL', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'AIRPERSONNEL', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'AIRPERSONNEL', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'AIRPERSONNEL', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'AIRPERSONNEL', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'AIRPERSONNEL', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'AIRPERSONNEL', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'AIRPERSONNEL', @optname=N'use remote collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'AIRPERSONNEL', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO
November 3, 2011 at 10:11 am
Ok... This "@datasrc=N'\gmnc2-j6ops-a7\autod\Database Imports\Personnel.xlsx'" What is the drive letter? This should be a mounted or local drive.
Thanks,
Jared
Jared
CE - Microsoft
November 3, 2011 at 10:18 am
It is shared drive name of our d drive. All permissions have been given to it. It works from the sql server itself. i logged in my my admin permissions and created the linked server and it took, I then created a job and did an import and scheduled it and it worked. but when I log in remotely with SSMS with my non privledged account I cannot query the linked server?? the funny thing is my non-privledged account to the development server has admin rights and it has asdmin rights to the folder I am using.
November 3, 2011 at 10:30 am
Are you executing a stored procedure for this or simply querying? Also, can you query other linked servers? Maybe it is your user's permissions. If you log in using SSMS as sa, does it work?
Jared
Jared
CE - Microsoft
November 3, 2011 at 10:36 am
I can create linked servers with my regular login accoutn to all my sql servers, oracle servers and query them jsut fine, its just excel spreedsheets and access databases that are giving me the trouble. If I log onto my sql server with admin I can create the linked servers and the jobs, but when trying to query or run the jobs remotely, its a no go. It has to be something further with how permissions work, but I am unable to pintpoint it , i have given permissions to all autheticated users, local users, admin accounts and it still will not work remotely.
November 3, 2011 at 10:44 am
Just so I have all of my ducks in a row: The file is local to the SQL Server, you are logging in using SSMS from your client machine, executing a query, and getting the error. Is this correct?
Thanks,
Jared
Jared
CE - Microsoft
November 3, 2011 at 10:58 am
hi,
negative, the file is on our web server on a shared drive where personnel upload files along with programs, we don't allow any access to sql servers other than through asp programs.
I am logging in remotely via SSMS and I cannot query even if I run as admin, but if i log onto sql server adn open SSMS locally I can create the linked server and query it from here and create a job. Now I can execute the job remotely which will work, which for right now is a stop gap measure to put job on daily schedule.
November 3, 2011 at 11:04 am
The problem is that that shared drive disappears when you are no longer logged into the server. It has to be mapped to a drive letter and set up to map every time the server is restarted.
Jared
Jared
CE - Microsoft
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply