November 16, 2017 at 7:20 am
I have a SQL server 2016 sp1 sitting on a windows 2016 standard 64 bit, and I am trying to get open rowset working as our developers would like to use it. I turned on adhoc distributed queries and I am trying to run the statement below but I keep getting the error listed below. After much googling I can't seem to get this thing to work. Does anyone have any suggestions/ideas?
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=c:\temp\temp.csv;HDR=YES','SELECT * FROM [Sheet1$]');
Msg 7302, Level 16, State 1, Line 14
Cannot create an instance of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
November 16, 2017 at 7:30 am
Have you downloaded the Provider and installed it on the server? That is sometimes an issue.
https://www.microsoft.com/en-us/download/details.aspx?id=13255
Also, is this path the path on the server or your workstation?
November 16, 2017 at 7:34 am
The provider is already installed on the machine. The path that I am referencing is a path on the server. Eventually the goal is to point that to a network location, but I just wanted to get it working locally.
I do think this has to do with 32 vs 64 bit drivers. When I run the statement below I get an error that talks more about that:
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0 XML; HDR=YES;Database=c:\temp\temp.csv','SELECT * FROM [Sheet1$]');
Msg 7438, Level 16, State 1, Line 5
The 32-bit OLE DB provider "Microsoft.ACE.OLEDB.12.0" cannot be loaded in-process on a 64-bit SQL Server.
The machine has 32 bit office installed on it so I am not able to install the 64 bit driver.
November 16, 2017 at 7:42 am
tommiwan - Thursday, November 16, 2017 7:34 AMThe provider is already installed on the machine. The path that I am referencing is a path on the server. Eventually the goal is to point that to a network location, but I just wanted to get it working locally.
And if you navigate in SSMS to the Providers list, it appears there? Sometimes installations of providers don't fully operate correctly because the provider is older and a Windows 2016 server might keep that info in a different part of the registry than where previous OS versions keep such things, and the installer for the provider doesn't use the right registry location because it was written long before Windows Server 2016 was a twinkle in Microsoft's eye.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
November 16, 2017 at 7:42 am
tommiwan - Thursday, November 16, 2017 7:34 AMThe provider is already installed on the machine. The path that I am referencing is a path on the server. Eventually the goal is to point that to a network location, but I just wanted to get it working locally.
You don't happen to have this patch install by any chance do you https://support.microsoft.com/en-us/help/4041688">https://support.microsoft.com/en-us/help/4041688
That patch has broke the JET driver and you need to use the ACE driver until MS fix the problem or uninstall the patch.
November 16, 2017 at 7:45 am
tommiwan - Thursday, November 16, 2017 7:34 AMThe provider is already installed on the machine. The path that I am referencing is a path on the server. Eventually the goal is to point that to a network location, but I just wanted to get it working locally.I do think this has to do with 32 vs 64 bit drivers. When I run the statement below I get an error that talks more about that:
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0 XML; HDR=YES;Database=c:\temp\temp.csv','SELECT * FROM [Sheet1$]');Msg 7438, Level 16, State 1, Line 5
The 32-bit OLE DB provider "Microsoft.ACE.OLEDB.12.0" cannot be loaded in-process on a 64-bit SQL Server.The machine has 32 bit office installed on it so I am not able to install the 64 bit driver.
"In Process", if I remember correctly, is a check box on the Provider properties (in SSMS) that has to be checked for such things to work, but please validate that I'm not just mis-remembering where an "in process" checkbox appears.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
November 16, 2017 at 7:46 am
sgmunson - Thursday, November 16, 2017 7:45 AMtommiwan - Thursday, November 16, 2017 7:34 AMThe provider is already installed on the machine. The path that I am referencing is a path on the server. Eventually the goal is to point that to a network location, but I just wanted to get it working locally.I do think this has to do with 32 vs 64 bit drivers. When I run the statement below I get an error that talks more about that:
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0 XML; HDR=YES;Database=c:\temp\temp.csv','SELECT * FROM [Sheet1$]');Msg 7438, Level 16, State 1, Line 5
The 32-bit OLE DB provider "Microsoft.ACE.OLEDB.12.0" cannot be loaded in-process on a 64-bit SQL Server.The machine has 32 bit office installed on it so I am not able to install the 64 bit driver.
"In Process", if I remember correctly, is a check box on the Provider properties (in SSMS) that has to be checked for such things to work, but please validate that I'm not just mis-remembering where an "in process" checkbox appears.
Actually, it's "Allow inprocess"...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
November 16, 2017 at 8:35 am
Thanks for all the replies thus far. I've gotten a little farther with this but still don't have it working. I was able to remove office and get the 64 bit ACE driver installed. That driver does show under "providers" and I have checked the box to "allow in process" (the 32 bit jet driver was not showing). Now when I run the following statements I receive the errors below. note that the first statement is trying to use a csv file and the second an excel file. I'm not sure why the two file types return a different error.
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0 XML; HDR=YES;Database=c:\temp\temp.csv','SELECT * FROM [Sheet1$]');
Msg 7399, Level 16, State 1, Line 2
The OLE DB provider "Microsoft.ACE.OLEDB.12.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 2
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0 XML; HDR=YES;Database=c:\temp\temp.xlsx','SELECT * FROM [Sheet1$]');
Msg 7399, Level 16, State 1, Line 3
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. Access denied.
Msg 7350, Level 16, State 2, Line 3
Cannot get the column information from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
November 16, 2017 at 9:29 am
I was able to get this working. After getting the proper driver installed I realized that something had locked access to my excel file. Once I cleared that up I was able to select data from the file using open rowset.
November 16, 2017 at 2:45 pm
Thanks for the update. Good to know that checking the file first is probably a good checklist item.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply