I have the below master script which creates tables, and inserts some data and then creates the stored procedures.
--todo_master.sql
use master
go
:r todo_create_ddl.sql
:r todo_create_dml.sql
:r todo_create_sprocs.sql
go
However, even though the todo_master.sql is in the same path as the other three scripts, it is unable to locate those three scripts.
I get the following error:
A fatal scripting error occurred.
The file specified for :r command was not found.
If I provide the complete path like below, these files are found and executed as intended.
"C:\Docs and Settings\user\My Docs\SSMS\Projects\todo_create_ddl.sql"
What might I be missing?
Edit As suggested by Jason I tried this, but still get the same error:
use master
go
:setvar path "C:\Documents and Settings\user\My Documents\SQL Server Management Studio\Projects"
:setvar ddl "todo_create_ddl.sql"
:setvar dml "todo_create_dml.sql"
:setvar sprocs "todo_create_sprocs.sql"
:r $(path)$(ddl)
:r $(path)$(dml)
:r $(path)$(sprocs)
go
You can work around this by using the sqlcmd setvar option to assign the path to a variable. Then use that variable in your :r call like:
:setvar path "c:\some path"
:r $(path)\myfile.sql
This link has a more in depth example: http://www.simple-talk.com/sql/sql-tools/the-sqlcmd-workbench/
With that in place you can remove the setvar line and pass that in from the command line with:
Sqlcmd /Sserver /E -ddatabase -iInputfilename -oOutputfilename -v path=c:\somepath
This would work around the issue of the script not running from the directory where the first SQL script was called from.