/Katmai_October2009_Refresh3/Administrator/ascmd/CS/ascmd/ascmd.cs

Large files files are truncated, but you can click here to view the full file

//=====================================================================

//

//  File:      ascmd.cs

//  Summary:   A command-line utility to execute Analysis Services XMLA scripts

//             or MDX queries

//  Authors:   Dave Wickert (dwickert@microsoft.com)

//  Date:	   03-Jan-2006

//

//  Change history:

//    -Jul-2007: EricJ    : Added input parsing from xml format.

//                          Added -oResultStat for output result statistics.

//                          Added RandomSeed, ThinkTimeMin, ThinkTimeMax, ConnectWaitMin, ConnectWaitMax.

//  07-Apr-2008: dwickert : Added scripting variable support for RandomSeed, ThinkTimeMin, 

//                          ThinkTimeMax, ConnectWaitMin, ConnectWaitMax.

//  10-Apr-2008: dwickert : Added -Xf (exit file) option with matching scripting variable support

//  18-Aug-2008: dwickert : integrated and prep'ed for 2008 support

//  20-Aug-2008: dwickert : Released SQL 2008 beta1

//  27-Aug-2008: dwickert : Fixed bug in -U logon processing (was disposing of token handle early)

//  28-Aug-2008: dwickert : Finalized support SQL 2008 (+ various naming issues that fxcop found)

//  02-Sep-2008: dwickert : Fixed bug with input file name as query name when multiple input files used



// @TODO: (1) add InputPatternBegin, InputPatternEnd

// @TODO: (2) add support for "go;" with semicolon

// @TODO: (3) Consider moving ResultStat to separate utility

// @TODO: (4) Why does it not measure query duration?

// @TODO: (5) Add tag to output to csv file verbatim info, to help create output for excel

//

//---------------------------------------------------------------------

//

//  This file is part of the Microsoft SQL Server Code Samples.

//  Copyright (C) Microsoft Corporation.  All rights reserved.

//

//This source code is intended only as a supplement to Microsoft

//Development Tools and/or on-line documentation.  See these other

//materials for detailed information regarding Microsoft code samples.

//

//THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 

//ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO 

//THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A

//PARTICULAR PURPOSE.

//

//===================================================================== 



// Note: the code below contains additional lines to check for strong

// naming convention of obects, e.g. server names, usernames, passwords, etc.

// However, since we don't know what you guidelines will be we were not

// able to assume your specific values. Free from to change ParseArgs and add

// minimum, maximum and invalid object characters based on your 

// individual naming conventions.



// Line length (limit to 79 characters for formatting)

//       1         2         3         4         5         6         7

//34567890123456789012345678901234567890123456789012345678901234567890123456789



using System;

using System.Web;

using System.Collections.Generic;

using System.Diagnostics;

using System.IO;

using System.Reflection;

using System.Text;

using System.Text.RegularExpressions;

using System.Xml;

using System.Globalization;

using System.Threading;

using Microsoft.Samples.SqlServer.Properties; // the locSting resources



using Microsoft.AnalysisServices;             //AMO

using Microsoft.AnalysisServices.AdomdClient; //ADOMD.NET



// needed for Logon Win32 API call and user impersonation //

using System.Runtime.InteropServices;

using System.Security.Principal;

using System.Security.Permissions;



// needed for random number generation

using Microsoft.Samples.SqlServer.ASCmd.RandomHelper;



[assembly: CLSCompliant(true)]

namespace Microsoft.Samples.SqlServer.ASCmd

{

    internal static class NativeMethods

    {

        [DllImport("advapi32.dll", CharSet = CharSet.Unicode, SetLastError = true)]

        [return: MarshalAs(UnmanagedType.Bool)]

        public static extern bool LogonUser(String lpszUsername, String lpszDomain, String lpszPassw_outord,

            int dwLogonType, int dwLogonProvider, ref IntPtr phToken);



        [DllImport("kernel32.dll", CharSet = CharSet.Auto, SetLastError = true)]

        [return: MarshalAs(UnmanagedType.Bool)]

        public static extern bool CloseHandle(IntPtr handle);

    }



    class CmdMain

    {



        #region Assembly Attribute Accessors



        //static string AssemblyTitle // not used yet

        //{

        //    get

        //    {

        //        // Get all Title attributes on this assembly

        //        object[] attributes = System.Reflection.Assembly.GetExecutingAssembly().GetCustomAttributes(typeof(AssemblyTitleAttribute), false);

        //        // If there is at least one Title attribute

        //        if (attributes.Length > 0)

        //        {

        //            // Select the first one

        //            AssemblyTitleAttribute titleAttribute = (AssemblyTitleAttribute)attributes[0];

        //            // If it is not an empty string, return it

        //            if (titleAttribute.Title != "")

        //                return titleAttribute.Title;

        //        }

        //        // If there was no Title attribute, or if the Title attribute was the empty string, return the .exe name

        //        return System.IO.Path.GetFileNameWithoutExtension(System.Reflection.Assembly.GetExecutingAssembly().CodeBase);

        //    }

        //}



        static string AssemblyVersion

