SSMS SQLCMD Mode

A question was posted on the MSDN forums in regards to executing a query file from SQL Server management studio without having to first open the file.  My first instinct was to use xp_cmdshell or even create a clr procedure, but this was way overkill.  I began to respond pointing out that the ability to pass an input query file using sqlcmd was easy and straightforward, but the post was specific to SSMS.  After a quick refresher I realized that using sqlcmd mode in management studio met all the requirements. 

In SQL Server 2005 the command line interface sqlcmd was introduced as successor to osql.  In addition to the new interface sqlcmd mode was included in management studio which provides the ability to run queries and command line arguments in the same query window.  In order to take advantage of this feature then sqlcmd mode must be enabled for each query, unless this has been set as the default option for new queries.  To enable sqlcmd mode simply click the icon on the menu bar:

or set all new query tabs to use SQLCMD Mode by default from Tools>Options>Query Execution> SQL Server>General

Once enabled then you are able to pass t-sql queries as well as command line arguments by prefacing them with “!!”:

By executing the above in sqlcmd mode the results would look like this:

Messages Tab

Results Tab

Returning to the question at hand, “How can a query file be executed from SSMS without first being loaded”? 

Once in sqlcmd mode then using the script keyword “:r” followed by the file path and name will execute the query file without loading it into SSMS:

Once interesting fact that is pointed out in BOL is that the sqlcmd command line interface uses the OLE DB provider while sqlcmd mode uses .NET Framework SQLClient.  The on-line documentation outlining the full syntax and use of SQLCMD Mode can be found on MSDN here.