public static void TraceException(ProcedureContext procedureContext, Exception exception) { #region Argument exceptions if (procedureContext == null) { throw new ArgumentNullException("procedureContext"); } if (exception == null) { throw new ArgumentNullException("exception"); } #endregion var updateCommand = @"UPDATE [asqa].[Execution] SET [ExecutionError] = @executionError WHERE [BatchID] = CAST(@batchID AS UNIQUEIDENTIFIER) AND [ExecutionID] = CAST(@executionID AS UNIQUEIDENTIFIER)"; using (procedureContext.ImpersonationIdentity.Impersonate()) { using (var connection = new SqlConnection(procedureContext.BatchConnectionString)) { connection.Open(); using (var command = new SqlCommand(updateCommand, connection)) { command.Parameters.Add(new SqlParameter("@batchID", SqlDbType.UniqueIdentifier)).Value = procedureContext.BatchID; command.Parameters.Add(new SqlParameter("@executionID", SqlDbType.UniqueIdentifier)).Value = procedureContext.ExecutionID; command.Parameters.Add(new SqlParameter("@executionError", SqlDbType.NVarChar)).Value = exception.ToString(); command.ExecuteNonQuery(); } } } }
public static void Finalize(ProcedureContext procedureContext) { #region Argument exceptions if (procedureContext == null) { throw new ArgumentNullException("procedureContext"); } #endregion var updateCommand = @"UPDATE [asqa].[Execution] SET [ExecutionEndTime] = @executionEndTime WHERE [BatchID] = CAST(@batchID AS UNIQUEIDENTIFIER) AND [ExecutionID] = CAST(@executionID AS UNIQUEIDENTIFIER)"; using (procedureContext.ImpersonationIdentity.Impersonate()) { using (var connection = new SqlConnection(procedureContext.BatchConnectionString)) { connection.Open(); using (var command = new SqlCommand(updateCommand, connection)) { command.Parameters.Add(new SqlParameter("@batchID", SqlDbType.UniqueIdentifier)).Value = procedureContext.BatchID; command.Parameters.Add(new SqlParameter("@executionID", SqlDbType.UniqueIdentifier)).Value = procedureContext.ExecutionID; command.Parameters.Add(new SqlParameter("@executionEndTime", SqlDbType.DateTime)).Value = DateTime.UtcNow; command.ExecuteNonQuery(); } } } }
public static void WriteToServer(ProcedureContext procedureContext, IDataReader reader, string destinationTableName, string beforeActionCommandText = null, string afterActionCommandText = null) { using (procedureContext.ImpersonationIdentity.Impersonate()) { using (var connection = new SqlConnection(procedureContext.BatchConnectionString)) { connection.Open(); if (beforeActionCommandText != null) { using (var command = connection.CreateCommand()) { command.CommandText = beforeActionCommandText; command.ExecuteNonQuery(); } } // http://www.sqlbi.com/wp-content/uploads/SqlBulkCopy-Performance-1.0.pdf using (var bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.TableLock, externalTransaction: null)) { var fields = Enumerable.Range(0, reader.FieldCount); foreach (var columnName in fields.Select((i) => reader.GetName(i))) { bulkCopy.ColumnMappings.Add(columnName, columnName); } bulkCopy.BatchSize = BatchHelper.BulkCopyBatchSize; bulkCopy.BulkCopyTimeout = BatchHelper.BulkCopyTimeout; bulkCopy.DestinationTableName = destinationTableName; do { bulkCopy.WriteToServer(reader); }while (reader.NextResult()); } if (afterActionCommandText != null) { using (var command = connection.CreateCommand()) { command.CommandText = afterActionCommandText; command.CommandTimeout = 0; command.ExecuteNonQuery(); } } } } }
public static DataTable GetConfigurationFor(ConfigurationType configurationType) { switch (configurationType) { case ConfigurationType.Engine: return(ProcedureContext.GetConfiguration()); case ConfigurationType.TraceEvents: return(ProfilerCollector.GetConfiguration()); case ConfigurationType.PerformanceCounters: return(PerformanceCollector.GetConfiguration()); default: throw new ApplicationException("Unknown ConfigurationType [{0}]".FormatWith(configurationType)); } }
public static DataTable ToExecutionInfoTable(this ProcedureContext procedureContext) { #region Argument exception if (procedureContext == null) { throw new ArgumentNullException("procedureContext"); } #endregion var table = new DataTable("ExecutionInfo", "Common"); { table.Columns.Add("key", typeof(string)); table.Columns.Add("value", typeof(string)); } if (procedureContext.ExecutionMode == ProcedureExecutionMode.Batch) { table.Rows.Add("batch_id", procedureContext.BatchID.ToString()); table.Rows.Add("batch_name", procedureContext.BatchName); table.Rows.Add("sql_instance_name", procedureContext.SQLInstanceName); table.Rows.Add("sql_instance_version", procedureContext.SQLInstanceVersion); table.Rows.Add("sql_instance_edition", procedureContext.SQLInstanceEdition); } table.Rows.Add("client_version", procedureContext.ClientVersion); table.Rows.Add("client_type", procedureContext.ClientType); table.Rows.Add("connection_user_name", procedureContext.ConnectionUserName); table.Rows.Add("asqa_server_version", procedureContext.ASQAServerVersion); table.Rows.Add("asqa_server_config", procedureContext.ASQAServerConfig); table.Rows.Add("ssas_instance_name", procedureContext.SSASInstanceName); table.Rows.Add("ssas_instance_version", procedureContext.SSASInstanceVersion.ToString()); table.Rows.Add("ssas_instance_edition", procedureContext.SSASInstanceEdition); table.Rows.Add("ssas_instance_config", procedureContext.SSASInstanceConfig); table.Rows.Add("statement", procedureContext.Statement); table.Rows.Add("database_name", procedureContext.DatabaseName); table.Rows.Add("cube_name", procedureContext.CubeName); table.Rows.Add("cube_metadata", procedureContext.CubeMetadata); table.Rows.Add("system_operative_system_name", procedureContext.SystemOperativeSystemName); table.Rows.Add("system_physical_memory", procedureContext.SystemPhysicalMemory); table.Rows.Add("system_logical_cpu_core", procedureContext.SystemLogicalCpuCore); return(table); }
public static void ClearCache(this ProcedureContext procedureContext) { #region Argument exceptions if (procedureContext == null) { throw new ArgumentNullException(nameof(procedureContext)); } if (procedureContext.ClearCacheMode == ClearCacheMode.Default) { throw new ArgumentException("Invalid ClearCacheMode[ClearCacheMode.Default]"); } #endregion if (procedureContext.IsCancellationRequested) { return; } if (procedureContext.ClearCacheMode != ClearCacheMode.Nothing) { EventsNotifier.Instance.Notify(ProcedureEvents.ProcedureAnalyzeStepClearCache); } if (procedureContext.ClearCacheMode == ClearCacheMode.CurrentCube || procedureContext.ClearCacheMode == ClearCacheMode.CurrentCubeAndFileSystem || procedureContext.ClearCacheMode == ClearCacheMode.CurrentDatabase || procedureContext.ClearCacheMode == ClearCacheMode.CurrentDatabaseAndFileSystem || procedureContext.ClearCacheMode == ClearCacheMode.AllDatabases || procedureContext.ClearCacheMode == ClearCacheMode.AllDatabasesAndFileSystem) { XmlaHelper.ClearCache(procedureContext); } if (procedureContext.ClearCacheMode == ClearCacheMode.FileSystemOnly || procedureContext.ClearCacheMode == ClearCacheMode.CurrentCubeAndFileSystem || procedureContext.ClearCacheMode == ClearCacheMode.CurrentDatabaseAndFileSystem || procedureContext.ClearCacheMode == ClearCacheMode.AllDatabasesAndFileSystem) { FileSystemHelper.ClearFileSystemCache(clearStandbyCache: true); } }
private static void WriteToServer(ProcedureContext procedureContext, DataTable table, string destinationTableName) { using (procedureContext.ImpersonationIdentity.Impersonate()) { // http://www.sqlbi.com/wp-content/uploads/SqlBulkCopy-Performance-1.0.pdf using (var bulkCopy = new SqlBulkCopy(procedureContext.BatchConnectionString, SqlBulkCopyOptions.TableLock)) { foreach (DataColumn column in table.Columns) { bulkCopy.ColumnMappings.Add(column.ColumnName, column.ColumnName); } bulkCopy.BatchSize = BatchHelper.BulkCopyBatchSize; bulkCopy.BulkCopyTimeout = BatchHelper.BulkCopyTimeout; bulkCopy.DestinationTableName = destinationTableName; bulkCopy.WriteToServer(table); } } }
public static void ValidateStatement(this ProcedureContext procedureContext) { #region Argument exceptions if (procedureContext == null) { throw new ArgumentNullException("procedureContext"); } #endregion if (procedureContext.IsCancellationRequested) { return; } EventsNotifier.Instance.Notify(ProcedureEvents.ProcedureAnalyzeStepValidateStatement); AdomdClientHelper.ExecutePrepare(procedureContext.ConnectionString, procedureContext.Statement); }
public static Tuple <string, string, string, string> GetServerInfo(ProcedureContext procedureContext) { var selectCommand = @" SELECT [ServerName] = CAST(SERVERPROPERTY('ServerName') AS NVARCHAR(256)), [ProductVersion] = CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(256)), [Edition] = CAST(SERVERPROPERTY('Edition') AS NVARCHAR(256)), [ASQADatabaseVersion] = ( SELECT [ASQADatabaseVersion] = ep.value FROM sys.extended_properties AS ep WHERE ep.class = 0 AND ep.name = N'ASQA_DatabaseVersion' ) "; using (procedureContext.ImpersonationIdentity.Impersonate()) { using (var connection = new SqlConnection(procedureContext.BatchConnectionString)) { connection.Open(); using (var command = new SqlCommand(selectCommand, connection)) { using (var reader = command.ExecuteReader()) using (var table = new DataTable()) { table.Load(reader); var serverName = Convert.ToString(table.Rows[0][0]); var productVersion = Convert.ToString(table.Rows[0][1]); var edition = Convert.ToString(table.Rows[0][2]); var asqaDatabaseVersion = Convert.ToString(table.Rows[0][3]); return(Tuple.Create(serverName, productVersion, edition, asqaDatabaseVersion)); } } } } }
/// <summary> /// Force reload MDX script code in the cube executing a query that return an empty cellset. /// </summary> public static void LoadCubeScript(this ProcedureContext procedureContext) { #region Argument exceptions if (procedureContext == null) { throw new ArgumentNullException("procedureContext"); } #endregion if (procedureContext.IsCancellationRequested) { return; } if (procedureContext.ClearCacheMode != ClearCacheMode.Nothing) { EventsNotifier.Instance.Notify(ProcedureEvents.ProcedureAnalyzeStepLoadCubeScript); AdomdClientHelper.ExecuteNonQuery(procedureContext.ConnectionString, "SELECT {{}} ON 0 FROM [{0}]".FormatWith(procedureContext.CubeName)); } }
public static void Calculate(ProcedureContext procedureContext) { #region Argument exceptions if (procedureContext == null) { throw new ArgumentNullException("procedureContext"); } #endregion Func <string, string> loadResource = (name) => { using (var stream = System.Reflection.Assembly.GetExecutingAssembly().GetManifestResourceStream(name)) using (var reader = new StreamReader(stream)) return(reader.ReadToEnd()); }; const string BatchCalculateCommonEnginePerformance = "SSASQueryAnalyzer.Server.Resources.SSASQueryAnalyzerDatabaseBatchCalculateCommonEnginePerformance.sql"; const string BatchCalculateCommonAggregationsRead = "SSASQueryAnalyzer.Server.Resources.SSASQueryAnalyzerDatabaseBatchCalculateCommonAggregationsRead.sql"; const string BatchCalculateCommonPartitionsRead = "SSASQueryAnalyzer.Server.Resources.SSASQueryAnalyzerDatabaseBatchCalculateCommonPartitionsRead.sql"; const string BatchCalculateCommonCachesRead = "SSASQueryAnalyzer.Server.Resources.SSASQueryAnalyzerDatabaseBatchCalculateCommonCachesRead.sql"; using (procedureContext.ImpersonationIdentity.Impersonate()) { using (var connection = new SqlConnection(procedureContext.BatchConnectionString)) { connection.Open(); if (procedureContext.IsCancellationRequested) { return; } using (var command = new SqlCommand(loadResource(BatchCalculateCommonEnginePerformance), connection)) { command.CommandTimeout = CalculateTimeout; command.Parameters.Add(new SqlParameter("@executionID", SqlDbType.UniqueIdentifier)).Value = procedureContext.ExecutionID; command.ExecuteNonQuery(); } if (procedureContext.IsCancellationRequested) { return; } using (var command = new SqlCommand(loadResource(BatchCalculateCommonAggregationsRead), connection)) { command.CommandTimeout = CalculateTimeout; command.Parameters.Add(new SqlParameter("@executionID", SqlDbType.UniqueIdentifier)).Value = procedureContext.ExecutionID; command.ExecuteNonQuery(); } if (procedureContext.IsCancellationRequested) { return; } using (var command = new SqlCommand(loadResource(BatchCalculateCommonPartitionsRead), connection)) { command.CommandTimeout = CalculateTimeout; command.Parameters.Add(new SqlParameter("@executionID", SqlDbType.UniqueIdentifier)).Value = procedureContext.ExecutionID; command.ExecuteNonQuery(); } if (procedureContext.IsCancellationRequested) { return; } using (var command = new SqlCommand(loadResource(BatchCalculateCommonCachesRead), connection)) { command.CommandTimeout = CalculateTimeout; command.Parameters.Add(new SqlParameter("@executionID", SqlDbType.UniqueIdentifier)).Value = procedureContext.ExecutionID; command.ExecuteNonQuery(); } } } }
public static void WriteToServer(this List <PerformanceAggregate> aggreagates, ProcedureContext procedureContext) { #region Argument exceptions if (aggreagates == null) { throw new ArgumentNullException("aggreagates"); } if (procedureContext == null) { throw new ArgumentNullException("procedureContext"); } #endregion // TODO: LINQ to IDataReader, avoid DataTables foreach (var table in aggreagates.ToDataTables(executeForPrepare: false)) { using (table) { if (procedureContext.IsCancellationRequested) { break; } if (table.Rows.Count == 0) { continue; } table.Columns.Add(new DataColumn("ExecutionID", typeof(Guid)) { DefaultValue = procedureContext.ExecutionID }); table.Columns.Add(new DataColumn("CategoryName", typeof(string)) { DefaultValue = table.Namespace.Split(':').Last() }); table.Columns.Add(new DataColumn("CounterName", typeof(string)) { DefaultValue = table.TableName }); WriteToServer(procedureContext, table, PerformanceTableName); } } }
private ProcedureContext(ProcedureExecutionMode executionMode, string statement, ClearCacheMode clearCacheMode, int queryResultRowLimit = 0, string clientVersion = null, string clientType = null, string batchID = null, string batchConnectionString = null, string batchName = null) { #region Argument exception if (statement == null) { throw new ArgumentNullException("statement"); } if (executionMode == ProcedureExecutionMode.Batch ^ batchID != null) { throw new ArgumentException("ExecutionMode XOR batchID"); } if (executionMode == ProcedureExecutionMode.Batch ^ batchConnectionString != null) { throw new ArgumentException("ExecutionMode XOR BatchConnectionString"); } #endregion _batchConnectionString = batchConnectionString; _cancellationSource = new CancellationTokenSource(); Statement = statement; ExecutionMode = executionMode; BatchID = Guid.Parse(batchID ?? Guid.Empty.ToString()); ExecutionID = Guid.NewGuid(); BatchName = batchName; CubeName = statement.GetCubeName(); QueryResultRowLimit = queryResultRowLimit; DatabaseName = AdomdServer.Context.CurrentDatabaseName; ConnectionUserName = AdomdServer.Context.CurrentConnection.User.Name; ImpersonationIdentity = WindowsIdentity.GetCurrent(); ExecuteForPrepare = AdomdServer.Context.ExecuteForPrepare; CurrentTraceEvents = ProfilerCollector.CurrentTraceEvents(); ASQAServerVersion = GetAsqaServerVersion().ToString(); ASQAServerConfig = GetRawConfiguration(); ClientVersion = clientVersion; ClientType = clientType; SystemPhysicalMemory = PInvokeHelper.GetPhysicalSystemMemory(); SystemOperativeSystemName = PInvokeHelper.GetOperatingSystemName(); SystemLogicalCpuCore = Environment.ProcessorCount; GetConfigurationDetails(out int configuredTraceEventsThreshold, out ClearCacheMode configuredClearCacheMode); TraceEventsThreshold = configuredTraceEventsThreshold; ClearCacheMode = clearCacheMode == ClearCacheMode.Default ? configuredClearCacheMode : clearCacheMode; var ssasInfo = GetServerInfo(); SSASInstanceName = AdomdServer.Context.CurrentServerID; SSASInstanceVersion = ssasInfo.Item1; SSASInstanceEdition = ssasInfo.Item2; SSASInstanceConfig = ssasInfo.Item3; if (executionMode == ProcedureExecutionMode.Batch) { var sqlInfo = BatchHelper.GetServerInfo(this); SQLInstanceName = sqlInfo.Item1; SQLInstanceVersion = sqlInfo.Item2; SQLInstanceEdition = sqlInfo.Item3; CheckBatchVersionCompatibility(version: sqlInfo.Item4); } CubeMetadata = XmlaHelper.RetrieveCubeMetadata(this); ProcedureContext.CheckVersionCompatibility(SSASInstanceVersion); }
public static void WriteToSqlite(ProcedureContext procedureContext, IDataReader reader, Action <IDataReader> afterCompleted) { #region Argument exceptions if (procedureContext == null) { throw new ArgumentNullException("procedureContext"); } if (reader == null) { throw new ArgumentNullException("reader"); } if (afterCompleted == null) { throw new ArgumentNullException("afterCompleted"); } #endregion var createTableCommandText = @" CREATE TABLE Trace ( ID NUMERIC, EventClass TEXT, EventSubclass TEXT, IntegerData INTEGER, StartTime DATETIME, CurrentTime DATETIME, Duration INTEGER, ObjectName TEXT, CpuTime INTEGER, EndTime DATETIME, ObjectID TEXT, ObjectPath TEXT, ObjectType TEXT, ObjectReference TEXT, ProgressTotal INTEGER, TextData TEXT )"; var insertCommandText = @" INSERT INTO Trace ( ID, EventClass, EventSubclass, IntegerData, StartTime, CurrentTime, Duration, ObjectName, CpuTime, EndTime, ObjectID, ObjectPath, ObjectType, ObjectReference, ProgressTotal, TextData ) VALUES ( @ID, @EventClass, @EventSubclass, @IntegerData, @StartTime, @CurrentTime, @Duration, @ObjectName, @CpuTime, @EndTime, @ObjectID, @ObjectPath, @ObjectType, @ObjectReference, @ProgressTotal, @TextData )"; var selectCommandText = @" SELECT ID, EventClass, EventSubclass, IntegerData, StartTime, CurrentTime, Duration, ObjectName, CpuTime, EndTime, ObjectID, ObjectPath, ObjectType, ObjectReference, ProgressTotal, TextData FROM Trace"; System.Action action = () => { var path = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData), ProcedureContext.SQLiteFolderPath); Environment.SetEnvironmentVariable("PreLoadSQLite_BaseDirectory", Path.Combine(path, "bin")); var interopPath = Path.Combine(path, "bin", "x64"); var interopFile = Path.Combine(interopPath, "SQLite.Interop.dll"); if (!File.Exists(interopFile)) { Directory.CreateDirectory(interopPath); using (var stream = new StreamReader(System.Reflection.Assembly.GetExecutingAssembly().GetManifestResourceStream("SSASQueryAnalyzer.Server.Resources.SQLite.Interop.dll"))) using (var file = File.Create(interopFile)) { stream.BaseStream.Seek(0, SeekOrigin.Begin); stream.BaseStream.CopyTo(file); } } var databasePath = Path.Combine(path, "db"); var databaseFileName = Path.Combine(databasePath, "asqa-{0:N}-{1:N}.db".FormatWith(procedureContext.BatchID, procedureContext.ExecutionID)); Directory.CreateDirectory(databasePath); foreach (var file in Directory.EnumerateFiles(databasePath, "asqa-*.db")) { File.Delete(file); } var connectionStringBuilder = new SQLiteConnectionStringBuilder(); connectionStringBuilder.JournalMode = SQLiteJournalModeEnum.Off; connectionStringBuilder.SyncMode = SynchronizationModes.Off; connectionStringBuilder.DataSource = databaseFileName; connectionStringBuilder.FailIfMissing = false; connectionStringBuilder.LegacyFormat = false; connectionStringBuilder.Version = 3; connectionStringBuilder.DateTimeKind = DateTimeKind.Utc; connectionStringBuilder.DateTimeFormat = SQLiteDateFormats.ISO8601; using (var connection = new SQLiteConnection(connectionStringBuilder.ToString())) { connection.Open(); using (var command = connection.CreateCommand()) { command.CommandText = createTableCommandText; command.ExecuteNonQuery(); } using (var transaction = connection.BeginTransaction()) using (var command = connection.CreateCommand()) { command.CommandText = insertCommandText; command.Prepare(); #region Params binding var paramID = command.Parameters.Add("@ID", DbType.Int32); var paramEventClass = command.Parameters.Add("@EventClass", DbType.String); var paramEventSubclass = command.Parameters.Add("@EventSubclass", DbType.String); var paramIntegerData = command.Parameters.Add("@IntegerData", DbType.Int64); var paramStartTime = command.Parameters.Add("@StartTime", DbType.DateTime); var paramCurrentTime = command.Parameters.Add("@CurrentTime", DbType.DateTime); var paramDuration = command.Parameters.Add("@Duration", DbType.Int32); var paramObjectName = command.Parameters.Add("@ObjectName", DbType.String); var paramCpuTime = command.Parameters.Add("@CpuTime", DbType.Int32); var paramEndTime = command.Parameters.Add("@EndTime", DbType.DateTime); var paramObjectID = command.Parameters.Add("@ObjectID", DbType.String); var paramObjectPath = command.Parameters.Add("@ObjectPath", DbType.String); var paramObjectType = command.Parameters.Add("@ObjectType", DbType.String); var paramObjectReference = command.Parameters.Add("@ObjectReference", DbType.String); var paramProgressTotal = command.Parameters.Add("@ProgressTotal", DbType.Int64); var paramTextData = command.Parameters.Add("@TextData", DbType.String); var columnOrdinalID = reader.GetOrdinal("ID"); var columnOrdinalEventClass = reader.GetOrdinal("EventClass"); var columnOrdinalEventSubclass = reader.GetOrdinal("EventSubclass"); var columnOrdinalIntegerData = reader.GetOrdinal("IntegerData"); var columnOrdinalStartTime = reader.GetOrdinal("StartTime"); var columnOrdinalCurrentTime = reader.GetOrdinal("CurrentTime"); var columnOrdinalDuration = reader.GetOrdinal("Duration"); var columnOrdinalObjectName = reader.GetOrdinal("ObjectName"); var columnOrdinalCpuTime = reader.GetOrdinal("CpuTime"); var columnOrdinalEndTime = reader.GetOrdinal("EndTime"); var columnOrdinalObjectID = reader.GetOrdinal("ObjectID"); var columnOrdinalObjectPath = reader.GetOrdinal("ObjectPath"); var columnOrdinalObjectType = reader.GetOrdinal("ObjectType"); var columnOrdinalObjectReference = reader.GetOrdinal("ObjectReference"); var columnOrdinalProgressTotal = reader.GetOrdinal("ProgressTotal"); var columnOrdinalTextData = reader.GetOrdinal("TextData"); #endregion do { while (reader.Read()) { #region Params values paramID.Value = reader[columnOrdinalID]; paramEventClass.Value = reader[columnOrdinalEventClass]; paramEventSubclass.Value = reader[columnOrdinalEventSubclass]; paramIntegerData.Value = reader[columnOrdinalIntegerData]; paramStartTime.Value = reader[columnOrdinalStartTime]; paramCurrentTime.Value = reader[columnOrdinalCurrentTime]; paramDuration.Value = reader[columnOrdinalDuration]; paramObjectName.Value = reader[columnOrdinalObjectName]; paramCpuTime.Value = reader[columnOrdinalCpuTime]; paramEndTime.Value = reader[columnOrdinalEndTime]; paramObjectID.Value = reader[columnOrdinalObjectID]; paramObjectPath.Value = reader[columnOrdinalObjectPath]; paramObjectType.Value = reader[columnOrdinalObjectType]; paramObjectReference.Value = reader[columnOrdinalObjectReference]; paramProgressTotal.Value = reader[columnOrdinalProgressTotal]; paramTextData.Value = reader[columnOrdinalTextData]; #endregion command.ExecuteNonQuery(); } }while (reader.NextResult()); transaction.Commit(); } using (var command = connection.CreateCommand()) { command.CommandText = selectCommandText; using (var select = command.ExecuteReader()) afterCompleted(select); } } }; using (var task = Task.Factory.StartNew(action)) task.Wait(); }
public static string RetrieveCubeMetadata(ProcedureContext procedureContext) { #region Argument exception if (procedureContext == null) { throw new ArgumentNullException("procedureContext"); } #endregion var path = default(string); var document = new XDocument(new XDeclaration("1.0", "utf-8", standalone: null), new XElement("CubeMetadata")); using (var server = new Server()) { server.Connect(procedureContext.ConnectionString); #region Find database/cube/dimension var database = server.Databases.FindByName(procedureContext.DatabaseName); if (database == null) { throw new ApplicationException("Database not found [{0}]".FormatWith(procedureContext.DatabaseName)); } var cube = database.Cubes.FindByName(procedureContext.CubeName); if (cube == null) { throw new ApplicationException("Cube not found [{0}]".FormatWith(procedureContext.CubeName)); } var dimension = cube.Dimensions.Cast <CubeDimension>().FirstOrDefault((d) => d.Visible); if (dimension == null) { throw new ApplicationException("CubeDimension for metadata not found"); } #endregion path = "[{0}]".FormatWith(dimension.Name); var attribute = dimension.Attributes.Cast <CubeAttribute>().FirstOrDefault((a) => a.AttributeHierarchyVisible && a.AttributeHierarchyEnabled); if (attribute != null) { path += ".[{0}]".FormatWith(attribute.Attribute.Name); } else { var hierarchy = dimension.Hierarchies.Cast <CubeHierarchy>().FirstOrDefault((h) => h.Visible && h.Enabled); if (hierarchy == null) { throw new ApplicationException("CubeAttribute/CubeHierarchy for metadata not found"); } path += ".[{0}]".FormatWith(hierarchy.Hierarchy.Name); } var mdxScript = cube.MdxScripts.Cast <MdxScript>().First(); document.Root.Add(new XElement("Cube", new XAttribute("ID", cube.ID ?? string.Empty), new XAttribute("Name", cube.Name ?? string.Empty), new XAttribute("LastSchemaUpdate", cube.LastSchemaUpdate.ToUniversalTime().ToString("yyyyMMdd-HHmmss", CultureInfo.InvariantCulture)), new XAttribute("EstimatedRows", cube.EstimatedRows), new XAttribute("StorageMode", cube.StorageMode), new XAttribute("DefaultMeasure", cube.DefaultMeasure ?? string.Empty), new XElement("MdxScript", new XAttribute("ID", mdxScript.ID ?? string.Empty), new XAttribute("Name", mdxScript.Name ?? string.Empty), new XAttribute("DefaultScript", mdxScript.DefaultScript), new XElement("Commands", mdxScript.Commands.Cast <Command>() .Select((c) => { var e = new XElement("Command"); e.Value = c.Text; return(e); }))), new XElement("CubeDimensions", cube.Dimensions.Cast <CubeDimension>() .Select((d) => new XElement("CubeDimension", new XAttribute("ID", d.ID ?? string.Empty), new XAttribute("Name", d.Name ?? string.Empty), new XAttribute("Visible", d.Visible), new XAttribute("AttributesCount", d.Attributes.Count), new XAttribute("HierarchiesCount", d.Hierarchies.Count), new XAttribute("DimensionID", d.DimensionID ?? string.Empty)))), new XElement("MeasureGroups", cube.MeasureGroups.Cast <MeasureGroup>() .Select((m) => new XElement("MeasureGroup", new XAttribute("ID", m.ID ?? string.Empty), new XAttribute("Name", m.Name), new XAttribute("Type", m.Type), new XAttribute("StorageMode", m.StorageMode), new XAttribute("EstimatedRows", m.EstimatedRows), new XAttribute("EstimatedSize", m.EstimatedSize), new XAttribute("DataAggregation", m.DataAggregation), new XAttribute("ProcessingMode", m.ProcessingMode), new XElement("AggregationDesigns", m.AggregationDesigns.Cast <AggregationDesign>() .Select((a) => new XElement("AggregationDesign", new XAttribute("ID", a.ID ?? string.Empty), new XAttribute("Name", a.Name), new XAttribute("EstimatedRows", a.EstimatedRows), new XAttribute("EstimatedPerformanceGain", a.EstimatedPerformanceGain)))), new XElement("Partitions", m.Partitions.Cast <Partition>() .Select((p) => new XElement("Partition", new XAttribute("ID", p.ID ?? string.Empty), new XAttribute("Name", p.Name), new XAttribute("EstimatedRows", p.EstimatedRows), new XAttribute("EstimatedSize", p.EstimatedSize), new XAttribute("ProcessingMode", p.ProcessingMode), new XAttribute("Slice", p.Slice ?? string.Empty), new XAttribute("StorageMode", p.StorageMode), new XAttribute("Type", p.Type), new XAttribute("AggregationDesignID", p.AggregationDesignID ?? string.Empty)))), new XElement("Measures", m.Measures.Cast <Measure>() .Select((s) => new XElement("Measure", new XAttribute("ID", s.ID ?? string.Empty), new XAttribute("Name", s.Name), new XAttribute("Visible", s.Visible), new XAttribute("DataType", s.DataType), new XAttribute("FormatString", s.FormatString ?? string.Empty), new XAttribute("MeasureExpression", s.MeasureExpression ?? string.Empty), new XAttribute("SourceNullProcessing", s.Source.NullProcessing), new XAttribute("SourceNullDataType", s.Source.DataType))))))))); } using (var table = AdomdClientHelper.ExecuteDataTable(procedureContext.ConnectionString, commandText: MdxMeasuresMetadata.FormatWith(procedureContext.CubeName, path))) { if (table.Columns.Count != 3) { throw new ApplicationException("Invalid columns for MdxMeasuresMetadata"); } table.Columns[0].ColumnName = "Name"; table.Columns[1].ColumnName = "IDOfCurrentMember"; table.Columns[2].ColumnName = "ID"; document.Root.Add(new XElement("MeasuresExtended", table.AsEnumerable().Skip(1) .Select((r) => new XElement("Measure", table.Columns.Cast <DataColumn>().Select((c) => new XAttribute(c.ColumnName, r[c]))))) ); } return(document.ToString(SaveOptions.DisableFormatting)); }
/// <summary> /// Code from ASStoredProcedures [ASStoredProcs.XmlaDiscover.ClearCache(string cubeName)] /// </summary> /// <see cref="http://www.codeplex.com/Wiki/View.aspx?ProjectName=ASStoredProcedures"/> public static void ClearCache(ProcedureContext procedureContext) { #region Argument exception if (procedureContext == null) { throw new ArgumentNullException(nameof(procedureContext)); } #endregion using (var server = new Server()) { server.Connect(procedureContext.ConnectionString); switch (procedureContext.ClearCacheMode) { case ClearCacheMode.CurrentCube: case ClearCacheMode.CurrentCubeAndFileSystem: { var database = server.Databases.FindByName(procedureContext.DatabaseName); if (database == null) { throw new ApplicationException("Database not found [{0}]".FormatWith(procedureContext.DatabaseName)); } var cube = database.Cubes.FindByName(procedureContext.CubeName); if (cube == null) { throw new ApplicationException("Cube not found [{0}]".FormatWith(procedureContext.DatabaseName)); } server.Execute(XmlaBatchClearCubeCacheTemplate.FormatWith(database.ID, cube.ID)); } break; case ClearCacheMode.CurrentDatabase: case ClearCacheMode.CurrentDatabaseAndFileSystem: { var database = server.Databases.FindByName(procedureContext.DatabaseName); if (database == null) { throw new ApplicationException("Database not found [{0}]".FormatWith(procedureContext.DatabaseName)); } server.Execute(XmlaBatchClearDatabaseCacheTemplate.FormatWith(database.ID)); } break; case ClearCacheMode.AllDatabases: case ClearCacheMode.AllDatabasesAndFileSystem: { foreach (Database database in server.Databases) { server.Execute(XmlaBatchClearDatabaseCacheTemplate.FormatWith(database.ID)); } } break; default: throw new ApplicationException("Invalid ClearCacheMode [{0}]".FormatWith(procedureContext.ClearCacheMode)); } } }
public static void Initialize(ProcedureContext procedureContext) { #region Argument exceptions if (procedureContext == null) { throw new ArgumentNullException("procedureContext"); } #endregion var insertCommand = @" INSERT INTO [asqa].[Execution] ( [BatchID], [ExecutionID], [BatchName], [ExecutionStartTime], [ConnectionUserName], [ImpersonationUserName], [SSASInstanceName], [SSASInstanceVersion], [SSASInstanceEdition], [SSASInstanceConfig], [SQLInstanceName], [SQLInstanceVersion], [SQLInstanceEdition], [DatabaseName], [CubeName], [CubeMetadata], [ClearCacheMode], [Statement], [ASQAServerVersion], [ASQAServerConfig], [ClientVersion], [ClientType], [SystemOperativeSystemName], [SystemPhysicalMemory], [SystemLogicalCpuCore] ) VALUES ( @batchID, @executionID, @batchName, @executionStartTime, @connectionUserName, @impersonationUserName, @ssasInstanceName, @ssasInstanceVersion, @ssasInstanceEdition, @ssasInstanceConfig, CAST(SERVERPROPERTY('ServerName') AS NVARCHAR(256)), CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(256)), CAST(SERVERPROPERTY('Edition') AS NVARCHAR(256)), @databaseName, @cubeName, @cubeMetadata, @clearCacheMode, @statement, @asqaServerVersion, @asqaServerConfig, @clientVersion, @clientType, @systemOperativeSystemName, @systemPhysicalMemory, @systemLogicalCpuCore )"; using (procedureContext.ImpersonationIdentity.Impersonate()) { using (var connection = new SqlConnection(procedureContext.BatchConnectionString)) { connection.Open(); using (var command = new SqlCommand(insertCommand, connection)) { command.Parameters.Add(new SqlParameter("@batchID", SqlDbType.UniqueIdentifier)).Value = procedureContext.BatchID; command.Parameters.Add(new SqlParameter("@executionID", SqlDbType.UniqueIdentifier)).Value = procedureContext.ExecutionID; command.Parameters.Add(new SqlParameter("@batchName", SqlDbType.NVarChar)).Value = procedureContext.BatchName; command.Parameters.Add(new SqlParameter("@executionStartTime", SqlDbType.DateTime)).Value = DateTime.UtcNow; command.Parameters.Add(new SqlParameter("@connectionUserName", SqlDbType.NVarChar)).Value = procedureContext.ConnectionUserName; command.Parameters.Add(new SqlParameter("@impersonationUserName", SqlDbType.NVarChar)).Value = procedureContext.ImpersonationIdentity.Name; command.Parameters.Add(new SqlParameter("@ssasInstanceName", SqlDbType.NVarChar)).Value = procedureContext.SSASInstanceName; command.Parameters.Add(new SqlParameter("@ssasInstanceVersion", SqlDbType.NVarChar)).Value = procedureContext.SSASInstanceVersion.ToString(); command.Parameters.Add(new SqlParameter("@ssasInstanceEdition", SqlDbType.NVarChar)).Value = procedureContext.SSASInstanceEdition; command.Parameters.Add(new SqlParameter("@ssasInstanceConfig", SqlDbType.NVarChar)).Value = procedureContext.SSASInstanceConfig; command.Parameters.Add(new SqlParameter("@databaseName", SqlDbType.NVarChar)).Value = procedureContext.DatabaseName; command.Parameters.Add(new SqlParameter("@cubeName", SqlDbType.NVarChar)).Value = procedureContext.CubeName; command.Parameters.Add(new SqlParameter("@cubeMetadata", SqlDbType.NVarChar)).Value = procedureContext.CubeMetadata; command.Parameters.Add(new SqlParameter("@clearCacheMode", SqlDbType.NVarChar)).Value = procedureContext.ClearCacheMode; command.Parameters.Add(new SqlParameter("@statement", SqlDbType.NVarChar)).Value = procedureContext.Statement; command.Parameters.Add(new SqlParameter("@asqaServerVersion", SqlDbType.NVarChar)).Value = procedureContext.ASQAServerVersion; command.Parameters.Add(new SqlParameter("@asqaServerConfig", SqlDbType.NVarChar)).Value = procedureContext.ASQAServerConfig; command.Parameters.Add(new SqlParameter("@clientVersion", SqlDbType.NVarChar)).Value = procedureContext.ClientVersion; command.Parameters.Add(new SqlParameter("@clientType", SqlDbType.NVarChar)).Value = procedureContext.ClientType; command.Parameters.Add(new SqlParameter("@systemOperativeSystemName", SqlDbType.NVarChar)).Value = procedureContext.SystemOperativeSystemName; command.Parameters.Add(new SqlParameter("@systemPhysicalMemory", SqlDbType.BigInt)).Value = procedureContext.SystemPhysicalMemory; command.Parameters.Add(new SqlParameter("@systemLogicalCpuCore", SqlDbType.Int)).Value = procedureContext.SystemLogicalCpuCore; command.ExecuteNonQuery(); } } } }