        {

            get

            {

                return System.Reflection.Assembly.GetExecutingAssembly().GetName().Version.ToString();

            }

        }



        static string AssemblyDescription

        {

            get

            {

                // Get all Description attributes on this assembly

                object[] attributes = System.Reflection.Assembly.GetExecutingAssembly().GetCustomAttributes(typeof(AssemblyDescriptionAttribute), false);

                // If there aren't any Description attributes, return an empty string

                if (attributes.Length == 0)

                    return "";

                // If there is a Description attribute, return its value

                return ((AssemblyDescriptionAttribute)attributes[0]).Description;

            }

        }



        static string AssemblyProduct // not used yet

        {

            get

            {

                // Get all Product attributes on this assembly

                object[] attributes = System.Reflection.Assembly.GetExecutingAssembly().GetCustomAttributes(typeof(AssemblyProductAttribute), false);

                // If there aren't any Product attributes, return an empty string

                if (attributes.Length == 0)

                    return "";

                // If there is a Product attribute, return its value

                return ((AssemblyProductAttribute)attributes[0]).Product;

            }

        }



        static string AssemblyCopyright

        {

            get

            {

                // Get all Copyright attributes on this assembly

                object[] attributes = System.Reflection.Assembly.GetExecutingAssembly().GetCustomAttributes(typeof(AssemblyCopyrightAttribute), false);

                // If there aren't any Copyright attributes, return an empty string

                if (attributes.Length == 0)

                    return "";

                // If there is a Copyright attribute, return its value

                return ((AssemblyCopyrightAttribute)attributes[0]).Copyright;

            }

        }



        //static string AssemblyCompany // not used yet

        //{

        //    get

        //    {

        //        // Get all Company attributes on this assembly

        //        object[] attributes = System.Reflection.Assembly.GetExecutingAssembly().GetCustomAttributes(typeof(AssemblyCompanyAttribute), false);

        //        // If there aren't any Company attributes, return an empty string

        //        if (attributes.Length == 0)

        //            return "";

        //        // If there is a Company attribute, return its value

        //        return ((AssemblyCompanyAttribute)attributes[0]).Company;

        //    }

        //}



        static string AssemblyProcessorArchitecture

        {

            get

            {

                return System.Reflection.Assembly.GetExecutingAssembly().GetName().ProcessorArchitecture.ToString();

            }

        }



        #endregion



        #region Class Variables



        // message handling for loc strings

        static string Msg(string locString) { return (AssemblyProduct + ": " + locString); }



        // What options have been seen on the command line

        static bool Option_U_specified;

        static bool Option_P_specified;

        static bool Option_S_specified;

        static bool Option_d_specified;

        static bool Option_t_specified;

        static bool Option_tc_specified;

        static bool Option_i_specified;

        static bool Option_o_specified;

        static bool Option_oResultStat_specified;

        static bool Option_NoResultStatHeader_specified;

        static bool Option_RunInfo_specified;

        static bool Option_RandomSeed_specified;

        static bool Option_ThinkTimeMin_specified;

        static bool Option_ThinkTimeMax_specified;

        static bool Option_ConnectWaitMin_specified;

        static bool Option_ConnectWaitMax_specified;

        static bool Option_T_specified;

        static bool Option_Tt_specifed;

        static bool Option_Tf_specified;

        static bool Option_Tl_specified;

        static bool Option_Td_specified;

        static bool Option_Q_specified;

        static bool Option_xc_specified;

        static bool Option_v_specified;

        static bool Option_Xf_specified;



        // Variables for options

        static string UserName = "";

        static string Domain = "";

        static string Password = "";

        static string Server = "localhost";

        static string Instance = "";

        static string Database = "";

        static string InputFile = "";

        static string OutputFile = "";

        static string OutputResultStatFile = "";

        static string RunInfo = "";

        static string TraceFile = "";

        static string Query = "";

        static string ExtendedConnectstring = "";

        static string TraceFormat = "csv"; // use the -f option

        static string Timeout = "";

        static string ConnectTimeout = "";

        static string TraceTimeout = "5"; // trace is finished if no activity for 5 seconds

        static bool httpConnection;

        static int RandomSeed;

        static int ThinkTimeMin;

        static int ThinkTimeMax;

        static int ConnectWaitMin;

        static int ConnectWaitMax;

        static string ExitFile = ""; // exit file (if specified)



        // provider to use -- should we have this an an option (i.e. -p) ??

        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Performance", "CA1823:AvoidUnusedPrivateFields")]

        const string Provider = "MSOLAP.3"; // force AS2K5 provider



        // Variables for read/writing

        static StringBuilder sb = new StringBuilder(); // input stream

        static StreamWriter sw_out; //Output file

        static StreamWriter sw_outResultStat; //Output file

        static bool IsOutputFileOpen;

        static bool IsOutputResultStatFileOpen;

        static StreamWriter sw_trace; //Trace file

        static bool IsTraceFileOpen;

        static string TraceDelim = "|";

        static string TraceLevel = "high"; // default is to trace at the high level

        static long BeginEndBlockCount; // if 0 then OK to immediately finish trace

        static int TraceTimeoutCount; // if goes to 0, then finish trace regardless of BeginEndBlockCount

