private void WriteCacheFile(DataTable collectedData, CollectionItemConfig itm)
{
String ts = DateTime.Now.ToString("yyyyMMddHHmmss");
String collectorId = CollectorUtils.getCacheFilePrefix(SourceServerInstance, CollectionSetUid, ItemId) + "_" + itm.Index;
String destFile = Path.Combine(cfg.CacheDirectory, collectorId + "_" + ts + ".cache");
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");
}
collectedData.Rows.Clear();
}
/*
* 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);
}