CODEWITHSUNDEEP
sql-serverbatch-filesqlcmd
Dev
Dev

Reputation: 51

Specifying relative path to :r command in sqlcmd mode

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?

Upvotes: 3

Views: 4493

Answers (2)

dbenham
dbenham

Reputation: 130919

@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.

Upvotes: 4

Mofi
Mofi

Reputation: 49167

What about using complete paths, but creating file Main.sql dynamically?

Batch file which expects the 4 script files in folder of the batch file and creates there also Main.sql.

@echo off
set "BatchFolder=%~dp0"
(   echo :r %BatchFolder%script1.sql
    echo :r %BatchFolder%script2.sql
    echo :r %BatchFolder%script3.sql
    echo :r %BatchFolder%script4.sql
)>"%BatchFolder%Main.sql"
sqlcmd.exe -E -d "YourDatabaseName" -i "%BatchFolder%Main.sql"
set "BatchFolder="
pause

Open a command prompt window and run there call /?. The help of this command is output in the window explaining %~dp0 which means drive and path of argument 0 ending with a backslash without surrounding quotes. Argument 0 on running of a batch file is the name of the batch file.

Running in command prompt window set /? results in getting help of command set displayed, listing on last help page some special environment variables defined dynamic on running a batch file. The first one listed is %CD% which means current directory.

So instead of working with folder of batch file, it is also possible to work with current folder on batch execution.

@echo off
set "CurrentFolder=%CD%\"
(   echo :r %CurrentFolder%script1.sql
    echo :r %CurrentFolder%script2.sql
    echo :r %CurrentFolder%script3.sql
    echo :r %CurrentFolder%script4.sql
)>"%CurrentFolder%Main.sql"
sqlcmd.exe -E -d "YourDatabaseName" -i "%CurrentFolder%Main.sql"
set "CurrentFolder="
pause

The folder path referenced by %CD% does not end with a backslash like the folder path returned by %~dp0 which is the reason for backslash after %CD% on second line.

Upvotes: 0

Related Questions