SQLTeam.com | Weblogs | Forums

Openrowset to a different excel-file depending on condition

Transact-SQL
#1

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.

#2

Might be hokey but what if you did it the following way

if @@servername = 'prod'
  begin
	SELECT *
	FROM OPENROWSET(
		'Microsoft.ACE.OLEDB.12.0',
		'Excel 12.0;Database=D:\Databases\DBTables.xlsx;',
		'SELECT * FROM [Data$]'
	) AS EX
  end
else
begin
	SELECT *
	FROM OPENROWSET(
		'Microsoft.ACE.OLEDB.12.0',
		'Excel 12.0;Database=C:\Projects\Project1\Databases\DBTables.xlsx;',
		'SELECT * FROM [Data$]'
	) AS EX
end
#3

It doesn't work because for some reason SQL Server checks for an existense both of these xlsx-files :face_with_diagonal_mouth:

you can try:

CREATE PROCEDURE dbo.proc1
AS
BEGIN
if @@servername = 'prod'
  begin
	SELECT *
	FROM OPENROWSET(
		'Microsoft.ACE.OLEDB.12.0',
		'Excel 12.0;Database=D:\Databases\DBTables.xlsx;',
		'SELECT * FROM [Data$]'
	) AS EX
  end
else
begin
	SELECT *
	FROM OPENROWSET(
		'Microsoft.ACE.OLEDB.12.0',
		'Excel 12.0;Database=C:\Projects\Project1\Databases\DBTables.xlsx;',
		'SELECT * FROM [Data$]'
	) AS EX
end
END
GO
#4

interesting.
You don't have a D drive in the non prod server?

#5

I don't have D drive.

#6 
DECLARE @sql AS varchar(MAX)
DECLARE @metasql as varchar(MAX)
DECLARE @PrintQuery AS bit
DECLARE @ExecQuery AS bit
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'

SET @PrintQuery = 1
SET @ExecQuery = 0

SET @sql = 
'
USE OtherDatabase
go

CREATE PROCEDURE dbo.proc1
AS
BEGIN 
SELECT *
			FROM OPENROWSET(
				''Microsoft.ACE.OLEDB.12.0'',
				''Excel 12.0;Database=' +  @path + ',
				''SELECT * FROM [Data$]''
			) AS EX
' + 


+ '
END
'

IF @PrintQuery = 1
    PRINT @sql
IF @ExecQuery = 1
    EXEC (@metasql)
#7

If you don't want to use dynamic sql you can always check if the file exists or you can catch the error and continue.

File Validation in SQL Server with xp_fileexist (sqlshack.com)

Nice error handling example
sql server - Check file exists with T-SQL openrowset - Stack Overflow