Linking Excel with SQL Server

Description. SQL Server may be used to implement OpI applications (Operational Intelligence - generally databases providing high-detail operational data requiring relatively small databases and available to a defined population within an organization) such as are described on this site. SQL Server is extensible and scalable, and commonly runs on a shared server supporting multi-functional and distributed organizations. SQL Server is more complex to set up for OpI use than is MS Access. SQL Server usually resides on a shared server requiring group/user access control and security. SQL Server also requires installation of optional components listed below, to support integration of Excel.

Architecture. The same "Low-Code" architecture described on this site to connect Excel and Access, can be used to incorporate SQL Server with Excel. Limited background or training in coding are needed, to set up use of this methodology and toolset; none to use it once the system is set up. Data contained in Excel tables can be linked into the SQL Server environment and used there in QUERYs, which can themselves be used back in Excel to drive Pivot Table reports, Pivot Charts, or to return a Table for further use in Excel projects.

Reasons to use SQL Server rather than Microsoft Access include

• SQL Server running on a commonly-accessible server allows non-Windows clients to refresh Pivot Table reports driven from queries executed by the SQL Server database.

  In contrast, although MS Access files may reside on a shared server or on a user's system, Access requires Windows on the system it runs on which is likely to be the client desktop, thereby limiting refresh from non-Windows systems. There are workarounds in this situation. A VM may be used to provide Windows on a client system, or client RDP access to a shared Windows computer may be used. Users requiring only read-only reports not requiring refresh and re-QUERY will not run Access so have more flexibility. Use of files available to MS Access over a network is via Microsoft filesharing, using CIFS or SMB. Use of Access is simpler than use of SQL Server, but support is more likely a responsibility of its users, including support of the noted accessibility workarounds. Fortunately experience shows that this is not a significant burden.

• SQL Server provides Transact-SQL (T-SQL) and may be programmable in SQL from client systems. T-SQL implements ANSI SQL with non-standard extensions and variances targeted to data retrieval and set manipulation. T-SQL provides SQL programmability including constructs for IF, and conditional looping WHILE. SQL Server is not itself integrated VBA, although it may of course be accessed from clients using VB or VBA via ADODB, or more simply via Excel Data Connection.

  In contrast, MS Access provides ANSI SQL with fewer extensions. Access is more closely integrated into Office, supporting programmability via Visual Basic integrated into its environment. MS Access also supports User Interface design using Forms. This capability is not used in applications of the type described by this website, but may be useful for other applications on the user's desktop.

• SQL Server is scalable in both software and hardware. SQL Server software supports large databases with many client connections. It commonly runs on a shared server, which is itself scalable in CPU type and CPU sockets, MIPS, DRAM, storage capacity and performance, network configuration and performance, and software performance tuning. Such installations are usually supported by IT.

  For smaller organizations or sub-groups, and for development, SQL Server can also run on a desktop system or smaller-scale shared computer. The Express edition is free, and usable for applications up to 10GB.

SQL Server: Connect SQL Server to Excel to Link Input From Excel Tables

The technique described here involves use of a "Linked Server" in SQL Server that is instantiated and connected to each Excel Workbook containing tables used as data sources. Subsequent QUERY involving Excel tables and worksheets is accomplished through each Linked Server. Each Excel table to be queried must be on a separate Worksheet within the Workbook.

Alternatively, OPENROWSET may be used in SQL Server to read Excel tables. Also see this stackoverflow article which shows use of both a Linked Server and OPENROWSET. You must follow the installation and configuration Steps just below to make either of these work. SQL code for using OPENROWSET is given below.

Steps Involved (updated 2020 April 23)

• References
  ASPSnippets re: Microsoft.Ace.OLEDB.12.0 for Linked Server provides key advice to set up working integration with Excel.
  Stackoverflow re: OLEDB Provider.
  IF EXISTS Test for Linked Server (StackOverflow).
  DROP IF EXISTS Test, Microsoft Docs, includes list of applicable objects.
  IF EXISTS constructs for more purposes, writeup by SQLShack.
  SQL Server Remote Access Settings.
  Import data from Excel to SQL Server or Azure SQL Database - Useful overview: Sept 2019 Microsoft SQL Docs.

• Install SQL Server and SQL Server Management Studio (SSMS), or just SSMS if SQL Server is already available to you. SQL Server Express edition is free, and usable for applications up to 10GB. Tools including SSMS, and connectors including ODBC are available from the SQL Server page. SQL Server runs on Windows or Linux, or in a VM on Azure or Docker. SSMS runs on Windows. Power OpI projects are compatible with SQL Server or SQL Server Express, 32-bit or 64-bit with 32-bit or 64-bit MS Office or Office 365. Note that SQL Server 2019 supports only an x64 version. See note on bitness shortly below.

  Tool	Download
  SQL Server Express 2019.	x64 (64-bit) only
  SQL Server Management Studio SSMS v18.5.	x86 (32-bit) only
  SQL Server Data Tools SSDT for Visual Studio 2019.	Install using VS Installer
  Visual Studio Downloads. Community, Professional and Enterprise editions available.	Visual Studio Community Edition

  SQL Server Management Studio SSMS x86 runs also on x64 systems; the application is 32-bit just as SQL Server or Microsoft 365/Office 365 could be 32-bit installations on x86 or x64 Operating Systems.

