Dealing with SQLCMD :r in a post-deployment script

Question

We use a Visual Studio Database Project for the source control of the schema of our database. We also want to script certain data (configuration values etc.) from the database, so we use a custom command line utility for dumping data into insert scripts. One file represents one table's scripted data.

There is a certain SQL script, called post-deployment script, which automatically gets executed after the Deploy command on the project. It is recommended to link other script files here, and we'd like to link our data insert scripts.

Instructions say to use this command to parse from arbitrary files

SQLCMD :r <filename>

The problem is, I don't see how my colleagues will be able to use it when they download it from source control, because the start directory for the :r command is where devenv.exe (the VS dev. env.) is located.

Are we approaching the problem of scripting data from the right angle with the right tools? And if it is so, then how can I get the executing post-deployment script file's path, or how can I parse the insert scripts without hard coding their path into the post-deployment script?

Answer 1

Well, it turns out, that simply specifying the relative path to the script file is enough. I got misled by Visual Studio's Validate SQL Syntax feature, which evaluated the path after :r by using devenv.exe's location as a starting directory.

However, when Deploy is executed on the project, the :r command uses the script's directory as the starting directory, so the file's location can be given easily using a relative path.