I have a procedure with a query pointing to an xlsx-file with a sheet of data.
The problem is the path to this xlsx-file differs on production and developer machines.
Is there any easy flexible way to run this query upon one path on one sql-server-name and another path on another sql-server-name?
I can't change the path. I want to take backup with that stored procedure from production to developer machine and do not change paths to files manually in that procedure.
SELECT *
FROM OPENROWSET(
'Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=D:\Databases\DBTables.xlsx;',
'SELECT * FROM [Data$]'
) AS EX
I tried something like this but failed:
declare @path nvarchar(1000)
if @@servername = 'prod'
set @path = 'D:\Databases\DBTables.xlsx'
else if @@servername = 'dev'
set @path = 'C:\Projects\Project1\Databases\DBTables.xlsx'
SELECT *
FROM OPENROWSET(
'Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=' + @path,
'SELECT * FROM [Data$]'
) AS EX
I know about dynamic sql commands (exec(...)) but it's a bit overkill since the underlying query is more complex than this simplified example. It would be better to avoid dynamic t-sql if possible due to maintaince reasons.