        static int TraceTimeoutCountReset; // the amount of time to wait once trace events stop

        static bool TraceStarted; // have the trace events started to flow?

        const int PollingInterval = 20; // Every 1/20th of a second (50ms) see if trace is done (BeginEndBlockCount drives to 0)

        static bool ExceptionSeen; // have we seen an exception in the xmla return stream



        static bool _DEBUGMODE; // Set to indicate that we need debug messages outputted



        static HighPerformanceRandom perfRandom;



        // substitution table -- includes -v entries plus command-line arguments

        static Dictionary<string, string> substituteWith =

            new Dictionary<string, string>(StringComparer.CurrentCultureIgnoreCase);



        // maximum timeout values (if used)

        const int maxTimeout = -1; // -1 means don't check



        // regular expressions used

        const string ScriptingVarRefRegex = @"\$\([a-zA-Z0-9_-]+\)";

        const string ScriptingVarNameRegex = @"^[a-zA-Z0-9_-]+=.*";

        const string XMLACommandRegex =

                        @"(?sx)^(<Alter.*|

                                 <Attach.*|

                                 <Backup.*|

                                 <Batch.*|

                                 <BeginTransaction.*|

                                 <Cancel.*|

                                 <ClearCache.*|

                                 <CommitTransaction.*|

                                 <Create.*|

                                 <Delete.*|

                                 <DesignAggregations.*|

                                 <Detach.*|

                                 <Drop.*|

                                 <Insert.*|

                                 <Lock.*|

                                 <MergePartitions.*|

                                 <NotifyTableChange.*|

                                 <Process.*|

                                 <Restore.*|

                                 <RollbackTransaction.*|

                                 <SetPasswordEncryptionKey.*|

                                 <Statement.*|

                                 <Subscribe.*|

                                 <Synchronize.*|

                                 <Unlock.*|

                                 <Update.*|

                                 <UpdateCells.*)$";

        const string DiscoverRegex = @"(?sx)^<Discover.*$";

        const string ExecuteRegex = @"(?sx)^<Execute.*$";

        const string BatchRegex = @"^[\w\W]*?[\r\n]*go\s";

        const string InputFileFormat = "**InputFile: {0}\n";

        const string InputFileRegex = @"(?sx)^\*\*InputFile: (?<inputFile>[\w\W]+?)\n(?<batchText>.*)$";



        // formats

        const string SoapEnvFormat =

            @"<Envelope xmlns='http://schemas.xmlsoap.org/soap/envelope/'>

                <Body>

                {0}

                </Body>

              </Envelope>";



        #endregion



        // --------------------------------------------------------------------

        // Main routine -- called with the command line arguments

        [SecurityPermission(SecurityAction.Assert, Flags = SecurityPermissionFlag.UnmanagedCode)]

        static int Main(string[] args)

        {

            //HighPerformanceRandom.TestShowWithSeeds();

            //HighPerformanceRandom.Test2();

            try

            {

                Console.WriteLine(AssemblyDescription);

                Console.WriteLine(Properties.Resources.locVersion,

                            AssemblyVersion, AssemblyProcessorArchitecture);

                Console.WriteLine(AssemblyCopyright);



                // Parse the command line argument list, verify the options and

                // open any requested files

                try

                {

                    if (!ParseArgs(args))

                    {

                        return 1; // if it returns an error, just exit

                    }

                }

                catch (Exception ex)

                {

                    Console.WriteLine(Msg(Properties.Resources.locParseArgsErr),

                                ex.Message);

                    return 1; // error raised

                }



                // Take the input stream and substitute any scripting variables within it

                SubstituteScriptingVariables(sb);



                // Impersonate the -U username (if needed) and then **execute** the

                // query or script. Note: http connections set the UID and PWD in

                // the connect string

                if ((UserName.Length > 0) && !httpConnection)

                {

                    try

                    {

                        // Need to impersonate the user first -- then ex



                        const int LOGON32_PROVIDER_DEFAULT = 0;

                        //This parameter causes LogonUser to create a primary token.

                        const int LOGON32_LOGON_INTERACTIVE = 2;



                        IntPtr tokenHandle = new IntPtr(0);

                        tokenHandle = IntPtr.Zero;



                        // Call LogonUser to obtain a handle to an access token.

                        if (_DEBUGMODE) Console.WriteLine("Calling LogonUser");

                        bool returnValue = NativeMethods.LogonUser(UserName, Domain, Password,

                            LOGON32_LOGON_INTERACTIVE, LOGON32_PROVIDER_DEFAULT,

                            ref tokenHandle);



                        if (false == returnValue)

                        {

                            int ret = Marshal.GetLastWin32Error();

                            Console.WriteLine(Msg(Properties.Resources.locLogonFailedErr), ret);

                            throw new System.ComponentModel.Win32Exception(ret);

                        }



                        if (_DEBUGMODE) Console.WriteLine("Did LogonUser Succeed? " + (returnValue ? "Yes" : "No"));

                        if (_DEBUGMODE) Console.WriteLine("Value of Windows NT token: " + tokenHandle);



                        // Check the identity.

                        if (_DEBUGMODE) Console.WriteLine("Before impersonation: " + WindowsIdentity.GetCurrent().Name);



                        // Impersonate using the token handle returned by LogonUser.

                        using (WindowsIdentity newId = new WindowsIdentity(tokenHandle))

                        {

                            WindowsImpersonationContext impersonatedUser = newId.Impersonate();



                            ExecuteInput(ConnectString); // execute it



                            impersonatedUser.Undo(); // which reverts to self

                        }

                        if (tokenHandle != IntPtr.Zero) NativeMethods.CloseHandle(tokenHandle);



                        // Check the identity.

                        if (_DEBUGMODE) Console.WriteLine("After impersonation: " + WindowsIdentity.GetCurrent().Name);



                    }

                    catch (Exception ex)

                    {

                        Console.WriteLine(Msg(Properties.Resources.locImpersonateErr), ex.Message);

                        throw;

                    }

                }

                else // no impersonation needed

                {

                    try

                    {

                       ExecuteInput(ConnectString); // execute it

                    }

                    catch (Exception ex)

                    {

                        Console.WriteLine(Msg(Properties.Resources.locImpersonateErr), ex.Message);

                        throw;

                    }

                }

                CloseFiles();

            }

            catch (Exception ex)

            {

                Console.WriteLine(Msg(Properties.Resources.locFailedErr), ex.Message);

                return 1; // error raised

            }

            if (Option_o_specified && ExceptionSeen)

                Console.WriteLine(Msg(Properties.Resources.locCheckOutputForErr));

            return (ExceptionSeen) ? 1 : 0;

        }



