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?

dbenham · Accepted Answer · 2016-12-15 02:26:27Z

4

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

edited Dec 15, 2016 at 2:26

answered Jul 19, 2015 at 18:15

dbenham

132k29 gold badges273 silver badges410 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Nathan Over a year ago

pushd "%~f0" does not work for me, but pushd "%~dp0" does.

dbenham Over a year ago

@Nathan - You are absolutely correct. That was a sloppy mistake on my part. Thanks. I've corrected the answer with an edit.

Mofi · Accepted Answer · 2015-07-19 13:47:27Z

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.

Collectives™ on Stack Overflow

Specifying relative path to :r command in sqlcmd mode

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related