2

I have the following code in a SQL Server database, recently we had migrated this database from a Server which is SQL Server 2008 R2 to another server which is SQL Server 2014.

IF EXISTS (SELECT 1 FROM sys.servers WHERE name = 'Excel_File_Src')
    EXEC sp_dropserver 'Excel_File_Src', 'droplogins'

EXEC sp_addlinkedserver 'Excel_File_Src',
        @srvproduct = 'ACE 12.0',
        @provider = 'Microsoft.ACE.OLEDB.12.0',
        @datasrc =  'E:\UCB_Data.xlsx' ,                
        @provstr = 'Excel 8.0;'

EXEC sp_addlinkedsrvlogin 'Excel_File_Src', 'false'

EXEC SP_TABLES_EX 'Excel_File_Src'

New SQL Server version is: 

Microsoft SQL Server 2014 - Microsoft Corporation Developer Edition (64-bit) on Windows

It is used to run perfectly on old server, but after moving it to new server we getting below error.

The 32-bit OLE DB provider "Microsoft.ACE.OLEDB.12.0" cannot be loaded in-process on a 64-bit SQL Server.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user3789961
  • 135
  • 3
  • 12
  • So download the x64 version of the driver https://www.microsoft.com/en-us/download/details.aspx?id=13255 – Alex K. Dec 01 '15 at 15:04

3 Answers3

2

Firstly the message is telling you the office bits you have installed on the box are the 32bit version. You need the 64 bit version. Secondly, make sure you aren't running the "Developer" edition in production because you aren't allowed - see EULA.

Tony Rogerson
  • 579
  • 2
  • 6
  • it is just another DEV server. am i supposed to install MS office 64 bit version on that server or the SQL Server 64 bit. – user3789961 Dec 02 '15 at 07:53
  • That's the issue - you have 64 bit of SQL Server installed (which is the right thing), so you need to install the 64 bit version of Office. – Tony Rogerson Dec 02 '15 at 08:05
  • This article will help: http://blogs.msdn.com/b/farukcelik/archive/2010/06/04/accessing-excel-files-on-a-x64-machine.aspx – Tony Rogerson Dec 02 '15 at 08:11
0

You Could Download Both x64 and x86 and run these commands to install them without conflicting with one another:

"C:\AccessDatabaseEngine_x64.exe" /passive for 64-bit installation.

or

"C:\AccessDatabaseEngine.exe" /passive for 32-bit installation.

Source: "Microsoft.ACE.OLEDB.12.0" cannot be loaded in-process on a 64-bit SQL Server

Poseidon
  • 93
  • 9
-1

Isn't the error self explanatory which saying that 32bit driver can't be used with 64 bit installation. You need a 64-bit driver to solve this issue.

Rahul
  • 76,197
  • 13
  • 71
  • 125
  • 1
    Please provide useful information, not translating the text. this issue is well known – Amirreza Aug 29 '17 at 10:54
  • @Amirreza, that itself explains and indeed useful. what more you expect. – Rahul Aug 29 '17 at 11:10
  • Honestly I except you to add something useful, just search the error and you will see it's a lot of people problem and it needs workaround in some situations, (edit your question (change something) so I could be able to up vote) – Amirreza Aug 29 '17 at 12:15