        // Regex match against input string??

        private static bool InputMatch(string regexString, StringBuilder sb)

        {

            string s = sb.ToString();

            bool b = Regex.IsMatch(s, regexString);

            return b;

        }



        // Rest of the functions below are supporting routines.



        // --------------------------------------------------------------------

        // Supporting routine -- perform scripting variable substitution

        private static void SubstituteScriptingVariables(StringBuilder sb)

        {

            Regex expression = new Regex(ScriptingVarRefRegex);



            foreach (Match m in expression.Matches(sb.ToString()))

            {

                // Pick out just the scripting variable name by remove leading and

                // trailing characters of the scripting variable

                string name = m.ToString().Replace("$(", "").Replace(")", "");



                // Perform the substitution of scripting variable to its value

                if (substituteWith.ContainsKey(name))

                {

                    // -v or system-reserved replacement

                    sb.Replace(m.ToString(), substituteWith[name]);

                }

                else if ((Environment.GetEnvironmentVariable(name) != null) &&

                        (!name.StartsWith("ASCMD", StringComparison.CurrentCultureIgnoreCase)))

                {

                    // environment variable replacement (cannot be used for system-reserved)

                    sb.Replace(m.ToString(), Environment.GetEnvironmentVariable(name));

                }

                else

                {

                    // no match found, so eliminate the scripting variable by

                    // replacing it with a blank string

                    sb.Replace(m.ToString(), "");

                }

            }

            if (_DEBUGMODE) Console.WriteLine("After substitution, final input is: " + sb.ToString());

        }



        // --------------------------------------------------------------------

        // Supporting routine -- determine what the connect string should be

        private static string ConnectString

        {

            get

            {

                string cs = "";



                // Build the connect string as required

                // Note: some parameters might have legimite embedded doublequotes "

                //       if so, we marked them with // ** tag

                cs += "Provider=" + Provider;

                if (httpConnection)

                {

                    cs += ";Data Source=\"" + Server + "\"";

                }

                else

                {

                    cs += ";Data Source=\"" + Server;

                    if (Instance.Length > 0) cs += "\\" + Instance;

                    cs += "\"";

                }

                if (Database.Length > 0) cs += ";Database=\"" + Database.Replace("\"", "\"\"") + "\""; //**



                // Add http information, if requested

                if (httpConnection && (UserName.Length > 0))

                {

                    cs += ";UID=\"" + ((Domain.Length > 0) ? Domain + "\\" + UserName : UserName) + "\"";

                    cs += ";PWD=\"" + Password.Replace("\"", "\"\"") + "\""; // **

                }



                // Add timeout information, if requested

                if (Timeout.Length > 0)

                    cs += ";Timeout=" + Timeout.ToString(CultureInfo.CurrentCulture);

                if (ConnectTimeout.Length > 0)

                    cs += ";Connect Timeout=" + ConnectTimeout.ToString(CultureInfo.CurrentCulture);



                // Add extended connectstring option

                if (ExtendedConnectstring.Length > 0)

                    cs += ";" + ExtendedConnectstring.Replace("\"", "\"\""); // **



                // Finally, add our application name :-)

                cs += ";SspropInitAppName=" + AssemblyProduct;



                if (_DEBUGMODE) Console.WriteLine("Connect string: {0}", cs);



                return cs;

            }

        }



        // --------------------------------------------------------------------

        // Execute an input statement (MDX query or XMLA script)

        // *** This is the real work of the program -- the rest is supporting functions ***

        private static void ExecuteInput(string ConnectionString)

