HOW TO : (ACTUAL) Linked-Server Settings for SQL Server 2008 R2 to Excel 2010 Spreadsheet…

2012-11-09 MV: Anyone trying to actually define a Linked-Server in SQL Server 2008 R2 to an Excel 2010 spreadsheet, I’ll be you’re as frustrated as I was. Surprisingly few links, and what links there WERE kind of ran you around in circles, eventually showing you settings for the JET provider, or describing the ACE provider, but NOT really showing the SETTINGS for the ACE provider. What follows is what worked nicely for me…

STEPS TO CREATING A LINKED-SERVER FROM SQL 2008R2 (64 bit) TO AN EXCEL 2010 SPREADSHEET:

REMOTE (whichever RDP client you prefer) directly into your SQL Server 2008R2 server with an ADMINISTRATOR-level account (you’re downloading and installing stuff);

LAUNCH SQL Server 2008R2 Management Studio

CONNECT to your local SQL Server instance (often connects by default)

NAVIGATE to SQL Server (instance) > Server Objects > Linked Servers > Providers

CHECK if provider “Microsoft.ACE.OLEDB.12.0” provider is already installed

IF (NOT Installed)

  • BROWSE to http://www.microsoft.com/en-us/download/details.aspx?id=13255
  • DOWNLOAD the AccessDatabaseEngine_64.exe Provider Installer to your SQL Server’s hard drive, say C:\Application Installers  is a location I create for such activities – makes it easy to see what “extra” stuff I’ve installed;
  • LAUNCH the AccessDatabaseEngine_64.exe installer
        => the Microsoft.ACE.OLEDB.12.0 provider will now be added to SQL Server’s list of Data Access Providers
  • REFRESH the Providers node in SQL Management Studio (cool how you did NOT need to close/restart the studio!)
       => you will now SEE the Microsoft.ACE.OLEDB.12.0 provider listed with all the other built-in providers

RIGHT-MOUSE-CLICK on Linked Servers > New Linked Server
   => New Linked Server dialogue box displays

TYPE or SELECT the following information:

  • Linked Server (name) = “EXPORT”     (ANYTHING is acceptable, but shorter names are preferred)
  • Server Type = Other Data Source (select this radio button)
  • Provider = Microsoft Office 12 Access Database Engine OLE DB Provider
  • Product Name = Excel
  • Data Source = H:\Data\MySpreadsheet.xlsm  �
       note: you could also use a UNC convention like \\BogusServer123\K$\Shared\MySpreadsheet.xlsm  convention as well
  • Provider String = Excel 12.0 Macro
  • Location = blank (don’t need it)
  • Catalog = blank (don’t need it)

CLICK OK

=> SQL Managment Studio uses the info to create & test a new connecton string, and completes creating the new Linked Server

NAVIGATE into the new Linked Server

NAVIGATE down to a table

RIGHT-MOUSE-CLICK > Script Table > Select to
   => a canned SELECT query is generated

EXECUTE query

   => you’ll see your table contents returned – CONFIRMATION that you now have a working Linked Server!

 

That’s it!

Cheers,

-MV

Leave a Reply

Your email address will not be published. Required fields are marked *