/* * 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(); }