-- Get table (worksheet) or column (field) listings from an excel spreadsheet -- SET THESE! declare @linkedServerName sysname = 'TempExcelSpreadsheet' declare @excelFileUrl nvarchar(1000) = 'c:\MySpreadsheet.xls' -- /SET -- Remove existing linked server (if necessary) if exists(select null from sys.servers where name = @linkedServerName) begin exec sp_dropserver @server = @linkedServerName, @droplogins = 'droplogins' end -- Add the linked server -- ACE 12.0 seems to work for both xsl and xslx, though some might prefer the older JET provider exec sp_addlinkedserver @server = @linkedServerName, @srvproduct = 'ACE 12.0', @provider = 'Microsoft.ACE.OLEDB.12.0', @datasrc = @excelFileUrl, @provstr = 'Excel 12.0;HDR=Yes' -- Grab the current user to use as a remote login declare @suser_sname nvarchar(256) = suser_sname() -- Add the current user as a login exec sp_addlinkedsrvlogin @rmtsrvname = @linkedServerName, @useself = 'false', @locallogin = @suser_sname, @rmtuser = null, @rmtpassword = null -- Return the table/column info exec sp_tables_ex @linkedServerName exec sp_columns_ex @linkedServerName -- Remove temp linked server if exists(select null from sys.servers where name = @linkedServerName) begin exec sp_dropserver @server = @linkedServerName, @droplogins = 'droplogins' end
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)