Authoring of SQLCMD Queries Using SQL Server Management Studio Query Editor
| The SQL Server Management Studio Query Editor has support for SQLCMD scripts authoring and execution. The following section demonstrates how to enable this feature and how to use it. Turning the SQLCMD Feature On and OffWhile you are inside SQL Server Management Studio, SQLCMD mode can be turned on and off for SQL Queries on both a per-window and global basis. To toggle the mode for a particular query window, use the Query>SQLCMD Mode menu item or the corresponding button on the query window toolbar (see Figure 6-3). Figure 6-3. SQL Server Management Studio toolbar. Tips and Tricks If you find this mode useful, you can make it the default for all SQL Query windows. To do that, go to the Tools>Options dialog (see Figure 6-4) and select Query Execution>SQL Server>General category on the left side of the dialog. After that, check the check box at the bottom of the page on the right side of the dialog that says By default, open new queries in SQLCMD mode. Figure 6-4. SQL Server Management Studio Options dialog. While adding support for SQLCMD syntax to Query Editor, the development team envisioned two primary scenarios. One scenario is to make Query Editor more powerful by supporting new commands in addition to GO. The other one is to enable writing and debugging SQLCMD scripts in Query Editor so that they can be executed later from batch files with the SQLCMD.EXE command line tool. Colorizing of SQLCMD Commands and VariablesNow you can try using this mode to author and execute in Query Editor a simple query that selects all columns from a table whose name is specified by a SQLCMD variable. Create a new query window and connect to AdventureWorks. Turn on the SQLCMD mode. After that, type the following in the text editor area of the query window: :setvar tablename HumanResources.Department go select * from $(tablename) go Please note the same rules apply as if we were working in console mode, that is, the command should be the first statement on the line. Notice that the line containing the :setvar SQLCMD command is highlighted. All recognized SQLCMD commands are highlighted this way so that it is clear that the command is not part of T-SQL syntax. SQLCMD Mode Script Execution in Query EditorAny highlighted commands are going to be recognized by the execution engine. If you run a query in the Query Editor, the results appear the same way as if you were to execute it from within the console application. Tips and Tricks Processing of the SQLCMD commands and scripting variables is accomplished via a commonly shared component called BatchParser. Even though parsing of the script goes through a common engine, do not expect query results to be formatted the same way. They are close, but not identical, primarily because the two tools use completely different query execution and formatting engines. If you turn off the SQLCMD mode for this query window you'll notice that the first line is no longer highlightedit means that query editor won't do any special processing for it while executing the batch: :setvar tablename HumanResources.Department go select * from $(tablename) go If you run the query in this mode, execution fails because the SETVAR command is submitted directly to SQL Server Database Engine, and the engine doesn't recognize its syntax. Tips and Tricks Batch terminator, which is also considered a special command in SQLCMD, as mentioned earlier in this chapter, is still recognized by Query Editor even when SQLCMD mode is off. This applies to its supplied argument, as well specifying the number of times the batch is to be executed. It is important to know that any definitions and settings introduced by a SQLCMD command during previous query execution are not carried over and remembered for the next execution. This is a significant difference from the command line tool. Query Editor remembers only settings that are specified with UI elements of SSMS. For example, suppose you execute the following: :setvar tablename HumanResources.Department go If after that you execute the following query separately from the preceding execution, you get an error that the tablename variable is not defined: select * from $(tablename) go A fatal scripting error occurred. Variable tablename is not defined. To correct this problem you need to execute both batches in one shot: :setvar tablename Person.Address select * from $(tablename) go It is important to note that all major Query Editor features are usable while queries execute in SQLCMD mode. For example, you can still parse the text and view the graphical execution plan. However, some particular aspects of the Query Editor behavior are slightly different when you are working in this mode. Some of the SQLCMD commands are not supported at all and some have limitations. SQLCMD Commands Not Supported by Query EditorThe list of SQLCMD commands that are not supported by Query Editor demonstrates the main difference between SQLCMD.exe and SSMS. The former is an interactive command line tool where the interaction with the user happens via Windows console window. The latter has graphical user interfaces and interacts with the user via menu commands and graphical elements. Therefore, not all interactive SQLCMD commands are supported inside Query Editor. What follows is the list of nonsupported commandsQuery Editor issues a warning in the Message tab if it encounters these commands while in SQLCMD mode:
When one of these commands is used in a SQLCMD query, Query Editor issues a non-fatal warning into the Messages tab during execution and continues on. :help select 1 Scripting warning. Command Help is not supported. String was not processed. ------------------ 1 (1 row(s) affected) One other important difference between Query Editor and SQLCMD.EXE is that Query Editor doesn't support built in SQLCMD and OSQL variables. If you try specifying them in the query that you execute inside SSMS you'll see an error about an undefined variable: print '$(SQLCMDSERVER)' go A fatal scripting error occurred. Variable SQLCMDSERVER is not defined. Redirecting Results and Errors in Query EditorQuery Editor still supports the following SQLCMD commands: :out <destination> and :error <destination>. As explained earlier, their purpose is to redirect the output and error messages respectively into a file, stderr, or stdout location. Suppose you're working on a query and you want to always have results in a file separate from error messages that should be in a different file. Assuming you have a c:\output folder, you can do the following: :out c:\output\queryresults.txt :error c:\output\queryerror.txt GO exec sp_who go exec sp_who 'foo' If you execute it, you'll notice that the results of the first sp_who execution will be in the first TXT file, and unless you have a login 'foo,' the error message from the second sp_who execution will be in the second TXT file. Tips and Tricks One important aspect of the Query Editor behavior is that it silently overwrites the files if they exist. So, be sure to back them up as needed. Because SQL Server Management Studio is a UI application and not a command line tool, it can't really support strerr and stdout parameters to these commands. However, it does accept them and redirects subsequent output to the Messages window. For example, executing the following puts the results of exec sp_who 'sa' into the text file and the results of exec sp_who into the Messages window. :out c:\output\queryresults.txt GO exec sp_who 'sa' GO :out stdout GO exec sp_who You may notice that it is similar to the Results to File and Results to Text query execution modes that Query Editor has. The major difference is that by utilizing these commands, you can change the result mode dynamically for different parts of the query. This might be useful for large scripts that do multiple logical operations, for which you want to track results independently. Tips and Tricks The :out command results in both tabular results and the non-error messages being redirected to the specified destination. |
EAN: 2147483647
Pages: 149