/*
* Controls that the session is running. If it's not running
*/
private void CheckSession(XEReaderCollectionItemConfig itm)
{
String sql_check_session = @"
SELECT name,
state =
CASE WHEN EXISTS (
SELECT 1
FROM sys.dm_xe_sessions AS x
WHERE name = sess.name
) THEN 'ON'
ELSE 'OFF'
END
FROM sys.server_event_sessions AS sess
WHERE name = '{0}'
";
sql_check_session = String.Format(sql_check_session, itm.SessionName);
DataTable sess = CollectorUtils.GetDataTable(SourceServerInstance, "master", sql_check_session);
if (sess.Rows.Count <= 0)
{
// create the session
CollectorUtils.InvokeSqlCmd(SourceServerInstance, "master", itm.SessionDefinition);
// repeat the check to see if
// the session has been started
sess = CollectorUtils.GetDataTable(SourceServerInstance, "master", sql_check_session);
}
if (sess.Rows[0]["state"].ToString().Equals("OFF"))
{
// TODO: start the session
String sql_startSession = @"
ALTER EVENT SESSION [{0}]
ON SERVER
STATE = start;
";
sql_startSession = String.Format(sql_startSession, itm.SessionName);
CollectorUtils.InvokeSqlCmd(SourceServerInstance, "master", sql_startSession);
}
}
/*
* 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 + "]");
}
private void btnSave_Click(object sender, EventArgs e)
{
String verb = "create";
if (textBox7.Text.Length == 0)
{
verb = "create";
}
else
{
verb = "update";
}
String sql = @"dbo.sp_syscollector_{0}_collection_item";
sql = String.Format(sql, verb);
int ConnectionTimeout = 15;
int QueryTimeout = 600;
String ConnectionString = String.Format("Server={0};Database={1};Integrated Security=True;Connect Timeout={2}", Manager.ServerName, "msdb", ConnectionTimeout);
int collection_item_id = -1;
int collection_set_id;
using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString = ConnectionString;
try
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = QueryTimeout;
SqlCommandBuilder.DeriveParameters(cmd);
if (verb.Equals("update"))
{
collection_item_id = Convert.ToInt32(textBox7.Text);
collection_set_id = Convert.ToInt32(textBox1.Text);
CollectorUtils.InvokeSqlCmd(Manager.ServerName, "msdb", "UPDATE syscollector_collection_items SET collector_type_uid = '" + ((ComboboxItem)comboBox1.SelectedItem).Value.ToString() + "' WHERE collection_item_id = " + collection_item_id);
cmd.Parameters["@collection_item_id"].Value = collection_item_id;
cmd.Parameters["@new_name"].Value = textBox2.Text;
}
else
{
collection_set_id = Convert.ToInt32(textBox1.Text);
cmd.Parameters["@collection_set_id"].Value = collection_set_id;
cmd.Parameters["@name"].Value = textBox2.Text;
cmd.Parameters["@collector_type_uid"].Value = ((ComboboxItem)comboBox1.SelectedItem).Value;
cmd.Parameters["@collection_item_id"].Direction = ParameterDirection.Output;
}
cmd.Parameters["@frequency"].Value = Convert.ToInt32(textBox3.Text);
cmd.Parameters["@parameters"].Value = textBox4.Text;
cmd.ExecuteNonQuery();
if (verb.Equals("create"))
{
collection_item_id = Convert.ToInt32(cmd.Parameters["@collection_item_id"].Value.ToString());
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
conn.Close();
}
}
fillValues(collection_item_id);
_main.RefreshTreeView();
}