Installing new Provider 1

[grnzbra]

Our databases were just moved to a new server. Prior to the move, the following script worked fine:

EXEC master.dbo.sp_addlinkedserver
    @server =N'CMMSSrc',
    @srvproduct=N'EXCEL', 
    @provider =N'Microsoft.ACE.OLEDB.12.0',
    @datasrc = @fileName,
    @provstr =N'Excel 12.0;IMEX=1;HDR=YES;'

After the move, it wouldn't work and when I checked the providers, I found that Microsoft.ACE.OLEDB.12.0 was missing. I spoke the manager who moved the database and he said that he had done nothing with the providers. At my request, the tech people installed the missing provider, but the code still didn't work; it appears to not see the provider. On both the old server and the new server, we were using Excel 2016 and SQL Server 2017.

Is there something else that needs to be done so that SQL Server "sees" the provider? I've checked the web and the only things I see are to set provider options (Dynamic Parameter and Allow Inprocess). What have we missed?

I just found out that our SQL Server is 64 bit and Office is 32 bit and that Mircosoft.ACE.OLEDB.12.0 is 64 bit and that the 32 bit version would not install into a 64 bit SQL Server. Could this be the problem?

By the way, by creating an SSIS package, I am able to bring the data in from Excel. For that we have:
Source Provider: Microsoft.Ace.OLEDB.16.0
Destination Provider: SQLOLEDB
Unfortunately, we need to use a linked server arrangement.

 

[fredericofonseca]

Seems like you installed Excel on the server. That is not supported and is breaking lots of licensing terms, and most likely you are also failing to pay required licenses for any user accessing that SQL Server machine.

As your SQL Server is 64 Bit you need to install the 64 Bit ACE Driver (2016 version, not the old 2010 version).

If you still wish to install Excel on that server you will need to install the 64 bit version of it.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[grnzbra]

Thanks for the suggestions. I'll pass them on to my tech people.
By the way, I didn't install anything; I'm just working with what's there.