• Debugging SQL Note that SQL Server Management Studio 2019 deprecates and removes DEBUG capability such as single-step execution through a QUERY or script. DEBUG can nevertheless be accomplished using MS Visual Studio by adding SQL Server Data Tools SSDT, linking and opening a SQL Server database via Visual Studio SQL Server Object Explorer. From that Object Explorer, stored procedures can be viewed, updated, executed and single-stepped via right-click. Files containing QUERYs can also be opened, viewed, modified, debugged, and executed using breakpoints, single-step and Locals pane displaying values.

  Install SQL Server Data Tools (SSDT)

  SQL debug in Visual Studio is based on SQL Server Data Tools (SSDT).

  SSDT Installation is from Visual Studio Installer > select VS edition > Modify > Data storage and processing workload > SQL Server Data Tools. Click the Install button at bottom right.

  Configuration and Security

  Perform debugging on a sandbox server not a production server.

  Set up debug-intended SQLServerLogins as members of SQL Server fixed role sysadmin.

  Using a SQL Server login that is already a member of fixed role sysadmin, such as a SQL Server System Administrator login, add the Windows account that Visual Studio is running under (Windows Domain\Name) as a member of SQL Server sysadmin fixed server role. Several ways to do that:

  • Execute sp_addsrvrolemember [WinDomain\Name], [sysadmin];
  • or execute ALTER SERVER ROLE [sysAdmin] ADD MEMBER [WinDomain\Name];
  • or use the SSMS user interface Server > Logins > choose WinDomain\Name' > Properties > Server Roles in Login Properties pane > and checkmark public and sysadmin.

  Using a SQL Server login that is already a member of fixed role sysadmin, such as a SQL Server System Administrator login, add the intended SQLServer LoginName (Windows Domain\Name) or SQLServerLoginName to SQL Server sysadmin fixed server role. Several ways to do that:

  • Execute sp_addsrvrolemember [WinDomain\Name], [sysadmin];
    or sp_addsrvrolemember [SQLServerLoginName], [sysadmin];
  • or execute ALTER SERVER ROLE [sysAdmin] ADD MEMBER [WinDomain\Name];
    or ALTER SERVER ROLE [sysAdmin] ADD MEMBER [SQLServerLoginName];
  • or use the SSMS user interface Server > Logins > choose SQLServerLoginName > Properties > Server Roles in Login Properties pane > and checkmark public and sysadmin.

  Set up debug-intended SQLServerLogins by executing
  GRANT EXECUTE ON [sys.sp_enable_sql_debug] TO [WinDomain\Name];
  or GRANT EXECUTE ON [sys.sp_enable_sql_debug] TO [SQLServerLoginName];

  Reference
  Debugging Transact-SQL
  How to: Enable Transact-SQL Debugging Error: User Could Not Execute Stored Procedure sp_enable_sql_debug (encountered when setting EXECUTE permission).

  Debug in Visual Studio

  In Visual Studio, open SQL file or SP containing the Query to debug.

  Connect this QUERY to the intended database. This can be done several ways, but one is: right-click the open QUERY window in Visual Studio > Connection > Connect... > set Encrypt to Optional and Trust Server Certificate to True > Choose Server in Local or Network or Azure (to location of database) > Choose Windows Authentication or SQL Server Login, SQLServerLoginName, and Password (if needed) > Choose Database > Click Connect button.

  Optionally set breakpoint by clicking in left margin on line to break on > in Query Window.

  In top bar of open Query window, at upper left choose Execute with Debugger (dropdown from Execute).

  Now you can use the Debug Toolbar step buttons (Single-Step, Step Over, Step Out, Stop), or Standard Toolbar Continue button. The Locals/Auto/Watch window opens below the QUERY window and displays updating variable values.

  The Debug bar Stop button stops execution leaving the QUERY connected. Right-click > Connection > Disconnect All Queries to exit debug session.

  
  

• Connection using OLEDB SQL Server 2019 includes numerous OLEDB Providers, including MSOLEDBSQL, SQLOLEDB, Microsoft.ACE.OLEDB.12.0 and Microsoft.ACE.OLEDB.16.0 all mentioned in these paragraphs. You can install later-version OLEDB Driver for SQL Server (MSOLEDBSQL) with SQL Server. Prior version OLEDB Provider for SQL Server (SQLOLEDB) is deprecated, not supported, and only available now from third-party sites.
  ref MSDN.Microsoft.com Blog.

  These show up using SSMS as Providers in <SQL Server instance name> > Server Objects > Linked Servers > Providers and become available as Providers when setting up a Linked Server via SQL or in the SSMS User Interface.

  Name	Version	Provider	Download
  Microsoft OLEDB Driver for SQL Server Use as provider in Data Connection between - Excel (or other Database-Client), and SQL Server. - Sample Connection String shown below.	18	MSOLEDBSQL	x64 (64-bit)
  			x86 (32-bit)
  		Match x64 / x86 bitness of Microsoft 365 Office Installed
  Microsoft Access Database Engine 2016 Redistributable Use as provider to Data Connection in Excel as database-client, from - File-based data source including Microsoft Access: .accdb, Excel: .xlsx, .xlsm, text: .txt . - Sample Connection String shown below. Use as provider to SQL Server from file-based data source (.xlsx, .xlsm, .txt, .accdb). - specified by QUERY from client (including Excel via Data Connection) or from SQL Stored Procedure (SP), that uses OPENROWSET QUERY through SQL Server. - specified by QUERY from client or SP, that uses a Linked Server (LS) in SQL Server.	12.0	Microsoft.ACE.OLEDB.12.0	x86 (32-bit)
  			x64 (64-bit)
  		Optionally install Microsoft Data Access Components (DAC) x86 only

  Bitness of the OLEDB Driver for SQL Server installed must match bitness of the Microsoft 365 (nee MS Office 365) product from which data will be read - the installer won't allow otherwise. Install OLEDB x86 for 32-bit MS Office, x64 for 64-bit MS Office. It would be good general practice to keep SQL Server bitness, MS Office bitness and OLEDB Driver bitness all the same, installing x64 versions consistently for each. You're stuck with x86 32-bit for SSMS but that works fine with x64 or x86 SQL Server, on x86 or x64 Windows; just be aware that you may also need Visual Studio plus SSDT for debugging SQL on SQL Server 2019 and presumably subsequent editions.

  You can install Microsoft Data Access Components, x86 for use with Microsoft OLEDB ACE 12.0.

  Remote Access to a SQL Server instance requires further configuration.

• Connection using ODBC allows connection to other databases as well as to SQL Server, using different components and setup than for MSOLEDBSQL. ODBC is used to connect to several cloud-based servers including MySQL, MariaDB, PostgreSQL.

  A more extensive description on connection using ODBC is shown on the Web Query Tools page on this website.

  Name	Download
  Microsoft ODBC Driver for SQL Server.	Current Driver
  Windows ODBC Data Source Tool (to create ODBC DSN connection to 32/64-Bit sources).	%25windir%25/system32/odbcad32.exe
  MySQL Community Server.	Windows x86, 64-Bit
  MySQL Connectors for ODBC, Node.js, .NET, PHP.	Connector/ODBC

