none
Issue with SMSS 2017 and OLE DB provider

    Question

  • Hello,

    I have installed SMSS 2017 on a new laptop, and have tried to create my first database table from an Excel file. I am using windows authentication for my access.

    This is the T-SQL script that has been used

    =============================

    SELECT * 
    INTO NOMI_RAW_1914
    FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Excel 8.0; Database=F:\F\Lost Location\Website\Data\roll_1914.xls; HDR=YES; IMEX=1',
    'SELECT * FROM [NOMI2$]') 
    GO

    SELECT * FROM NOMI_RAW_1914
    GO

    =============================

    This is the error message I keep getting:

    OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified 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)".

    It appears there is some setup issue whereby the downloaded AccessDatabaseEngine_X64.exe file is not integrating with SMSS 2017.

    I have seen references to accessing the SQL Server Configuration Manager but it not apparent how to do this with SMSS 2017, unlike SQL Server 2008 and prior versions.

    Please can someone help me to get started.

    Kind regards

    Keith_H99

    Thursday, August 17, 2017 10:10 AM

All replies

  • I get the following error message when opening the file SQLServerManager14.msc 


    Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 and later serviers with SQL Server Configuration Manager. Invalid namespace [0x8004100e]
    Thursday, August 17, 2017 10:38 AM
  • It's a little beside the issue itself, but it is still worth pointing out: there is no integration between the ACE provider and SSMS. SSMS is just a query tool that connects to SQL Server. The connection is between SQL Server and the OLE DB provider.

    It is not clear whether you are connecting to an SQL Server instance running on your new laptop, or if you are connecting to an SQL Server elsewhere. But the error message you get from SQLServerManager14.msc indicates that you don't have any local SQL Server instance installed.

    In such case, troubleshooting needs to be performed on the machine where SQL Server is installed.

    Thursday, August 17, 2017 10:49 AM
  • Hi, thanks for the reply.

    SQL Server is installed on my new machine. If I had not been able to see server type = database server and access the interface in the same way as I had done in the past. It should be that the SQL Server instance should be running locally. Everything is to be contained within my laptop, with no external connections.

    I have been able to run the query on my old machine (XP which has SQL Server 2008 Express).

    Googling of the term "SQL Server instance installed" would imply that this would not have necessarily auto-installed when I installed the SQL Server 2016 & SSMS 2017 bundle. Is there a means by which I can check to see if this has been installed/initiated?

    Kind regards

    Keith_H99


    Thursday, August 17, 2017 1:43 PM
  • I have used the System Configuration Checker, and there is a test which has failed:

    TITLE: SQL Server Setup failure.
    ------------------------------

    SQL Server Setup has encountered the following error:

    The value 'SqlUnsupportedProductBlocker' is an invalid rule ID or an invald rule group ID for the input parameter 'RULES'.

    Error code 0x84B40002.

    ------------------------------

    There was a URL but that took me to a generic Microsoft page, so I am now unsure how to proceed. I am puzzled as to why it was a SQL Server 2008 System Configuration Checker rather than a version oriented to the 2016 version.

    Thursday, August 17, 2017 3:40 PM
  • I went through the following tutorial, to ensure that both SQL Server 2016 and SMSS 2017 were both loaded and configured

    The link can't be posted, but the tutorial was made by Wise Owl

    I found this useful and straightforward.

    Thursday, August 17, 2017 5:50 PM
  • Hi Keith_H99,

    I have voted your post, then you can share a link here. Could you please share the link of the tutorial?

    Best Regards,

    Teige


    MSDN Community Support<br/> Please remember to click &quot;Mark as Answer&quot; the responses that resolved your issue, and to click &quot;Unmark as Answer&quot; if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact <a href="mailto:MSDNFSF@microsoft.com">MSDNFSF@microsoft.com</a>.

    Tuesday, August 22, 2017 7:25 AM
    Moderator
  • Hi Teige,

    Thanks for getting in touch. Unfortunately I have a further problem with SQL Server.

    Double unfortunately, I get the following error message when trying to post the URL on here:

    social.msdn.microsoft.com says:

    Body text cannot contain images or links until we are able to verify your account.

    Best regards,

    Keith

    Wednesday, August 23, 2017 10:19 AM
  • Hi Keith_H99,

    In common scenarios, when I voted your case, you should be albe to contain pictures and link in your reply. Please send anything under the case provided by Erland, then you can send a reply with link.

    >>Unfortunately I have a further problem with SQL Server.

    Which problem did you meet?

    Best Regards,
    Teige

    MSDN Community Support<br/> Please remember to click &quot;Mark as Answer&quot; the responses that resolved your issue, and to click &quot;Unmark as Answer&quot; if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact <a href="mailto:MSDNFSF@microsoft.com">MSDNFSF@microsoft.com</a>.

    Thursday, August 24, 2017 9:57 AM
    Moderator
  • >>Unfortunately I have a further problem with SQL Server.

    Which problem did you meet?

    Best Regards,
    Teige

    Hi Teige,

    The story so far

    Part One

    A friend who has a background in tech support helped to set me up with SQL Server 2017, or so I thought. He named the SQL Server instance "MSSQLSERVER". As this did not seem to work, I set up my own SQL Server instance named SQL2016. ( I seem to have SQL Server 2008, 2016 and 2017 all installed on my machine.)

    I set up my instance by watching some useful tutorials, a link to which can now be posted: 

    SQL Server 2016 Part 1 - Getting Started and Installing SQL Server 2016 Developer Edition

    https://www.youtube.com/watch?v=dspNtyemezo

    Part Two

    This second instance seemed to do the trick. I was able to create some tables, and write some queries.

    There was one issue, though. I was not able to use ACE as the OLE DB. I was able to get around it by using Jet instead, though, to import Excel. 

    Now that this does not work, it reminds me that I need to get the ACE setup fixed, but I do not how how to proceed.

    When trying to use a script to load, this is the resultant error message:

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

    Similarly, when trying to use the import wizard, the following messages are returned when selecting the options for Excel 2007-2010 and Excel 2016 respectively.

    The 'Microsoft.ACE.OLEDB.16.0' provider is not registered on the local machine
    
    The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine

    If anyone else out there, like me, has not properly installed ACE and has been dependent upon Jet to populate tables, they too are in the predicament of having a database tool that cannot populate tables. 

    I would really like to get this resolved. It is so frustrating of having things I want to do, but having hit a brick wall.

    Monday, October 23, 2017 9:45 AM
  • I have been looking at a lot of videos online, which are more oriented towards VB users, and they usually result in changing a setting.

    I am not sure of the significance, but I did see the following video, where a search for "import" is performed, and it lists - for him - SQL Server 2016 Import and Export Data (64-bit) as an app. He then clicks on the link.

    There is now something very peculiar.

    I can now use the wizard to import the Excel file. I select Excel "2007-2010" thereby using the ACE12 driver and it works. If I try to use a script, the script fails.

    When I performed search, and clicked on SQL Server 2016 Import and Export Data (64-bit), it initiated the import wizard, and I was able to select Excel "2016" thereby using the ACE16 driver. Yet when I tried to repeat this by the menu options, it did not work.

    A further attempt to use the script with ACE16 failed too.

    This is very frustrating.

     
    Monday, October 23, 2017 11:26 AM