Readme for Ascmd Command-line Utility Sample

[This topic is pre-release documentation and is subject to change in future releases. Blank topics are included as placeholders.]

This sample works only with SQL Server 2005 and SQL Server 2008. It will not work with any version of SQL Server earlier than SQL Server 2005.
The ascmd command-line utility enables a database administrator to execute an XMLA script, MDX query, or DMX statement against an instance of Microsoft Analysis Services. This command-line utility contains functionality for Analysis Services that resembles the sqlcmd utility included with SQL Server. For more information, see the topic sqlcmd Utility in SQL Server. The execution results of the script, query, or statement can be stored in a file together with relevant SQL Server Profiler trace information. The default install location for the ascmd command-line utility is as follows:
C:\Program Files\Microsoft SQL Server\100\Samples\Analysis Services\Administrator\ascmd

Scenarios

The following scenarios give examples of using the ascmd command-line utility.
Processing a Partition from a Third-Party ToolA database administrator must process partitions and dimensions as part of a nightly extract, transform, and load (ETL) process. The ETL tool is not a SQL Server tool and the database administrator cannot use SQL Server Agent’s built-in support of XMLA scripts or run a SQL Server Integration Services package. The database administrator wants an automated solution that uses the third-party tool. The solution is a command-line utility to run an XMLA script. The utility is then called from the third-party tool. The database administrator downloads and compiles the ascmd command-line utility sample. After compilation, the database administrator can use the ascmd command-line utility to execute XMLA scripts that process partitions and dimensions. Backing Up an OLAP Database from a Third-Party ToolAnother database administrator at the same company is required to automate the back-up of an Analysis Services database. Again, because the scheduling software that the company is using is not a SQL Server tool, the task has to be run from the command line. The database administrator generates the appropriate XMLA script (using SQL Server Management Studio). Then, the third-party scheduling software uses the ascmd command-line utility to run the XMLA script to backup the OLAP database. Using XMLA During an InstallationA developer for an independent software vendor is required to integrate the execution of an XMLA script directly into the installation of the firm's product. The developer must run an XMLA script and retrieve status (and trace events) to know that the Analysis Services database was created correctly. The developer can do this by using the ascmd command-line utility.

Languages

  • C#, the language that ascmd itself is coded in.
  • Batch file commands, which start the ascmd command-line utility.

Prerequisites

To effectively use the ascmd command-line utility, you should have some or all of the following software installed:
Microsoft SQL Server Analysis ServicesAn instance of Analysis Services must be installed and running, because the ascmd command-line utilityis used to connect to an instance of Analysis Services and execute MDX queries, XMLA scripts, and DMX statements. SQL Server Management Studio and Business Intelligence Development StudioThese two work environments provide supporting infrastructure for you to complete any task related to Analysis Services. For any given task, you can approach implementation through the user interface or programmatically. Analysis Management Objects (AMO) AMO is required to execute the ascmd command-line utility on a computer that does not have Analysis Services installed. AMO can be installed from the SQL Server Feature Pack, which can be downloaded from the Microsoft Download Center at http://www.microsoft.com/downloads. .NET Framework 2.0 The .NET Framework 2.0 is required for the ascmd command-line utility to run. It can be downloaded from the Microsoft Download Center at http://www.microsoft.com/downloads. Microsoft Visual Studio 2005 or .NET Framework SDK 2.0We recommended that you use Visual Studio 2005 when you are building or customizing the ascmd sample application. If you do not have Visual Studio 2005 available, use the .NET Framework SDK 2.0. The .NET Framework SDK includes the MSBuild.exe (see the “Compiling the Sample” installation later in this document). The Adventure Works DW database included with SQL ServerThe database samples are useful for experimenting with the ascmd command-line utility. For more information, see Running Setup to Install AdventureWorks Sample Databases and Samples in SQL Server Books Online.An updated version of AdventureWorks database is also available for download on the SQL Server Developer Center Web page.An updated version of the SQL Server Database Engine samples is also available for download on the SQL Server Developer Center Web page.

Arguments