• ---

  Connection String You'll need to determine a connection string to use with the Provider selected. The site ConnectionStrings.com will help determine one.

  if you're making a Connection from Excel, which is after all how we started all this, Excel will help create an OLEDB connection string.

  Go to Menu Get Data > From Other Sources > From OLEDB, which brings up a dialogue box. The Build button brings up a Data Link Properties dialogue box. In the Provider tab, select a provider (probably MS OLEDB Driver (MSOLEDBSQL) or another one discussed above). Then on the Connection tab, set Server name like SERVERNAME\SQLEXPRESSINSTANCENAME (type, or select from dropdown); set Login like Windows Authentication; optional SPN can be left blank; Select the Database (Excel will offer a dropdown list of Databases in the Server selected).

  You can modify other Connection String values on the All tab. Test the Connection and OK; and Excel presents a Connection String.

  In the Advanced box you insert a SQL statement to be executed like EXEC dbo.sp_qABDBE; or a SELECT QUERY etc.; and OK. And voila, you have a functional Connection that you can Load To... and choose to drive a Structured Table, a Pivot Table, or just create a connection that you can use subsequently to drive a variety of pivot reports. In the connection Properties you can see the Connection String built by Excel, and use it in later connections you build.

  Sample Connection String for MSOLEDBSQL Excel-to-SQL Server:
  OLEDB; Provider=MSOLEDBSQL; Integrated Security=SSPI; Initial Catalog=DBDBDB; Data Source=XXXXXX\YYYYYY; Workstation ID=CCCCCC; Application Intent=READWRITE; with placeholders:
  DBDBDB names a particular database within SQL Server;
  XXXXXX is the name of the computer running the intended instance of SQL Server
  YYYYYY is the SQL Server instance such as "SQLEXPRESS"
  CCCCCC is the name of the client computer connecting to SQL Server.

  Sample Connection String for SQLOLEDB Excel-to-SQL Server:
  OLEDB; Provider=SQLOLEDB; Integrated Security=SSPI; Initial Catalog=DBDBDB; Data Source=XXXXXX\YYYYYY; Workstation ID=CCCCCC; Application Intent=READWRITE;
  with same placeholders as for MSOLEDBSQL.

  Sample Connection String for Microsoft.ACE.OLEDB.12.0 Excel to Microsoft Access:
  OLEDB; Provider=Microsoft.ACE.OLEDB.12.0; data source=FFFFFF; Mode=MMMMMM; where:
  FFFFFF is a fully-qualified computer filename (.accdb, .xlsx etc.)
  MMMMMM Mode controls READ DENY WRITE access. Connection String Keys reference, including Mode.

  Sample Connection String for Microsoft.ACE.OLEDB.12.0 for Excel .xlsx into Excel
  (Could be used setting up a Linked Server within a SQL Server to access a local Excel worksheet from within the database, or as a direct import (not through a database) of an external Excel worksheet into a Structured Table within an Excel file that uses its data, or pushes data to a remote SQL Server that cannot use a Linked Server because it is not local to the source Excel workbook.):
  OLEDB; Provider=Microsoft.ACE.OLEDB.12.0; data source=FFFFFF; Mode=MMMMMM; Extended Properties="QQQQQQ"; where:
  FFFFFF is a fully-qualified computer filename (.accdb, .xlsx etc.)
  MMMMMM Mode controls READ DENY WRITE access. I usually use Share Deny None. Connection String Keys reference, including Mode.
  QQQQQQ Extended Properties: To import .xlsx: Excel 12.0 Xml; To import .xlsm: Excel 12.0 macro; To include import of header top row HDR=YES; To handle mixed data types on the worksheet IMEX=1;
  Note inclusion of quote and semicolon characters in example below:
  Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\<your_path>\<your_filename>.xlsx;Mode=Share Deny None;Extended Properties="Excel 12.0 Xml;IMEX=1;HDR=YES";
  
  In an Excel connection, set Command Type as Table, and set Command Text as the name of the Worksheet to import, like <WorksheetName>$ where the "$" specifies that the whole worksheet is to be imported. Excel is pretty good at bringing in only the area of the sheet containing entries; but if several tables are on that sheet they will all be brought in as a single clump of data.
  
  Alternatively, to specify each individual Table to import, set Command Text as a Named Range that covers the full table including its Header Row and DataBodyRange. You can create the Named Range manually, or use the Shadow Range automatic technique discussed elsewhere on this site. PowerBI and M can also see the names of Structured Tables within an Excel file, but SQL Server and Excel can see only Named Ranges and Worksheet names.
  
  On my system, OLEDB.12 works fine but OLEDB.16 does not work to pull .xlsx into a Structured Table in the destination workbook.

  ---

  Microsoft PowerQuery installation note: Note that as of 11 March 2024, Get & Transform (Power Query) in Excel requires the Microsoft Edge WebView2 run-time library, and drops support for TLS 1.1 and lower. Client computers must be configured with this to use PowerQuery versions of PowerOpI tools and any other apps using PowerQuery.
  
  The "Evergreen" (self-updating) version of WebView2 is pre-installed in Windows 11, and has mostly been distributed to Windows 10 installations. There is also a fixed version of WebView2 which some applications could require, available at Distribute your app and the WebView2 Runtime.
  
  Microsoft PowerQuery connections work differently than Excel direct connections to SQL Server and MSAccess, but is nevertheless controlled by a different form of connection string with Templates like the following; within its structure, PowerQuery can connect to sources including SQL Server and MS Access and many many more.
  
  Sample Connection String for OLEDB to SQLServer:
  let Source = OleDb.DataSource("provider=PPPPPP;initial catalog=DBDBDB;data source=SSSSSS", [Query="QQQQQQ"]) in Source
  
  Sample Connection String for OLEDB to Excel File:
  let Source = Excel.Workbook(File.Contents("FFFFFF"),null,true), SheetImport=Source{[Name="TTTTTT"]}[Data], SheetImportPromoted=Table.PromoteHeaders(SheetImport, [PromoteAllScalars=true]) in SheetImportPromoted
  
  Sample Connection String for Connection to SQL Server:
  let Source = Sql.Database("SSSSSS", "DBDBDB", [Query="QQQQQQ"]) in Source
  
  Sample Connection String for Connection to MS Access:
  let Source = Access.Database(File.Contents("FFFFFF"), [CreateNavigationProperties=true]), ImportQuery = Source{[Schema="",Item="TQTQTQ"]}[Data] in ImportQuery
  
  with the same placeholders as above, including:
  PPPPPP is the OLEDB provider to be used such as MSOLEDBSQL or SQLOLEDB
  ZZZZZZ names a particular database within SQL Server
  XXXYYY is the qualified SQL Server name such as COMPUTER\SQLSERVERINSTANCE
  QQQQQQ is the SQL QUERY string
  SSSSSS is ServerName XXXXXX\YYYYYY
  TTTTTT is Table name or Named Range or Worksheet Name
  TQTQTQ is MS Access Table name or Query name.

  This Key reference specifies common OLEDB connection string keys. Specify the key values you need. The template strings above are sufficient to connect using the providers specified, and additional keys provide further control of the connection.

