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.