The following arguments are supported at the command line for ascmd.
–Ulogin_idIs the user login ID, which is case-insensitive. Note: the use of login_id is different for sqlcmd and ascmd. In sqlcmd, login_id represents a SQL Server login; for ascmd it represents a Windows login.For TCP/IP access, Analysis Services only supports trusted connections. If the –U parameter is specified (together with the matching password using the –P parameter), the ascmd command-line utility logs on to the Windows operating system using the specified account and then impersonates the account when executing the XMLA script, MDX query, or DMX statement. The login ID must be in the form <domain>\<username>, and the domain must be specified. If –U is not specified, authentication is based on the Windows account of the user who is running the ascmd command-line utility.If an http (or https) connection is specified by the –S parameter, the ascmd command-line utility does not log on to the Windows operating system. Instead, the –U and –P parameters (if present) are included as part of the connect string to the Internet Information Services (IIS) server. Depending on how IIS is configured, the –U and –P parameters can be used for basic authentication. For more information about the “UID” connect string parameter, see AdomdConnection Class in SQL Server Books Online. –PpasswordIs a user-specified matching password to the –U parameter. If the –U parameter is specified and the –P parameter is not specified, the password is assumed to be blank (an empty, zero-length string). If the –P parameter is specified and the –U parameter is not, the –P parameter is ignored.Security Note: Do not use a blank password. Use a strong password. For more information, see Strong Passwords in SQL Server Books Online. The –P parameter password is stored as clear text in the script, query, or statement file; it will be visible to anyone who can see the computer monitor or read the file itself. If you use this feature, put ACLs on the files or use other security techniques to make sure that only trusted users can read the files. –Sserver\instance or –Shttp[s]://server[:port]/virtualdirectory/msmdpump.dllSpecifies the Analysis Services instance to which the ascmd command-line utility will connect and execute. If the –P parameter is not specified, the ascmd command-line utility connects to the default instance of Analysis Services on the local computer that is running TCP (connecting to localhost) and executes the XMLA script, MDX query, or DMX statement. –ddatabaseSpecifies the database against which an MDX query or DMX statement will execute. The –d parameter is ignored when the ascmd command-line utility executes an XMLA script, because XMLA scripts have the database name embedded within the XMLA script. –tquery-timeoutSpecifies the number of seconds before the execution of an XMLA script, MDX query, or DMX statement times out. The ascmd command-line utility adds the TIMEOUT =<query-timeout> clause to the connect string. –tcconnect-timeoutSpecifies the number of seconds before the ascmd connection to the Analysis Services instance times out. The ascmd command-line utility adds the CONNECT TIMEOUT = <connect-timeout> clause to the connect string. {" –iinput-fileIdentifies the file that contains the XMLA script, MDX query, or DMX statement. You must specify a value for either the –i or the –Q parameter when you use the ascmd command-line utility. If you specify no –i or–Q parameter, or specify both of these parameters, an error is generated. Note: Unlike the sqlcmd command-line utility (which can handle multiple input files), the ascmd command-line utility can only process one input file at a time. If you have more than one input file, each one must be called and executed separately.The input file specified with either the –i or the –Q parameter must be a valid XML structure and special characters must be HTML-encoded. For example, when you use an ampersand (&) in your text, it must be encoded as &amp;. So Product.&1922] will be encoded as Product.&amp;1922. Likewise, a less-than sign (<) must be encoded as &lt;, a greater-than sign (>) as &gt;, and double quotation marks (") as &quot;. This is important for MDX queries and DMX statements because the syntax of member keys uses the ampersand character (&). Note: If the input text does not look like it is an XMLA script, that is, it does not start with a valid XMLA command such as <Statement> or <Create> (see the full list later in this document), then the ascmd command-line utility assumes that the text is a <Statement> and HTML encodes the text for you and wraps it in a <Statement> … </Statement> XML element tag. This is done as a convenience so that executing MDX queries and DMX statements are easier. If you want t

Last edited Jun 5, 2007 at 5:30 PM by bonniefe, version 5

Comments

No comments yet.