• ---

  Schema Permissions, and Role Membership: You need permissions CREATE PROCEDURE and ALTER on the schema in which the procedure is created, or membership in the db_ddladmin fixed database role in order to manage stored procedures (Create, Alter, Drop). As a developer, you may be granted CONTROL permission, which includes ALTER, DELETE, EXECUTE, INSERT, SELECT, UPDATE, VIEW DEFINITION permissions.

  Schema Permissions, and Role Membership are somewhat complicated, here are some explanatory references:

  • Schema-Based Access Control for SQL Server Databases, Phil Factor on redgate

  • GRANT Schema Permissions, on Microsoft SQL Docs

  • Permissions (Database Engine), on Microsoft SQL Docs

  • Schemas for a Database can be examined and created using SSMS, at SQL Server Instance > Databases > [databaseName] > Security > Schemas > right-click to Properties. Role membership and Permissions are assigned at SQL Server Instance > Security > Logins > [loginName] > right-click to Properties > Server Roles and Securables.

  Schema Permissions, and Role Memberships are accomplished by T-SQL GRANT and ALTER ROLE shown in the SQL code below. The code first creates a [Database] role and grants permissions to it, and developers are subsequently made members of that role. These are assigned at the database level, selecting the database in the dropdown on the SSMS SQL Editor toolbar. The code is shown to demonstrate syntax; you may need to work with your IT database admin to accomplish these.

      /* Create ROLE [Developer] */
  IF DATABASE_PRINCIPAL_ID( N'Developer' ) IS NULL 
      BEGIN CREATE ROLE [Developer] AUTHORIZATION db_securityadmin END;
  GO
  
  /* Set Role Attributes on each database */
  USE ExcelProto;
  GO
      
  	GRANT CONTROL ON SCHEMA::dbo TO [Developer] WITH GRANT OPTION;
  	/* CONTROL implies ALTER, DELETE, EXECUTE, INSERT, SELECT, 
                  UPDATE, VIEW DEFINITION */
  	GO
       
  	/* Add MEMBERs to the ROLEs for each database */
  	ALTER ROLE [Public] ADD MEMBER Owner;
  	ALTER ROLE Developer ADD MEMBER Owner;
  	ALTER ROLE db_owner ADD MEMBER Owner;
  	ALTER ROLE db_ddladmin ADD MEMBER Owner;
       
  	ALTER ROLE [Public] ADD MEMBER Richard;
  	ALTER ROLE Developer ADD MEMBER Richard;
  	ALTER ROLE db_owner ADD MEMBER Richard;
  	ALTER ROLE db_ddladmin ADD MEMBER Richard;
  

  You may need to use the SSMS user interface for some of these operations. Create the role using T-SQL, and grant CONTROL permission to that role. Then, from the SSMS ObjectExplorer, select Security > Logins and select each of the logins in turn, to be assigned to roles. Right-click on each Login in turn > and select Properties. From the Properties dialogue box select User Mapping. Enable the checkbox for each database for which roles are to be assigned to the selected Login. Then click each database in turn in the database list, and enable checkboxes in the bottom list box to assign roles to the Login for the selected database. Roles for developers are likely to include Public, Developer (the user-defined database role defined above), db_owner, and db_ddladmin fixed-database roles. Repeat for each database, for each Login to be supported for a database.

  You may be logged in to SSMS using a different Owner or Admin login than the Windows Login on the intended client systems that may manage Stored Procedures. The client that will use the database will probably use the Windows login current on that computer at the time of execution, so be sure to assign required permissions within SQL Server to that Windows Login, using SSMS. The Windows Login will typically be of the familiar form like MyComputerName\MyWindowsLoginName.

  Each user you want to empower to manage SPs must be assigned the security attributes, as described above, on each database on which you wish to so empower them. General users of the SQL Server instance will have BUILTIN\Users security attributes and so will not be capable of Creating, Altering, or Dropping your Stored Procedures.

• In Excel: Enable Native Database QUERYs so that you can author SQL outside of Excel's PowerQuery environment: Data Ribbon > Get & Transform Data Group > Get Data Dropdown > Query Options > Security item > Un-check box: Require user approval for new native database queries > OK.

• ---

  The SQLServer default login account to system service NT Service\MSSQL$SQLEXPRESS (default account: MSSQLSERVER) must have certain system and server privileges, and R/W access to the Excel datasource files to be linked in to SQL Server.

  • Microsoft Docs addressing SQL Server Login Account privileges and Security
    Selection of an Account for the SQL Server Agent Service,
    Granting Permissions on a Stored Procedure, and
    Using T-SQL GRANT.
    A Role can be defined using CREATE ROLE, to which SQL Server permissions are GRANTed; then user logins are mapped to roles in SSMS Security > Logins > LoginName > Properties > User Mapping.

  • Alternative 1: MSSQL$SQLEXPRESS is now constrained by other policies and may no longer work for alternatives 2 and 3. Instead, set the SQL Server login account to LocalSystem. This is the alternative most likely to enable OLEDB functionality of SQL Server with you datasource files. When you have this working, you can use the following alternatives to achieve a more-secure working login with narrower privileges.

    In Computer Management Console "%windir%\system32\compmgmt.msc /s", select Services and Applications/Services/SQL Server(<Instance such as "SQLEXPRESS">) and right-click Properties. On Properties tab Log On, select Built-in account, then pull-down to select LocalSystem, then re-start the SQL Server service. For reference, the LocalSystem login is the NT Authority\System login within SQL Server Security.

    Article by Mudassar Ahmed Khan illustrates this procedure.

    Article on StackOverflow discusses this procedure, and several other alternatives to set up Microsoft.ACE.OLEDB.12.0.

  • Alternative 2: Create an ActiveDirectory Security Group with "Read", "Read & Execute", and "List folder contents" permissions to your Excel datasource directory, and add all users of these tools, and user "NT Service\MSSQL$SQLEXPRESS" or another alternative login to that Group.

  • Alternative 3: Grant the SQL Server login account access to your datasource directory. Right-Click the datasource directory > Security > Group or user names: > Edit... > Add... > "NT Service\MSSQL$SQLEXPRESS", and give it permissions "Read", "Read & Execute", and "List folder contents".

  • Alternative 4: Put your data files in a directory set up for access by the SQL Server login account.

    • C:\Windows\ServiceProfiles\LocalService\AppData\Local\Temp             for User "LOCAL SERVICE"

    • C:\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp         for User "NETWORK SERVICE"

    • Directory established by the SQL Server Admin for other SQL Server login users.

  • Alternative 5: Change SQL Server login account to be a login account with access to the datasource files. In Computer Management "%windir%\system32\compmgmt.msc /s" select Services and Applications/Services/SQL Server(<Instance such as "SQLEXPRESS">) :

    • Set that Service right-click > Properties > Log On to a Login name with R/W access to your Excel files (e.g. your login account name, or another account set up with more limited security but including access to your source directory for SQL Server OLEDB/Excel files).

    • Re-start the SQL Server service.

    • Ref. ASPSnippets re: Microsoft.Ace.OLEDB.12.0 for Linked Server

  • Alternative 6: Grant Server Permissions CONNECT ANY DATABASE and IMPERSONATE ANY LOGIN; or more targeted permissions. See GRANT Server Permissions (Transact-SQL).

