Ascmd

Hidden inside SQL Server 2005 is a nifty little command-line utility called ascmd, which lets DBAs and developers execute XMLA, MDX queries, or DMX statements against SQL Server 2005 Analysis Services. You'll remember that in a couple of SQL Server releases, Microsoft included a nice relational-engine command-line utility called sqlcmd. Ascmd gives you the same kind of functionality that sqlcmd does—but for Analysis Services. Ascmd can store the execution results of a script in a file and combine those results with trace information from SQL Server Profiler. The utility was made public in the April 2006 release of SQL Server 2005 Service Pack 1 (SP1).

You can use the ascmd utility for many of the same tasks that you'd perform by using the Analysis Services Execute DDL task in SQL Server Integration Services (SSIS), but ascmd also includes integrated tracing features. For example, Microsoft suggests using the utility as part of a nightly extract, transform, and load (ETL) process. If you need to process partitions and dimensions from a third-party tool, you can automatically invoke ascmd from that tool. Another example scenario might happen if your corporate backup software is provided by a third-party vendor.The corporate-approved software could invoke ascmd in its scheduled tasks to run an XMLA script and back up the Analysis Services database. Or say an independent software vendor (ISV) needs to integrate the execution of an XMLA script into the installation of ts product.The ISV also needs to use trace events to be certain that the Analysis Services database was created properly.The ascmd command-line utility will do the trick perfectly.

Arguments to use with ascmd. If you've used sqlcmd, bcp, or any of the other command-line utilities that ship with SQL Server, you know to look for the commandline arguments that properly enable the functionality of the tool. Ascmd is no different; it, too, makes heavy use of arguments. However, note that although some of the arguments seem familiar to sqlcmd users, many of ascmd's arguments are different in their implementation.You can see a complete list of ascmd's arguments in the readme file for ascmd in SQL Server 2005 Books Online (BOL).

Using variables. Ascmd supports a variety of system-reserved variables and user-defined scripting variables (refer to the -v command-line argument for more information). You can sometimes substitute environment variables for the system-reserved variables. The ascmd readme entry in BOL will help you learn how.

Sample scripts. The latest BOL release contains many samples and examples that you can use with only a tiny amount of tinkering.The samples include:

• Backing up a database in an untrusted domain
• Automating the build and training of a data-mining model
• Batch processing multiple partitions
• Creating a "cache warmer" application and clearing the Analysis Services data cache
• Creating a new database on a server
• Creating a validation procedure on an ETL run
• Discovering when a partition was last processed
• Querying an Analysis Services cube from the command line

You should definitely tinker around with these samples because it's a fast way to learn the basics of ascmd and make it a productive addition to your toolbox.