Specifying relative path to :r command in sqlcmd mode

Question

A) Suppose you have four .sql files (script1.sql,script2.sql,script3.sql,script4.sql ) in say in a folder c:\scripts.

B) Create a main script file (Main.sql) with the following, please note I have given relative path for scripts.

:r script1.sql
:r script2.sql
:r script3.sql
:r script4.sql

Save the Main.sql in c:\scripts itself.

C) Create a batch file named "ExecuteScripts.bat" with the following:-

SQLCMD -E -d<YourDatabaseName> -ic:\Scripts\Main.sql
PAUSE

When I run the batch file, its unable to script1.sql file. When i give full path C:\scripts\script1.sql, it works fine but I don't want to hardcode the path here.

Is it possible to achieve this using sqlcmd?

Answer 1

@echo off
pushd "c:\scripts"
SQLCMD -E -d<YourDatabaseName> -iMain.sql
popd
PAUSE

Or if all the sql scripts are in the same folder as your batch script, then:

@echo off
pushd "%~dp0"
SQLCMD -E -d<YourDatabaseName> -iMain.sql
popd
PAUSE

The last version allows your scripts to run properly, no matter where they reside.