• ---

  Create a Database in SQL Server (see Microsoft Docs Create a Database), to be used to hold Stored Procedures including set up of Linked Servers, or QUERYs that execute JOINs on behalf of Excel clients. A separate Linked Server is stored in this SQL Server instance for each Excel workbook from which SQL Server will read source data from tables or worksheets. In the T-SQL code below the example Database instance for the project is named "ExcelProto". The SQL below could be run from SQL Server Management Studio (SSMS), or a Stored Procedure could be created to hold the code. For this example, the SP below is named "spCreateExcelProto" which would be executed by SQL calling "EXEC spCreateExcelProto;".

  DECLARE @dbname nvarchar(128)
  SET @dbname = N'ExcelProto'
  
  IF (NOT EXISTS (SELECT name FROM master.dbo.sysdatabases
      WHERE ('[' + name + ']' = @dbname OR name = @dbname)))
  	BEGIN
  		CREATE DATABASE ExcelProto
  
  	END
  

• ---

  A Linked Server should be instantiated for each Excel Workbook providing source tables to be read by SQL Server, named appropriately to reflect the name of the Workbook.

  • Before instantiating a Linked Server, SQL code should check for an existing instantiation by the same name. If such Linked Server already exists and if you are confident of its configuration, either skip instantiation of a new one, or DROP the existing one and instantiate and configure a new one. SQL code for this is shown in the example below.

• Create work QUERYs to JOIN multiple tables including the tables read by SQL Server from Excel. The QUERYs are then incorporated into Data Connections in Excel to drive Pivot Tables, which are executed on refresh of each Pivot Table.

SQL Server: Linking an Excel Workbook into SQL Server

Link using T-SQL

For reasons described below in the Automation Section, you'll have to execute these PROCEDUREs that include DROP and CREATE from SSMS (SQL Server Management Studio) or VS (Visual Studio), detailed above in the Installation section.

/* Copyright (c) 2018 Richard M. Bixler, All Rights Reserved. Do not delete this copyright notice. */
    USE ExcelProto
    GO

    /* Configure OLEDB */
    EXEC sp_configure
    @configname='Show Advanced Options',
    @configvalue=1;
    RECONFIGURE WITH OverRide;
    GO
    EXEC sp_configure
    @configname='Ad Hoc Distributed Queries',
    @configvalue=1;
    RECONFIGURE WITH OverRide;
    GO
    EXEC master.sys.sp_MSset_oledb_prop
    @provider_name=N'Microsoft.ACE.OLEDB.12.0',
    @property_name=N'AllowInProcess',
    @property_value=1;
    GO
    EXEC master.sys.sp_MSset_oledb_prop
    @provider_name=N'Microsoft.ACE.OLEDB.12.0',
    @property_name=N'DynamicParameters',
    @property_value=1;
    GO

/* Delete prior instance of Linked Server if it already exists
	Reference: https://stackoverflow.com/questions/3104186/
            is-there-an-if-exists-test-for-a-linked-server
	Identify the name of the Linked Server to be deleted e.g. 'BI_linked_tablesSQLServer'
            in two instances below:
*/
IF EXISTS ( SELECT * FROM sys.servers WHERE name = N'BI_linked_tablesSQLServer')
	BEGIN
		EXEC sp_dropserver
			@server = N'BI_linked_tablesSQLServer',
			@droplogins = 'droplogins';
	END

GO

/* Create and initialize a Linked Server to connect to each Excel Workbook 
	Name the Linked Server to be associated with the linked Workbook e.g.:
            @server= N'BI_linked_tablesSQLServer',
	Specify location of the workbook to be associated with the Linked Server:
            @datasrc= N'C:\Users\XXXXXX\
            linked_tablesSQLServer\BI_linked_tablesSQLServer.xlsm''

	To connect to .xlsm: @provstr= 'Excel 12.0 Macro;HDR=YES;IMEX=1;', 
	To connect to .xlsx: @provstr= 'Excel 12.0 Xml;HDR=YES;IMEX=1;', 
	To connect to .xlsb: @provstr= 'Excel 12.0;HDR=YES;IMEX=1;'
*/

USE ExcelProto
GO

EXEC sp_addLinkedServer
    @server= N'BI_linked_tablesSQLServer',
    @srvproduct= N'ACE 12.0',
    @provider= N'Microsoft.ACE.OLEDB.12.0',
    @datasrc= N'C:\Users\XXXXXX\
        BI_linked_tablesSQLServer.xlsm',
    @location= NULL,
    @provstr= 'Excel 12.0 Macro;HDR=YES;IMEX=1;',
    @catalog= NULL;
GO

Illustrates a QUERY that can be executed from SSMS. The setup could also be accomplished by a Stored Procedure (SP) in the database, which is covered below.

Excel: Structured Table Design

Excel Workbook Structure

The application structure described here has Excel Structured Tables read and JOINed by the database environment, and passed back to the Client, typically Excel, for reporting.

For MS Access we are able to keep Excel input tables in a workbook that also contains Excel pivots driven by Data Connections looping back through the database environment to those input tables in the same workbook. SQL Server, however, does not allow a workbook containing input tables to be open at the same time as a client using them through the database, and causes an error in the client on refresh. For this reason, you must place input tables into Source workbooks SEPARATE from any Client workbooks using data connections to access those input tables. Input tables and output pivots cannot co-exist in the same workbook, and the Source workbook cannot be open when a Client workbook refreshes connections back to the Input.

Column Names

SQL Server is unhappy if column names in tables are duplicated between tables in a JOIN. Avoid problems by prefacing column names in Structured Tables in Excel with a short identifier uniquely identifying the table to which each column resides; for example "akElement" for a column containing names of "Element" in a table called "Allocation".

Database Behavior on Duplicate Column Names in a QUERY

• MS Access adds a table identifier to duplicated column, for example "rngAlloc.Element" thereby avoiding duplicate-name problems.

• PowerQuery prefaces each column identifier by a table identifier so there are no duplicated names, for example "alloc.Element" and "bom.Element".

• SQL Server will not save a View including tables with a column name duplicated among participating tables. If Views are to be used, column names should not be duplicated among tables; names could include indication of the table in which the columns reside; for example, "akElement" and "emElement".

Use Excel Shadow Range Setup Code to make Structured Tables visible to SQL Server.

VBA code in Excel on each page containing a Structured Table will create a Named Range in Excel that is visible to the Linked Server in SQL Server that is set up to link the Excel workbook into SQL Server.

SQL Server, MS Access, and PowerQuery M: Normalized Tables

Data Source tables, which may be Excel Structured Tables or Tables within the Database Environment (PowerQuery, SQL Server, MS Access et al) should mostly be designed as Normalized structures. In quick summary, that means each record is a Key-Value pair, and records are independent of each other. A Key-Value pair specifies the value of a single data point, with an associated key value used to access it in JOIN operation. The Key may be complex, constructed from aggregation of multiple sub-fields; and there could be multiple data points in a record (so not formally normalized) if their values are directly related to each other and will not be referenced independently of each other. This is described with several reference links at the Pivot Zen page section on Normalization.

