SQL - Dynamic SQL inside OpenRowSet then Inserted into Temp Table

Question

I am trying to utilize Dynamic SQL that will parse in the file directory into an OpenRowSet(@Database). Right now I have a hard coded directory for the Excel File. The end result will be me creating an application that will take the user's file and import into the SQL table. From there I'll be merging and Match/Not Matching (Which is working properly). This is the last piece of the puzzle. I don't know why the error message is looking for my file in "C:\WINDOWS\system32\ "

My current error messages are:

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "The Microsoft Access database engine could not find the object 'C:\WINDOWS\system32\ C:\Users\GrimRieber\Desktop\isi test.xlsx'. Make sure the object exists and that you spell its name and the path name correctly. If 'C:\WINDOWS\system32\ C:\Users\GrimRieber\Desktop\isi test.xlsx' is not a local object, check your network connection or contact the server administrator.".

Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

Code:

declare @Database varchar(max)

select @Database = 'C:\Users\GrimRieber\Desktop\isi test.xlsx'

declare @sql varchar(Max)

select @sql = 'SELECT * FROM OPENROWSET(
''Microsoft.ACE.OLEDB.12.0'',
''Excel 12.0; Database= ' + @Database + '; HDR=YES; IMEX=1'',
''SELECT [Vendor],[VendorName],[Material],[MaterialName],[Supplier Stock Num],[01],[02],[03],[04],[05],[06],[07],[08],[09],[10],[11],[12],[Year]FROM [Data$]''
)'

IF OBJECT_ID('tempdb.dbo.#TempScorecardInventorySold', 'U') IS NOT NULL
  DROP TABLE #TempScorecardInventorySold;

CREATE TABLE #TempScorecardInventorySold
(
    [Vendor] [varchar](50) NULL,
    [VendorName] [varchar](50) NULL,
    [Material] [varchar](50) NULL,
    [MaterialName] [varchar](50) NULL,
    [Supplier Stock Num] [varchar](50) NULL,
    [01] [nVarchar](50) NULL,
    [02] [nVarchar](50) NULL,
    [03] [nVarchar](50) NULL,
    [04] [nVarchar](50) NULL,
    [05] [nVarchar](50) NULL,
    [06] [nVarchar](50) NULL,
    [07] [nVarchar](50) NULL,
    [08] [nVarchar](50) NULL,
    [09] [nVarchar](50) NULL,
    [10] [nVarchar](50) NULL,
    [11] [nVarchar](50) NULL,
    [12] [nVarchar](50) NULL,
    [Year] [Int] Null
) ON [PRIMARY];

INSERT INTO [dbo].#TempScorecardInventorySold ([Vendor],[VendorName],[Material],[MaterialName],[Supplier Stock Num],[01],[02],[03],[04],[05],[06],[07],[08],[09],[10],[11],[12],[Year])
EXECUTE(@sql)

Answer 1

While I cannot recreate your file path issue (be sure to carefully check if file exists at the path and file extension), the OPENROWSET() in that setup should define fields in the SELECT clause at beginning. The last argument should be unquoted, pointing to worksheet range:

select @sql = 'SELECT [Vendor],[VendorName],[Material],[MaterialName],[Supplier Stock Num],
                      [01],[02],[03],[04],[05],[06],
                      [07],[08],[09],[10],[11],[12],[Year] 
              FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'',
              ''Excel 12.0; Database= ' + @Database + '; HDR=YES; IMEX=1'', [Data$])';

---

Alternatively, consider OPENDATASOURCE:

select @sql = 'SELECT [Vendor],[VendorName],[Material],[MaterialName],[Supplier Stock Num],
                      [01],[02],[03],[04],[05],[06],
                      [07],[08],[09],[10],[11],[12],[Year]
              FROM  OPENDATASOURCE(''Microsoft.ACE.OLEDB.12.0'',
              ''Data Source=' + @Database + ';Extended Properties=Excel 12.0'')...Data$' 

Or even a Driver version (fields can be in either of two SELECT clauses)

select @sql = 'SELECT [Vendor],[VendorName],[Material],[MaterialName],[Supplier Stock Num],
                      [01],[02],[03],[04],[05],[06],
                      [07],[08],[09],[10],[11],[12],[Year] 
               FROM OPENROWSET(''MSDASQL'',
               ''Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};
               DBQ=' + @Database + ''', ''SELECT * FROM [DATA$]'');