        {

            // Wait a random time before connecting.

            // The purpose is during a multi-user load test,

            // to gradually ramp up and overcome potential problems of not being able

            // to connect all clients.

            

            // fix up limits of connect time

            if (ConnectWaitMin < 0) ConnectWaitMin = 0;

            if (ConnectWaitMax < ConnectWaitMin) ConnectWaitMax = ConnectWaitMin;

            if (perfRandom == null) perfRandom = new HighPerformanceRandom(RandomSeed);



            // if we have connect time, then wait it

            if (ConnectWaitMax > 0)

            {

                int iSleepTime = perfRandom.Next(ConnectWaitMin * 1000, ConnectWaitMax * 1000);

                if (_DEBUGMODE) Console.WriteLine("ConnectWait, begin sleeping {0} millisec.", iSleepTime);

                System.Threading.Thread.Sleep(iSleepTime);

                if (_DEBUGMODE) Console.WriteLine("ConnectWait, end sleeping.");

            }

            

            Server oServer = new Server();

            //Open up a connection

            try

            {

                oServer.Connect(ConnectionString);

            }

            catch (Exception ex)

            {

                Console.WriteLine(Msg(Properties.Resources.locAmoConnErr), ConnectionString, ex.Message);

                throw;

            }



            // Try to execute the query/script

            try

            {

                // Setup to trace events during execution (only if level is not "duration")

                if (Option_T_specified && !(TraceLevel == "duration") && !(TraceLevel == "duration-result"))

                {

                    // Initialize trace handler

                    oServer.SessionTrace.OnEvent += new TraceEventHandler(SessionTrace_OnEvent);



                    //Write some status stuff

                    if (_DEBUGMODE) Console.WriteLine("Trace connection status to " + Server + " := " + oServer.Connected.ToString(CultureInfo.CurrentCulture));

                    if (_DEBUGMODE) Console.WriteLine("Server := " + oServer.Name + " (Session:" + oServer.SessionID.ToString(CultureInfo.CurrentCulture) + ")");



                    // Start the trace

                    oServer.SessionTrace.Start();

                }



                // Execute the input batches

                ExecuteBatches(oServer);



                // Stop the trace (if running)            

                if (!oServer.SessionTrace.IsStarted) oServer.SessionTrace.Stop();



            }

            catch (Exception ex)

            {

                Console.WriteLine(Msg(Properties.Resources.locAdomdExecutionErr), ex.Message);

                oServer.Dispose();

                throw;

            }



            //Disconnect and end the session on the server

            try

            {

                oServer.Disconnect(true);

            }

            catch (Exception ex)

            {

                Console.WriteLine(Msg(Properties.Resources.locAmoDisconnErr), ex.Message);

                throw;

            }



            oServer.Dispose();

        }



        // --------------------------------------------------------------------

        // Execute the batches in the input stream (sb)

        //   A batch is a portion of the input stream separated by "GO" commands, i.e.

        //      <batch>

        //      GO

        //      <batch>

        //      GO

        // 

        private static void ExecuteBatches(Server oServer)

        {

            Regex expression = new Regex(BatchRegex,

                RegexOptions.Compiled | RegexOptions.IgnoreCase | RegexOptions.Multiline);



            // pickup the input stream, append a GO command and parse it

            MatchCollection matches = expression.Matches(sb.ToString() + "\ngo\n");

            if (matches.Count > 1) Output(null, DateTime.Now, DateTime.Now, "<multiple-batches>");



            bool DidFirstQuery = false;

            foreach (Match m in matches)

            {

                // Wait a random think time after each query.

                // The purpose is during a multi-user load test.

                if (DidFirstQuery)

                {

                    // Does the exit file exist? If so, break out of the foreach Match loop early

                    if (File.Exists(ExitFile)) break;



                    // Fix up limits of think time

                    if (ThinkTimeMin < 0) ThinkTimeMin = 0;

                    if (ThinkTimeMax < ThinkTimeMin) ThinkTimeMax = ThinkTimeMin;

                    if (perfRandom == null) perfRandom = new HighPerformanceRandom(RandomSeed);



                    // If we have a think time, then wait it

                    if (ThinkTimeMax > 0)

                    {

                        int iSleepTime = perfRandom.Next(ThinkTimeMin * 1000, ThinkTimeMax * 1000);

                        if (_DEBUGMODE) Console.WriteLine("ThinkTime, begin sleeping {0} millisec.", iSleepTime);

                        System.Threading.Thread.Sleep(iSleepTime);

                        if (_DEBUGMODE) Console.WriteLine("ThinkTime, end sleeping.");

                    }

                }



                // Pick out just the scripting variable name by remove leading and

                // trailing characters of the scripting variable

                string fullText = m.Value;

                StringBuilder batch = new StringBuilder(fullText.Substring(0, fullText.Length - 3).Trim());



                if (_DEBUGMODE) Console.WriteLine("Batch input is: " + batch.ToString());

                if (batch.Length > 0) ExecuteBatch(oServer, InputFileMarker(batch)); // fire the batch

                DidFirstQuery = true;



            } // loop for next batch



            if (matches.Count > 1) Output(null, DateTime.Now, DateTime.Now, "</multiple-batches>");

        }



        private static StringBuilder InputFileMarker(StringBuilder inp)