SQL Server and PowerQuery M: PIVOT and UNPIVOT

Normalized Tables when JOINed may result in many records as Left Table records are "exploded" when a key value is repeated among multiple records in a Right Table, each representing a separate value related to that key. To create pivot reports using those values, it may be useful to collapse some of those exploded rows into columns, resulting in fields that can be used as row fields in the Excel pivot table report. That can be accomplished in SQL or M, using verbs in those languages to PIVOT the JOINed data set that will be used to drive the Pivot Report in Excel.

Alternatively, you may receive source data in the form of data in a table that is not normalized, i.e. it contains multiple columns of data values. You can use UNPIVOT operations in SQL or PowerQuery M, to separate those columns into separate rows to achieve a normalized table representing the data.

Here is a link to an article describing the PIVOT operation in SQL Server. Implementation using PowerQuery M would use analogous functions; all descriptions linked just below.

• SQL PIVOT and UNPIVOT
• Description and SQL PIVOT and UNPIVOT Link
• PowerQuery M Table.Pivot and Table.Unpivot
• Description and PowerQuery M Table.Pivot Link and Table.Unpivot Link
• PowerQuery / Excel provides UI support for Table.Unpivot via Menu Data > Get Data > Other > From Other Sources > Blank Query > Power Query Editor Tab Transform > Unpivot Columns or Pivot Columns. We focus on this website on PowerQuery M though, so that PIVOT and UNPIVOT functionality can be added within QUERYs authored and localized within Excel PowerOpI tool frameworks like SQLServerTool.

SQL Server: Linking Excel Structured Tables into SQL Server

QUERY Structure

For reasons described below in the Automation Section, you'll have to execute these PROCEDUREs that include DROP and CREATE from SSMS (SQL Server Management Studio) or VS (Visual Studio), detailed above in the Installation section.

/*QUERY the Linked Server created (name of example Linked Server is "BI_linked_tablesSQLServer"),
    to connect to an Excel Workbook and retrieve data from a contained Worksheet or Range
    (or shadow range created by VBA in Excel described on this site). */

/* Use a 4-part-name to specify Linked Server and contained Excel range or shadow range ("rngAlloc").
    (syntax of 4-part-name: linkedservername.servername.schemaname.tablename
    with servername and schemaname = null so linkedservername...tablename) */
SELECT qAlloc.* FROM BI_linked_tablesSQLServer...rngAlloc as qAlloc;

/* Or use a 4-part name specifying a Linked Server and contained Excel Worksheet ("BOM")
    to be brought in as a table and named: */
SELECT qBOM.* FROM BI_linked_tablesSQLServer...[BOM$] as qBOM;


/* To read in a table from Excel and create a Table from it (SQL Server 2016): */
/* Source: https://blogs.msdn.microsoft.com/sqlserverstorageengine/2015/11/03/
        drop-if-exists-new-thing-in-sql-server-2016/    */
DROP TABLE IF EXISTS dbo.tblBOM
SELECT qBOM.* INTO dbo.tblBOM FROM BI_linked_tablesSQLServer...rngBOM as qBOM;

This structure is embedded into a QUERY which will JOIN multiple tables when executed by a Connection from Excel, and used to drive a Pivot report or a Table. This is shown in the section just following.

SQL Server: QUERY structure in SQL Server

QUERY Structure

Incorporating reading of Excel ranges into JOINs within a QUERY.

/* Starting from a QUERY linking-in a table from Excel through the Linked Server
(   e.g. qAlloc and qBOM above linked via Linked Server BI-linked_tableSQLServer Excel ranges
    rngAlloc, rngBOM), build a QUERY combining multiple tables (shown as external tables). */
/* Basic structure example JOINing two tables qAlloc and qBOM */
SELECT	xlAlloc.*, xlBOM.*, 
        xlAlloc.akQty * xlBOM.bmSubQty AS QtyExt,
        xlAlloc.akQty * xlBOM.bmSubQty * xlBOM.bmSubCost AS CostExt 
FROM	BI_linked_tablesSQLServer...rngAlloc AS xlAlloc
LEFT OUTER JOIN BI_linked_tablesSQLServer...rngBOM AS xlBOM 
        ON xlAlloc.akAllocRevKey = xlBOM.bmBOMItemKeyIn;

/* JOINs of multiple tables read from Excel and executed in a single QUERY. */
SELECT  xlAlloc.*, xlBOM.*, xlDates.*, xlBuilds.*, xlElements.*, 
        xlAlloc.akQty * xlBOM.bmSubQty AS QtyExt, 
        xlAlloc.akQty * xlBOM.bmSubQty * xlBOM.bmSubCost AS CostExt 

FROM    BI_linked_tablesSQLServer...rngAlloc AS xlAlloc

LEFT OUTER JOIN BI_linked_tablesSQLServer...rngBOM AS xlBOM 
        ON xlAlloc.akAllocRevKey = xlBOM.bmBOMItemKeyIn
LEFT OUTER JOIN BI_linked_tablesSQLServer...rngDates AS xlDates 
        ON xlBOM.bmBOMItemKeyOut = xlDates.daSchedItemRevKey
LEFT OUTER JOIN BI_linked_tablesSQLServer...rngBuilds AS xlBuilds 
        ON xlDates.daSchedItemRevKey = xlBuilds.bdBuildRevKey
LEFT OUTER JOIN BI_linked_tablesSQLServer...rngElements AS xlElements 
        ON xlBOM.bmBOMItemKey = xlElements.emElementItemKey;

Note the form of calculated columns QtyExt and CostExt in the two SELECT clauses above.

Multiple tables are JOINed in a single QUERY so that a connection in Excel, in which that QUERY is stored as the SQL Command, can provide a completely-executed datasource to drive a table, pivot report, or pivot chart back in Excel.

"SELECT *" is used so that addition or deletion of columns from the Excel tables driving the QUERY will propagate through the QUERY. "*" is frequently avoided because it may transfer more data than required or slow QUERY execution. That is more likely true of commercial or Big Data applications than for applications using the structure described here using Excel as data storage and client. Use of "*" avoids the program manager needing to update all QUERYs involved in a data path.

Automating Setup of Each Linked Server From SSMS, VS, or an Excel Client

A Linked Server is required in SQL Server for each Excel Workbook providing source data to be incorporated into SQL Server.

(Update 05 May 2020) The title of this section indicates you can set up and call PROCEDUREs that are described in the following section, from an Excel client. However, the capability to execute some of these was removed from Excel starting in Excel Build 1907, approximately mid-year 2019.

