SSMS with SQLCMD Mode

by

Using SSMS with a master script is a reliable approach, especially with multiple scripts. By running in SQLCMD mode within SSMS, you can use a single master script to execute multiple scripts in sequence without manual intervention.

Step-by-Step Guide for SSMS with SQLCMD Mode

  1. Prepare a Master Script:
    • Create a new SQL file (e.g., master_script.sql) that lists each script with the :r command.
    • Example master_script.sql:
    • sqlCopy code:r C:\path\to\script1.sql 
:r C:\path\to\script1.sql
:r C:\path\to\script2.sql
:r C:\path\to\script3.sql
-- Continue adding all your scripts
:r C:\path\to\script17.sql
  1. Enable SQLCMD Mode in SSMS:
    • Open SSMS, then open your master_script.sql.
    • Go to Query > SQLCMD Mode in the menu bar to enable SQLCMD mode (you should see SQLCMD displayed in the query editor).
  2. Run the Master Script:
    • With SQLCMD mode enabled, execute master_script.sql by pressing F5 or clicking Execute. SSMS will process each :r command in sequence, loading and executing each specified file as if it were directly written in the master script.
    • Any errors encountered will be displayed in the SSMS output, so you can troubleshoot specific scripts if needed.

Notes and Tips

  • Absolute Paths: Ensure that each path in your master script is accurate and uses absolute paths, as relative paths may not resolve correctly.
  • Error Logging: Consider running in SSMS with the Results to File option (available under Query > Results to File), so you can review execution results if there’s a need to audit which scripts completed successfully.

Additional Notes

  • Consistency: For all paths, whether they contain spaces or not, it’s a good practice to always enclose them in double quotes to avoid issues.
  • File Location: Make sure each file path is accessible from the system where SSMS is running.

Leave a Comment