        {

            try

            {

                Regex expression = new Regex(InputFileRegex,

                        RegexOptions.Compiled | RegexOptions.IgnoreCase); // | RegexOptions.Multiline);



                // pickup the input stream, append a GO command and parse it

                MatchCollection matches = expression.Matches(inp.ToString());



                if (matches.Count != 1) return inp;



                InputFile = matches[0].Groups["inputFile"].ToString();

                StringBuilder sb = new StringBuilder(matches[0].Groups["batchText"].ToString());



                if (_DEBUGMODE) Console.WriteLine("Setting Input File to: {0}\nSetting batch to: {1}", InputFile, sb.ToString());



                return sb;

            }

            catch (Exception ex)

            {

                Console.WriteLine("Parse error: {0}", ex.Message);

                throw;

            }

        }



        // Now execute the batch itself (which is the lowest level of command to ascmd)

        private static void ExecuteBatch(Server oServer, StringBuilder inp)

        {

            // The result string for the execution

            string Result;



            // Wrap a stopwatch around execution

            DateTime StartTime = DateTime.Now;

            DateTime EndTime = DateTime.Now; 

            Stopwatch sw = new Stopwatch();



            // If this is not an XMLA command (or Discover/Execute raw XMLA request) then

            // we assume that it is an un-encoded Statement without the XML tag.

            // This allows the end-user the convience of just entering a MDX or DMX statement

            // and we will wrap it for them.

            if (!InputMatch(XMLACommandRegex, inp) && !InputMatch(DiscoverRegex, inp) && !InputMatch(ExecuteRegex, inp))

            {

                string stmt = HttpUtility.HtmlEncode(inp.ToString());

                // Recreate in Input string -- wrapping with <Statement> tags

                inp = new StringBuilder(stmt.Length); // allocates space for new batch -- nothing in it

                inp.AppendFormat("<Statement>{0}</Statement>", stmt); // adds the batch input itself

            }



            // There are two different ways to execute the input: a raw XMLA request or an XMLA command.

            // If this is a raw request, then the batch starts with "Discover" or "Execute", else it is an XMLA command

            if (InputMatch(DiscoverRegex, inp) || InputMatch(ExecuteRegex, inp))

            {

                //--------------------------------------------------------------------------------

                // A raw XMLA request:

                // To run a custom full SOAP Envelope request on Analysis Services server, we

                // need to follow 5 steps:

                // 

                // 1. Start the XML/A request and specify its type (Discover or Execute).

                //    For native connections (direct over TCP/IP with DIME protocol), local 

                //    cube connections and stored procedures connections we don't need to

                //    specify the XML/A request type in advance (an Undefined value is

                //    available). But for HTTP and HTTPS connections we need to.

                //

                // 2. Write the xml request (as an xml soap envelope containing the command

                //    for Analysis Services).

                //

                // 3. End the xml request; this will send the previously written request to the 

                //    server for execution.

                //

                // 4. Read/Parse the xml response from the server (with System.Xml.XmlReader).

                //

                // 5. Close the System.Xml.XmlReader from the previous step, to release the 

                //    connection to be used after.

                //--------------------------------------------------------------------------------



                XmlaRequestType r = XmlaRequestType.Undefined;

                if (InputMatch(DiscoverRegex, inp)) r = XmlaRequestType.Discover;

                if (InputMatch(ExecuteRegex, inp)) r = XmlaRequestType.Execute;



                // Wrap the request with a soap envelope and body

                string s = inp.ToString();

                s = String.Format(CultureInfo.CurrentCulture, SoapEnvFormat, s);



                // Start the stopwatch

                StartTime = DateTime.Now;

                sw.Start();



                // Execute the query/script and gather the results

                XmlWriter _xw = oServer.StartXmlaRequest(r);

                _xw.WriteRaw(s);

                using (XmlReader _xr = oServer.EndXmlaRequest())

                {

                    // Stop the stopwatch

                    sw.Stop();

                    EndTime = DateTime.Now; 



                    // Move in the reader to where the real content begins

                    _xr.MoveToContent();



                    // Skip past the soap envelope and body (if they exist)

                    if (!_xr.EOF && (_xr.Name == "soap:Envelope")) _xr.Read();

                    if (!_xr.EOF && (_xr.Name == "soap:Body")) _xr.Read();



                    // Gather the results and output them

                    Result = GatherResults(_xr);



                    // Close the System.Xml.XmlReader, to release the connection for

                    // future use.

                    _xr.Close();

                }

            }

            else

            {

                //--------------------------------------------------------------------------------

                // An XMLA Command request:



                StartTime = DateTime.Now;

                sw.Start(); // start the stopwatch

                XmlaResultCollection _xrc = oServer.Execute(inp.ToString());

                sw.Stop();

                EndTime = DateTime.Now;



                // Gather the results and output them

                Result = GatherResults(_xrc);

            }

            // Output the result

            Output(sw, StartTime, EndTime, Result);



            // If requested, wait for tracing to finish

            if (Option_T_specified)

            {

                if ((TraceLevel == "duration") || (TraceLevel == "duration-result"))

                {

                    DurationTrace(sw, Result);

                }

                else

                {

                    // is high, medium, low

                    WaitForTraceToFinish();

                }

            }

        }



