LinkedServer for Excel Worksheet

• luissantos

  SSCertifiable

  Points: 7391

  More actions

  November 12, 2012 at 5:09 am

  #264620

  Hello comunity

  I need to install the package "Microsoft Access Database Engine 2010 Redistributable" with the

  Microsoft.ACE.OLEDB.12.0 on My SQl server 2005 STD X64 version (Microsoft SQL Server 2005 - 9.00.5000.00 (X64) Dec 10 2010 10:38:40 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1) ), but i have already a LinkedServer that use "Microsoft OLEDB for SQL Server".

  I wonder if installing this new package, if I can have problems in my current linkedserver ??

  Many thanks

  Luis Santos

• Lowell

  SSC Guru

  Points: 323518

  More actions

  November 12, 2012 at 5:15 am

  #1558111

  that package adds the ACE drivers, which are not the same as the OleDB.

  it's just like adding the drivers for MYSQL, SQLite or any of the many ODBC drivers for different connections.

  once it's there, you'd be able to use it.

  as an FYI, because I've had to do this a lot, here's some notes I wrote and kept about the Excel linked server:

  the ACE drivers can be downloaded here:

  http://www.microsoft.com/en-us/download/details.aspx?id=13255

  make sure you run the install with the c:\Downloads\AccessDatabaseEngine_x64.exe /passive

  command line flag;

  this will force the install of the drivers, even if you have 32 bit office installed;

  otherwise you get some error about 32 bit Office preventing the install.

  After that is isntalled:

  --Required settings for the provider to work correctly as a linked server

  EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1

  GO

  EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1

  and then the code for the linked server:

  --#################################################################################################

  --Linked server Syntax for Excel with ACE 64 driver

  --#################################################################################################

  DECLARE

  @srvproduct nvarchar(256),

  @provider nvarchar(256),

  @location nvarchar(100),

  @provstr nvarchar(100),

  @catalog sysname,

  @sql varchar(1000)

  --add an excel spreadsheet as a linked server.

  SET @server = 'MyExcelACE'

  SET @srvproduct = ''

  SET @provider = 'Microsoft.ACE.OLEDB.12.0'

  set @provstr = 'Excel 12.0'

  SET @datasrc ='C:\Data\BlockGroups_2010\AKblockgroup.xls'

  EXEC sp_addlinkedserver @server,@srvproduct,@provider,@datasrc,NULL,@provstr

  EXEC dbo.sp_AddLinkedSrvLogin @server, FALSE, NULL, Admin, NULL

  --what spreadsheets (table equivilents are available?

  EXEC sp_tables_ex 'MyExcelACE'

  --you MUST know the name of the spreadsheet;

  --spreadsheet name has a dollar sign at the end of it!

  --I've personally never gor a spreadsheet that has a space in it

  --for example "Activity Data" =ActivityData$ never got [Activity Data$] or [Activity Data]$ to work

  --to work, so I end up editing the spreadsheet to remove spaces if that happens.

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• luissantos

  SSCertifiable

  Points: 7391

  More actions

  November 12, 2012 at 5:24 am

  #1558119

  Hello Lowell

  Thanks for your reply that is very good, simply and clear.

  Best regards,

  Luis Santos