In particular, from Excel you now CANNOT execute native SQL QUERYs to DROP or CREATE or to EXEC a PROCEDURE that contains DROP or CREATE. There may be other verbs as well, outside the scope of this article. You can still EXEC an SP containing a SELECT QUERY, so it's the management of SPs that's affected and you can still handle data QUERY structure and maintenance inside an SP rather than in every client instance. My question on StackOverflow contains detail.

So unless or until effective workarounds can be determined, or full capability to execute Native SQL QUERYs is restored to Excel, the overall workaround will be to execute DROP and CREATE from SSMS (SQL Server Management Studio) or VS-SSDT (Visual Studio with SQL Server Data Tools), detailed above in the Installation section.

Also see the article on VBA Connection for an automated solution for this operation.

SQL to DROP a prior instance of this SP if it's already there. Subject to note above though...

DROP PROCEDURE IF EXISTS dbo.Create_spBI_linked_tablesSQLServer;

Then create and send a Stored Procedure (SP) to SQL Server, to create the Linked Server. The SP will DROP the previous Linked Server instance if any, then re-CREATE the Linked Server (configured for OLEDB to read Excel files) in SQL Server. Note, no USE or GO in an SP; Excel Data Connection to the database in the SQL Server instance provides that context. Execute the following SQL code via an Excel connection, which will create and store the SP it defines, in the Database.

CREATE PROCEDURE dbo.Create_spBI_linked_tablesSQLServer
AS
BEGIN
/* Configure OLEDB */
EXEC sp_configure
  @configname='Show Advanced Options',
  @configvalue=1;
RECONFIGURE WITH OverRide;
EXEC sp_configure
  @configname='Ad Hoc Distributed Queries',
  @configvalue=1;
RECONFIGURE WITH OverRide;
EXEC master.sys.sp_MSset_oledb_prop
  @provider_name=N'Microsoft.ACE.OLEDB.12.0',
  @property_name=N'AllowInProcess',
  @property_value=1;
EXEC master.sys.sp_MSset_oledb_prop
  @provider_name=N'Microsoft.ACE.OLEDB.12.0',
  @property_name=N'DynamicParameters',
  @property_value=1;
/* Drop Linked Server if already exists */
IF EXISTS (SELECT * FROM sys.servers WHERE name = N'BI_linked_tablesSQLServer')
  BEGIN
EXEC sp_dropserver
  @server = N'BI_linked_tablesSQLServer',
  @droplogins = 'droplogins';
  END
/* Create replacement Linked Server */
EXEC sp_addLinkedServer
  @server= N'BI_linked_tablesSQLServer',
  @srvproduct= N'ACE 12.0',
  @provider= N'Microsoft.ACE.OLEDB.12.0',
  @datasrc= N'C:\Users\XXXXXX\linked_tablesSQLServer
        \BI_linked_tablesSQLServer.xlsm',
  @location= NULL,
  @provstr= 'Excel 12.0 Macro;HDR=YES;IMEX=1;',
  @catalog= NULL;
  RETURN
END;

Use an Excel Data Connection to EXECUTE the Stored Procedure in SQL Server that will Create the Linked Server in SQL Server. The following code included in a connection will do that.

DECLARE @Return int;
EXEC dbo.Create_spBI_linked_tablesSQLServer;
SELECT @Return;

QUERY Using an Excel Data Connection

Clients can then execute QUERYs that use the Linked Server, by executing a Connection containing the following SQL. The QUERY is an example, using the same QUERY as shown above.

SELECT xlAlloc.*, xlBOM.*, xlDates.*, xlBuilds.*, xlElements.*, 
        xlAlloc.akQty * xlBOM.bmSubQty AS QtyExt, 
        xlAlloc.akQty * xlBOM.bmSubQty * xlBOM.bmSubCost AS CostExt 
FROM BI_linked_tablesSQLServer...rngAlloc AS xlAlloc
LEFT OUTER JOIN BI_linked_tablesSQLServer...rngBOM AS xlBOM 
        ON xlAlloc.akAllocRevKey = xlBOM.bmBOMItemKeyIn
LEFT OUTER JOIN BI_linked_tablesSQLServer...rngDates as xlDates 
        ON xlBOM.bmBOMItemKeyOut = xlDates.daSchedItemRevKey
LEFT OUTER JOIN BI_linked_tablesSQLServer...rngBuilds as xlBuilds 
        ON xlDates.daSchedItemRevKey = xlBuilds.bdBuildRevKey
LEFT OUTER JOIN BI_linked_tablesSQLServer...rngElements as xlElements 
        ON xlBOM.bmBOMItemKey = xlElements.emElementItemKey;

QUERY Using a Stored Procedure in SQL Server

See the note above in the Automation section, regarding management of STORED PROCEDUREs via Excel.

The owner of a set of tables to be JOINed and QUERYed might use the same procedure to store a QUERY with the database, so that clients can execute it on command from Excel; rather than each Client sending the QUERY code up to SQL Server within a connection. Using an SP to QUERY allows the source data owner to provide and support source data structure and QUERY structure, without requiring client instances to be updated to incorporate changes.

Here's a useful YouTube video covering basics of Stored Procedures.

First, Owner DROP any previous definition of the QUERY: (or you could use ALTER instead of CREATE, as shown in the above-linked video.)

DROP PROCEDURE IF EXISTS dbo.sp_qABDBE;

Owner define or re-define the QUERY:

CREATE PROCEDURE dbo.sp_qABDBE AS
BEGIN
	SELECT xlAlloc.*, xlBOM.*, xlDates.*, xlBuilds.*, xlElements.*, 
            xlAlloc.akQty * xlBOM.bmSubQty AS QtyExt, 
            xlAlloc.akQty * xlBOM.bmSubQty * xlBOM.bmSubCost AS CostExt 
	FROM BI_linked_tablesSQLServer...rngAlloc AS xlAlloc
	LEFT OUTER JOIN BI_linked_tablesSQLServer...rngBOM AS xlBOM 
            ON xlAlloc.akAllocRevKey = xlBOM.bmBOMItemKeyIn
	LEFT OUTER JOIN BI_linked_tablesSQLServer...rngDates as xlDates 
            ON xlBOM.bmBOMItemKeyOut = xlDates.daSchedItemRevKey
	LEFT OUTER JOIN BI_linked_tablesSQLServer...rngBuilds as xlBuilds 
            ON xlDates.daSchedItemRevKey = xlBuilds.bdBuildRevKey
	LEFT OUTER JOIN BI_linked_tablesSQLServer...rngElements as xlElements 
            ON xlBOM.bmBOMItemKey = xlElements.emElementItemKey;
END;

Clients execute the QUERY:

EXEC dbo.sp_qABDBE;

Connection to SQL Server using PowerQuery