        // --------------------------------------------------------------------

        // Supporting routines -- gather the results



        private static string GatherResults(XmlReader Results)

        {

            string s = Results.ReadOuterXml();



            // -----------------------------------

            // Look for errors.

            // -----------------------------------

            // As an initial pass, we'll just look for some special tags in the

            // stream. This works because the underlying data in the xml stream

            // is html encoded -- thus the only tags that should exist is if there

            // are errors

            if (s.Contains("</soap:Fault>") ||   // xsd-detected errors

                s.Contains("</Exception>")      // server-detected errors

                )

            {

                ExceptionSeen = true; // tell the main program that we saw errors

            }



            return s;

        }



        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Performance", "CA1800:DoNotCastUnnecessarily")]

        private static string GatherResults(XmlaResultCollection Results)

        {

            // XML namespace constants used

            const string xmlns_xmla = "xmlns=\"urn:schemas-microsoft-com:xml-analysis\"";

            const string xmlns_multipleresults = "xmlns=\"http://schemas.microsoft.com/analysisservices/2003/xmla-multipleresults\"";

            const string xmlns_mddataset = "xmlns=\"urn:schemas-microsoft-com:xml-analysis:mddataset\"";

            const string xmlns_xsi = "xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\"";

            const string xmlns_xsd = "xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\"";

            const string xmlns_empty = "xmlns=\"urn:schemas-microsoft-com:xml-analysis:empty\"";

            const string xmlns_exception = "xmlns=\"urn:schemas-microsoft-com:xml-analysis:exception\"";



            // Format strings constants used

            const string fs_Error = "<Error ErrorCode=\"{0}\" Description=\"{1}\" Source=\"{2}\" HelpFile=\"{3}\" />";

            const string fs_Warning = "<Warning WarningCode=\"{0}\" Description=\"{1}\" Source=\"{2}\" HelpFile=\"{3}\" />";



            // Start to build the output

            StringBuilder _output = new StringBuilder(

                String.Format(CultureInfo.CurrentCulture, "<return {0}>", xmlns_xmla));



            // If there are multiple resultsets, then add the grouping element 

            if (Results.Count > 1)

                _output.Append(String.Format(CultureInfo.CurrentCulture,

                    "<results {0}>", xmlns_multipleresults));



            // loop through each result in the result set

            foreach (XmlaResult _xr in Results)

            {

                // Is there a value for this result?

                if (_xr.Value.Length > 0)

                {   // Yes, indicate its type 

                    _output.Append(String.Format(CultureInfo.CurrentCulture,

                        "<root {0} {1} {2}>", xmlns_mddataset, xmlns_xsi, xmlns_xsd));

                    _output.Append(_xr.Value); // include the value in the stream

                }

                else

                {   // Nope, output the empty set for the root

                    _output.Append(String.Format(CultureInfo.CurrentCulture,

                        "<root {0}>", xmlns_empty));

                }



                // Do we have some messages associated with the result? If so, output them

                if (_xr.Messages.Count > 0)

                {

                    if (ErrorsExist(_xr))

                        _output.Append(String.Format(CultureInfo.CurrentCulture,

                            "<Exception {0} />", xmlns_exception));



                    // Output the messages

                    _output.Append(String.Format(CultureInfo.CurrentCulture,

                        "<Messages {0}>", xmlns_exception));

                    foreach (XmlaMessage _xm in _xr.Messages)

                    {

                        if (_xm is XmlaError) // determine type type

                        {

                            ExceptionSeen = true;    // ERROR bubbles up for the "main" return value



                            int ErrorCode = ((XmlaError)_xm).ErrorCode;

                            _output.Append(String.Format(CultureInfo.CurrentCulture, fs_Error,

                                ((uint)ErrorCode).ToString(CultureInfo.CurrentCulture),

                                HttpUtility.HtmlEncode(_xm.Description), _xm.Source, _xm.HelpFile));



                        }

                        else

                        {

                            int WarningCode = ((XmlaWarning)_xm).WarningCode;

                            _output.Append(String.Format(CultureInfo.CurrentCulture, fs_Warning,

                                ((uint)WarningCode).ToString(CultureInfo.CurrentCulture),

                                HttpUtility.HtmlEncode(_xm.Description), _xm.Source, _xm.HelpFile));

                        }

                    }

                    _output.Append("</Messages>");

                }

                _output.Append("</root>");

            }

            if (Results.Count > 1) _output.Append("</results>");



            _output.Append("</return>");



            // Return the string we've constructed

            return _output.ToString();

        }



        private static bool ErrorsExist(XmlaResult _xr)

        {

            bool ret = false;

            foreach (XmlaMessage _xm in _xr.Messages)

            {

                if (_xm is XmlaError) ret = true;

            }

            return ret;

        }



        private static void WaitForTraceToFinish()

