How to make Excel available from SQL Server

Yesterday, I got an assignment to read an Excel spreadsheet. Well, well, well... How can I do that. As usually, I searched Google, and I found a lot of crab, but after a while; I found something about linked servers in SQL Server.

Here is what I found.

Place the Excel document in a place, available to the SQL Server.
1. Open "Microsoft SQL Server Management Studio" (MSSMS).
2. Connect to the database of choice as an administrator or similar user
3. Change according to your needs, and run this script on the database.

DECLARE @RC int
DECLARE @server nvarchar(128)
DECLARE @srvproduct nvarchar(128)
DECLARE @provider nvarchar(128)
DECLARE @datasrc nvarchar(4000)
DECLARE @location nvarchar(4000)
DECLARE @provstr nvarchar(4000)
DECLARE @catalog nvarchar(128)
-- Set parameter values
SET @server = 'XLTEST_SP'
SET @srvproduct = 'Excel'
SET @provider = 'Microsoft.ACE.OLEDB.12.0'
SET @datasrc = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\MultiMark POS Dump V1.2 - Copy.xlsm'
SET @provstr = 'Excel 12.0'
EXEC @RC = [master].[dbo].[sp_addlinkedserver] @server, @srvproduct, @provider,
@datasrc, @location, @provstr, @catalog

4. Open the Server Objects folder in Object Explorer.
5. Open Linked Servers.
6. Right-click Linked Servers.
7. Click Refresh. Now, you see the Linked Server XLTEST_SP.
8. Right-Click XLTEST_SP.
9. Click Properties in the context menu. Popping up "Linked Server Properties - XPTEST_SP".
10. Set the Security to "Be made using this security context:"
11. Remote Login: Admin
12. With password blank.
13. Hit the OK button. And everything ready for use.

14. Expand the XLTEST_SP Linked Server.
15. Expand the Catalog folder.
16. Expand the default folder.
17. Expand the Tables folder.

Now a list of tables will be available. These tables are actual names of the Sheets in the Excel document.

There are a lot of drawbacks to this access method, but with some disciplined behavior it is feasible to use Excel as a data source like SQL Views.