For PowerQuery, start with Get Data, and select "From SQL Server Database"...

When Login credentials are accepted, connection is established. Power Pivot will offer only Database Tables, but to import from Excel and to perform a QUERY in SQL Server, in the QUERY you can specify either a SELECT QUERY:

Or EXECUTE a Stored Procedure (SP) containing a QUERY:

SQL verb CREATE is not supported. To CREATE an SP, you can instead use a SQL EXEC command in an Excel Data Connection as described just above.

A preview of the QUERY result is shown, and you can choose to Load To... to determine how to use this QUERY.

As usual, you can create a Pivot Table or Pivot Chart, a Table, or just create a Connection for later use driving a Pivot or Table. You can add this QUERY to the Data Model for PowerBI and DAX.

Since the SQL Server QUERY handles the Excel Tables, column add / update / delete in the Excel Tables works properly as expected.

QUERY Using OPENROWSET

You can avoid the complexity of setting up a Linked Server in SQL Server, and of setting up Stored Procedures, by using OPENROWSET. In the QUERY structure shown several times above, replace 4-part references in QUERYs using the Linked Server to read a named ranges from an Excel Source table, by OPENROWSET( provider_name, datasource, object | QUERY ).
Other arguments noted, provide for sub-QUERY, and for BULK data import.

The same Example QUERY used in several examples above, implemented using OPENROWSET:

SELECT xlAlloc.*, xlBOM.*, xlDates.*, xlBuilds.*, xlElements.*,
	xlAlloc.akQty * xlBOM.bmSubQty AS QtyExt,
	xlAlloc.akQty * xlBOM.bmSubQty * xlBOM.bmSubCost AS CostExt

FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
	'Excel 12.0;Database=C:\Users\XXXXXX
                   \linked_tablesSQLServer\BI_linked_tablesSQLServer.xlsm;HDR=YES',
	rngAlloc) AS xlAlloc

LEFT OUTER JOIN OPENROWSET('Microsoft.ACE.OLEDB.12.0',
	'Excel 12.0;Database=C:\Users\XXXXXX
                   \linked_tablesSQLServer\BI_linked_tablesSQLServer.xlsm;HDR=YES',
	rngBOM) AS xlBOM
	ON xlAlloc.akAllocRevKey = xlBOM.bmBOMItemKeyIn

LEFT OUTER JOIN OPENROWSET('Microsoft.ACE.OLEDB.12.0',
	'Excel 12.0;Database=C:\Users\XXXXXX
                   \linked_tablesSQLServer\BI_linked_tablesSQLServer.xlsm;HDR=YES',
	rngDates) AS xlDates
	ON xlBOM.bmBOMItemKeyOut = xlDates.daSchedItemRevKey

LEFT OUTER JOIN OPENROWSET('Microsoft.ACE.OLEDB.12.0',
	'Excel 12.0;Database=C:\Users\XXXXXX
                   \linked_tablesSQLServer\BI_linked_tablesSQLServer.xlsm;HDR=YES',
	rngBuilds) AS xlBuilds
	ON xlDates.daSchedItemRevKey = xlBuilds.bdBuildRevKey

LEFT OUTER JOIN OPENROWSET('Microsoft.ACE.OLEDB.12.0',
	'Excel 12.0;Database=C:\Users\XXXXXX
                   \linked_tablesSQLServer\BI_linked_tablesSQLServer.xlsm;HDR=YES',
	rngElements) AS xlElements
	ON xlBOM.bmBOMItemKey = xlElements.emElementItemKey;

Use of OPENROWSET does not require setup of a Linked Server for each datasource. A QUERY of this form can be embedded directly into any of the constructs shown previously for SQL Server including in an Excel Data Connection, or a PowerQuery QUERY, which can be used to drive a Pivot Report, or Table or PowerBI report. A QUERY can mix use of OPENROWSET with use of Linked Servers, to read data from multiple Excel Sources within the QUERY.

The tradeoff: a more complex OPENQUERY, that can be constructed programmatically within a Client; versus a more complex algorithm in a data Source, to set up Linked Servers to the Source data referenced by QUERYs in Clients thereby simplifying Client QUERYs.

The form of the QUERY shown needs to know the Source data Table structure and directory structure. You may not want to expose that information to a Client making the QUERY - that may expose organization or product structure to the Client that you'd prefer to keep confidential. A Linked Server encapsulates such information. To avoid exposing detailed information about the QUERY using OPENROWSET, the Source data owner can provide a Stored Procedure in SQL Server containing the QUERY that uses OPENROWSET; then detailed information about the QUERY is hidden behind the name of the SP. The Client executes SQL as above like "EXECUTE sp_ABDBE", and the detail is not required within the Client. And as mentioned above re using a Stored Procedure, maintenance may be simplified by concentrating detail in the Server rather than in the Clients.

Robert Sheldon provides more detail on Using the OPENROWSET function in SQL Server, and Dan Buskirk provides a series of articles on Using OPENROWSET to Read Excel Worksheets from SQL Server.

An important use of OPENROWSET is for bulk data import into SQL Server. The syntax reference for OPENROWSET gives several examples of how OPENROWSET can be used to import data into a SQL Server table using the BULK rowset provider. Mr. Sheldon's article linked above provides further commentary on this functionality including use of the 'object' parameter as a sub-QUERY. These functionalities of SQL Server, like others described on this site, can be instigated from Excel using the methods described above on this web page.

QUERY Using UNION

Illustration of a QUERY using SQL UNION operator and OPENROWSET. UNION is wrapped inside a SELECT QUERY:

USE ExcelProto;
GO

SELECT qGroupUnionAlias.* FROM 
 
(SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0; 
Database=C:\XXXXXX\Merge.xlsm;HDR=YES', 
rngGroup1) 
 
UNION ALL 
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0; 
Database=C:\XXXXXX\Merge.xlsm;HDR=YES', 
rngGroup2) 
 
UNION ALL 
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0; 
Database=C:\XXXXXX\Merge.xlsm;HDR=YES', 
rngGroup3)) 
 
AS qGroupUnionAlias;

And illustration using a Linked Server:

USE ExcelProto;
GO

SELECT qGroupUnionAlias.* FROM 
 
(SELECT * FROM BI_linked_tablesSQLServer...rngGroup1
 
UNION ALL 
SELECT * FROM BI_linked_tablesSQLServer...rngGroup2
 
UNION ALL 
SELECT * FROM BI_linked_tablesSQLServer...rngGroup3)
 
AS qGroupUnionAlias;

The Excel file access parameters, seen in OPENROWSET, are wrapped inside the Linked Server.

SQL Server From Remote Clients

Notes on setup of SQL Server configuration and security to allow QUERYs from a client located on a computer separate from the computer hosting the SQL Server instance can be found at SQL Server from remote client