Create Excel Linked Server with Microsoft
SQL Server Management Studio

By using Parametrical Store Procedure
 


There are thousands of articles how to create an Excel Link Server in SQL specially in Local environment and not enough on  IIS.

Hope this is useful.

Requirements:

SQL server ver.    >=  2008

Excel                    >= 2007

IIS                         Optional

Provider for SQL server =  Microsoft.ACE.OLEDB.12.0

 

Let’s follow the steps bellow.

 First of all  the    Microsoft Access Database Engine  ver. >= 2010 Redistributable must be installed in our machine.

Open the SSMS.

 

Select Programmability -> Stored Procedures. Create New Stored Procedure.

  Create Procedure

Copy the next lines in the New Procedure body:

USE [master]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

 

CREATE PROCEDURE [dbo].[P_CR_LINK_EXL_SRVR]

         

@PEXCL_SRVR_NAME AS NVARCHAR(MAX),           @PSRVR_NAME AS  NVARCHAR(MAX) , @EXL_FILE_LCTN AS  NVARCHAR(MAX)

         

AS

BEGIN

         

          SET NOCOUNT ON;

 

 

 

EXEC master.dbo.sp_addlinkedserver @server = @PEXCL_SRVR_NAME, @srvproduct=N'Excel', @provider=N'Microsoft.ACE.OLEDB.12.0', @datasrc=@EXL_FILE_LCTN, @provstr=N'Excel 12.0'

 

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=@PEXCL_SRVR_NAME,@useself=N'False',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=@PEXCL_SRVR_NAME,@useself=N'True',@locallogin=@PSRVR_NAME,@rmtuser=NULL,@rmtpassword=NULL

 

 

EXEC master.dbo.sp_serveroption @server=@PEXCL_SRVR_NAME, @optname=N'collation compatible', @optvalue=N'false'

 

 

EXEC master.dbo.sp_serveroption @server=@PEXCL_SRVR_NAME, @optname=N'data access', @optvalue=N'true'

 

 

EXEC master.dbo.sp_serveroption @server=@PEXCL_SRVR_NAME, @optname=N'dist', @optvalue=N'false'

 

 

EXEC master.dbo.sp_serveroption @server=@PEXCL_SRVR_NAME, @optname=N'pub', @optvalue=N'false'

 

 

EXEC master.dbo.sp_serveroption @server=@PEXCL_SRVR_NAME, @optname=N'rpc', @optvalue=N'false'

 

 

EXEC master.dbo.sp_serveroption @server=@PEXCL_SRVR_NAME, @optname=N'rpc out', @optvalue=N'false'

 

 

EXEC master.dbo.sp_serveroption @server=@PEXCL_SRVR_NAME, @optname=N'sub', @optvalue=N'false'

 

 

EXEC master.dbo.sp_serveroption @server=@PEXCL_SRVR_NAME, @optname=N'connect timeout', @optvalue=N'0'

 

 

EXEC master.dbo.sp_serveroption @server=@PEXCL_SRVR_NAME, @optname=N'collation name', @optvalue=null

 

 

EXEC master.dbo.sp_serveroption @server=@PEXCL_SRVR_NAME, @optname=N'lazy schema validation', @optvalue=N'false'

 

 

EXEC master.dbo.sp_serveroption @server=@PEXCL_SRVR_NAME, @optname=N'query timeout', @optvalue=N'0'

 

EXEC master.dbo.sp_serveroption @server=@PEXCL_SRVR_NAME, @optname=N'use remote collation', @optvalue=N'true'

 

EXEC master.dbo.sp_serveroption @server=@PEXCL_SRVR_NAME, @optname=N'remote proc transaction promotion', @optvalue=N'true'

 

END

 

 

Run the Procedure (press F5)

Change the CREATE Command in Procedure body to ALTER

Rerun the Procedure

 

We are almost ready.

Expanding the Linked Servers…  Providers on the Microsoft.ACE.OLEDB.12.0, right click on Properties check the Allow inprocess :

Excel Linked Server

 

 

 

The next steps are to run the Procedure with the Parameters:

The name of Excel Linked Server         :           'EXCEL_DATA_TEST'

The Computer Name and Login  User Name , OR  the Domain Name and Login  User Name for IIS

'DESKTOP-HGUOGQ\DPSYC'    OR    WEKXOW-XX\IWPD_10(sysuser_0)

The Excel File Location                            :           'C:\STS\file_example_XLS_10.xlsx'

In case you use  IIS we have to give Read Rights for the user IWPD.. to folder 'C:\STS

 

 

 Now We Run  in a New Query the Procedure with the next format:

EXEC P_CR_LINK_EXL_SRVR  'EXCEL_DATA_TEST' , 'DESKTOP-HGUOXX\DPSYC' , 'C:\STS\file_example_XLS_10.xlsx'

Of course, you can change your data specs

Don’t miss to include your specs with  ' '

We are Ready

The next is the SQL Command:

SELECT [BusinessEntityID] ,

[FirstName] ,

[MiddleName] ,

[LastName]

FROM [adv_empl]...[Query1$]

GO

 

The Results:

 

We can use the Excel Linked Table Sheets in our Views by this way:

SELECT      *

FROM         EXCEL_DATA_TEST...Sheet1$ AS Sheet1$_1 (3 dots Important !!)

 

We don’t have to use programming code to Update an SQL table with the Excel Data.

Tips: In soucrce Excel file add a first column with related to  unique id code , its very useful and functional.

We can connect to Excel tables or reports from our sql server, wich  gives us the ability of dynamic changes from Excel to Sql link server and vice versa.

Example:

Excel file sheet connected to our SQl srver:

Sql Query  Linked to our Excel file:

 

Thanks for your time.

Dimitrios C. Psychias