Ejemplo n.º 1
0
        /*
         * 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);
            }
        }
Ejemplo n.º 2
0
        /*
         * 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 + "]");
        }
Ejemplo n.º 3
0
        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();
        }