        {

            int delay = Int32.Parse(TraceTimeout, CultureInfo.CurrentCulture);

            TraceTimeoutCount = TraceTimeoutCountReset = delay * PollingInterval; // TraceTimeoutCount is in 1/4 seconds

            // Wait for trace to start to flow

            while (!TraceStarted) { Thread.Sleep(1); } // loop every 20ms waiting for trace to start

            // Wait BeginEndBlockCount becomes 0 or TraceTimeoutCount expires

            while (TraceTimeoutCount > 0)

            { // loop forever until TraceFinished

                if (BeginEndBlockCount == 0) return;

                Thread.Sleep(1000 / PollingInterval); // wait a polling interval

                TraceTimeoutCount--;

            }

            // TraceTimeoutCount expired -- just exit

        }



        // -------------------------------------------------------------------

        // Supporting routines which handle the trace events

        // Remove control characters, i.e. LF, CR, TAB, etc. and replace them with spaces

        private static string RemoveControlChars(string s)

        {

            StringBuilder sb = new StringBuilder(s);

            for (int i = 0; i < s.Length; i++)

            {

                if (Char.IsControl(sb[i]))

                    sb[i] = ' '; // replace all control characters with a space

            }

            return sb.ToString();

        }



        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Maintainability", "CA1502:AvoidExcessiveComplexity")]

        static void SessionTrace_OnEvent(object sender, TraceEventArgs e)

        {

            string EventClass = e.EventClass.ToString();

            //if (_DEBUGMODE) Console.WriteLine("SessionTrace_OnEvent fired");



            TraceStarted = true; // indicated that we have started to see trace events



            // keep the begin-end block count

            if (EventClass.Contains("Begin")) BeginEndBlockCount++;

            if (EventClass.Contains("End") && (BeginEndBlockCount > 0)) BeginEndBlockCount--;



            // based on the trace level, decide if we should record the event

            // high just falls through -- everything is recorded

            if (TraceLevel == "medium" || TraceLevel == "low")

            {

                if ((EventClass == "ProgressReportCurrent") ||

                    //(EventClass == "ProgressReportBegin") || 

                    //(EventClass == "ProgressReportEnd") || 

                    (EventClass == "Notification")) return; // ignore this event

            }

            if (TraceLevel == "low")

            {

                if (!(EventClass.Contains("End") ||

                    EventClass.Contains("Error"))) return; // if EventClass doesn't contain 'End' or 'Error', ignore this event

            }



            switch (TraceFormat)

            {

                case "text":  // a text interpretation of the event

                    {

                        StringBuilder tm = new StringBuilder(); // trace message

                        tm.Append(e.CurrentTime.ToString(CultureInfo.CurrentCulture));

                        tm.Append(", " + e.EventClass.ToString());

                        tm.Append("." + e.EventSubclass.ToString());

                        foreach (TraceColumn tc in Enum.GetValues(typeof(TraceColumn)))

                        {

                            if ((tc.ToString() != "CurrentTime") &&

                                (tc.ToString() != "EventClass") &&

                                (tc.ToString() != "EventSubclass"))

                            {

                                string val = e[tc];

                                if (null != val)

                                {

                                    if (tm.Length != 0) tm.Append(", ");

                                    string v = tc + ": " + RemoveControlChars(val);

                                    tm.Append(v);

                                }

                            }

                        }

                        // Note: For text, output nothing for 'Result' since it is alwasy blank for trace events



                        Trace(tm.ToString()); // write trace line

                        break;

                    }

                case "csv":	// a csv interpreation of the event

                    {

                        StringBuilder tm = new StringBuilder();

                        tm.Append(e.CurrentTime.ToString(CultureInfo.CurrentCulture));

                        tm.Append(TraceDelim + e.EventClass.ToString());

                        tm.Append(TraceDelim + e.EventSubclass.ToString());

                        foreach (TraceColumn tc in Enum.GetValues(typeof(TraceColumn)))

                        {

                            if ((tc.ToString() != "CurrentTime") &&

                                (tc.ToString() != "EventClass") &&

                                (tc.ToString() != "EventSubclass"))

                            {

                                string val = e[tc];

                                if (tm.Length != 0) tm.Append(TraceDelim);

                                if (null != val) tm.Append(RemoveControlChars(val));

                            }

                        }

                        tm.Append(TraceDelim); // For csv, 'Result' is always blank for trace events



                        Trace(tm.ToString()); // write trace line

                        break;

                    }

            }

            TraceTimeoutCount = TraceTimeoutCountReset; // reset the no activity timer

        }



        private static void DurationTrace(Stopwatch sw, string Result)

        {

            string now = DateTime.Now.ToString(CultureInfo.CurrentCulture);

            string Duration = sw.ElapsedMilliseconds.ToString(CultureInfo.CurrentCulture);

            switch (TraceFormat)

            {

                case "text":

                    {

                        StringBuilder tm = new StringBuilder(); // trace message

                        tm.Append(now);

                        tm.Append(", Duration: " + Duration);

                        tm.Append(", DatabaseName: " + Database);

                        tm.Append(", TextData: " + RemoveControlChars(sb.ToString()));

                        tm.Append(", ServerName: " + Server);

                        if (TraceLevel == "duration-result") tm.Append(", Result: " + Result);

                        Trace(tm.ToString()); // write trace line

                        break;

                    }

                case "csv":	// a csv interpreation of the event

                    {

                        StringBu…

Large files files are truncated, but you can click here to view the full file