private TSQLCollectionItemConfig[] parse(string text) { String sql = @" ;WITH XMLNAMESPACES('DataCollectorType' AS ns) SELECT x.value('Value[1]','varchar(max)') AS query, x.value('OutputTable[1]', 'varchar(max)') AS outputTable FROM @x.nodes('/ns:TSQLQueryCollector/Query') Q(x) ORDER BY outputTable; "; int ConnectionTimeout = 15; int QueryTimeout = 600; String ConnectionString = String.Format("Server={0};Database={1};Integrated Security=True;Connect Timeout={2}", Manager.ServerName, "msdb", ConnectionTimeout); using (SqlConnection conn = new SqlConnection()) { conn.ConnectionString = ConnectionString; TSQLCollectionItemConfig[] results; try { conn.Open(); SqlCommand cmd = new SqlCommand(sql, conn); cmd.CommandType = CommandType.Text; cmd.CommandTimeout = QueryTimeout; cmd.Parameters.Add("x", SqlDbType.Xml, -1); cmd.Parameters[0].Value = text; DataSet ds = new DataSet(); SqlDataAdapter da = new SqlDataAdapter(cmd); da.Fill(ds); DataTable dt = ds.Tables[0]; results = new TSQLCollectionItemConfig[dt.Rows.Count]; for (int i = 0; i < dt.Rows.Count; i++) { DataRow dr = dt.Rows[i]; TSQLCollectionItemConfig cfg = new TSQLCollectionItemConfig(); cfg.Query = dr["query"].ToString(); cfg.OutputTable = dr["outputTable"].ToString(); results[i] = cfg; } } finally { conn.Close(); } return(results); } }
/* * Creates the target table from the * output definition of the query */ protected override String createTargetTable( CollectorConfig cfg, CollectionItemConfig itm ) { String TableName = itm.OutputTable; TSQLCollectionItemConfig itemCfg = (TSQLCollectionItemConfig)itm; String CollectorId = CollectorUtils.getCacheFilePrefix(SourceServerInstance, CollectionSetUid, ItemId) + "_" + itm.Index; // // checks if the table exists and which schema it belongs // String checkedTableName = checkTable(cfg, itm); if (checkedTableName != null) { return(checkedTableName); } String statement = @" IF NOT EXISTS ( SELECT * FROM sys.servers WHERE NAME = 'LOOPBACK' ) BEGIN DECLARE @srv nvarchar(4000); SET @srv = @@SERVERNAME; -- gather this server name -- Create the linked server EXEC master.dbo.sp_addlinkedserver @server = N'LOOPBACK', @srvproduct = N'SQLServ', -- it's not a typo: it can't be 'SQLServer' @provider = N'SQLNCLI', -- change to SQLOLEDB for SQLServer 2000 @datasrc = @srv; -- Set the authentication to 'current security context' EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'LOOPBACK', @useself = N'True', @locallogin = NULL, @rmtuser = NULL, @rmtpassword = NULL; END USE tempdb; GO IF OBJECT_ID('{0}') IS NOT NULL DROP PROCEDURE [{0}] GO CREATE PROCEDURE [{0}] AS BEGIN SET NOCOUNT ON; {1} END GO IF SCHEMA_ID('custom_snapshots') IS NULL EXEC('CREATE SCHEMA [custom_snapshots]') IF OBJECT_ID('custom_snapshots.{2}') IS NOT NULL DROP TABLE [custom_snapshots].[{2}] GO SELECT TOP 0 *, CAST(NULL AS sysname) AS _database_name, CAST(NULL AS datetimeoffset(7)) AS _collection_time, CAST(NULL AS int) AS _snapshot_id INTO tempdb.[custom_snapshots].[{2}] FROM OPENQUERY(LOOPBACK, 'SET FMTONLY OFF; EXEC tempdb.dbo.[{0}]'); DROP PROCEDURE [{0}]; GO IF EXISTS( SELECT 1 FROM sys.columns WHERE name = 'database_name' AND object_id = OBJECT_ID('[custom_snapshots].[{2}]') ) BEGIN EXEC sp_rename '[custom_snapshots].[{2}].[database_name]', '__database_name', 'COLUMN'; END EXEC sp_rename '[custom_snapshots].[{2}].[_database_name]', 'database_name', 'COLUMN'; IF EXISTS( SELECT 1 FROM sys.columns WHERE name = 'collection_time' AND object_id = OBJECT_ID('[custom_snapshots].[{2}]') ) BEGIN EXEC sp_rename '[custom_snapshots].[{2}].[collection_time]', '__collection_time', 'COLUMN'; END EXEC sp_rename '[custom_snapshots].[{2}].[_collection_time]', 'collection_time', 'COLUMN'; IF EXISTS( SELECT 1 FROM sys.columns WHERE name = 'snapshot_id' AND object_id = OBJECT_ID('[custom_snapshots].[{2}]') ) BEGIN EXEC sp_rename '[custom_snapshots].[{2}].[snapshot_id]', '__snapshot_id', 'COLUMN'; END EXEC sp_rename '[custom_snapshots].[{2}].[_snapshot_id]', 'snapshot_id', 'COLUMN'; " ; statement = String.Format(statement, CollectorId, itemCfg.Query, itm.OutputTable); CollectorUtils.InvokeSqlBatch(SourceServerInstance, "tempdb", statement); String scriptText = CollectorUtils.ScriptTable(SourceServerInstance, "tempdb", TableName); CollectorUtils.InvokeSqlCmd(cfg.MDWInstance, cfg.MDWDatabase, scriptText); return("[custom_snapshots].[" + TableName + "]"); }
public void CollectData( String SourceServerInstance, Guid CollectionSetUid, int ItemId ) { CollectorLogger logger = new CollectorLogger(SourceServerInstance, CollectionSetUid, ItemId); DataTable collectedData = null; if (verbose) { logger.logMessage("--------------------------------"); } if (verbose) { logger.logMessage(" ExtendedTSQLCollector "); } if (verbose) { logger.logMessage("--------------------------------"); } if (verbose) { logger.logMessage("Copyright© sqlconsulting.it 2014"); } if (verbose) { logger.logMessage("-"); } if (verbose) { logger.logMessage("Loading configuration"); } // // Load Configuration // TSQLCollectorConfig cfg = new TSQLCollectorConfig(); cfg.readFromDatabase(SourceServerInstance, CollectionSetUid, ItemId); if (verbose) { logger.logMessage("Entering collection items loop"); } foreach (CollectionItemConfig item in cfg.collectionItems) { TSQLCollectionItemConfig itm = (TSQLCollectionItemConfig)item; if (verbose) { logger.logMessage("Processing item n. " + itm.Index); } if (verbose) { logger.logMessage("Processing query " + itm.Query); } collectedData = null; String ts = DateTime.Now.ToString("yyyyMMddHHmmss"); String collectorId = CollectorUtils.getCacheFilePrefix(SourceServerInstance, CollectionSetUid, ItemId) + "_" + itm.Index; String destFile = Path.Combine(cfg.CacheDirectory, collectorId + "_" + ts + ".cache"); // // Iterate through the enabled databases // if (verbose) { logger.logMessage("Entering databases loop"); } foreach (String currentDatabase in cfg.Databases) { if (verbose) { logger.logMessage("Processing database " + currentDatabase); } // // Execute the query in the collection item // DataTable dt = CollectorUtils.GetDataTable(SourceServerInstance, currentDatabase, itm.Query); // // Add computed columns // if (dt.Columns.Contains("database_name")) { dt.Columns["database_name"].ColumnName = "__database_name"; } DataColumn cl_db = new DataColumn("database_name", typeof(String)); cl_db.DefaultValue = currentDatabase; dt.Columns.Add(cl_db); if (dt.Columns.Contains("collection_time")) { dt.Columns["collection_time"].ColumnName = "__collection_time"; } DataColumn cl_dt = new DataColumn("collection_time", typeof(DateTime)); cl_dt.DefaultValue = DateTime.Now; dt.Columns.Add(cl_dt); // // Merge collected data in a single DataTable // if (collectedData != null) { collectedData.Merge(dt); } else { collectedData = dt; collectedData.DataSet.RemotingFormat = System.Data.SerializationFormat.Binary; collectedData.RemotingFormat = System.Data.SerializationFormat.Binary; } } if (verbose) { logger.logMessage("Saving to cache file " + destFile); } // // Save data to a binary cache file // if (File.Exists(destFile)) { File.Delete(destFile); } System.Runtime.Serialization.Formatters.Binary.BinaryFormatter fm = new System.Runtime.Serialization.Formatters.Binary.BinaryFormatter(); using (FileStream fs = new FileStream(destFile, FileMode.CreateNew)) { fm.Serialize(fs, collectedData); fs.Close(); } if (verbose) { logger.logMessage("File saved successfully"); } } logger.cleanupLogFiles(cfg.DaysUntilExpiration); }