Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSadd_snapshot_history

  No additional text.


Syntax
CREATE PROCEDURE sys.sp_MSadd_snapshot_history
(
    @agent_id int,
    @runstatus int,
    @comments nvarchar(1000),
    @delivered_transactions int = 0,
    @delivered_commands int = 0,
    @log_error bit = 0,
    @perfmon_increment bit = 1,
    @update_existing_row bit = 0,
    @do_raiserror bit = 1,
    @start_time_string nvarchar(25) = null,
    @duration int = null
)
AS
BEGIN

	DECLARE @current_time datetime
			,@start_time datetime
			,@delivery_rate float
			,@error_id int
			,@retcode int
			,@idle int
			,@succeed int
			,@startup int
			,@retry int
			,@failure int
			,@inprogress int
			,@lastrow_timestamp timestamp
			,@publisher sysname
			,@publisher_db sysname
			,@publication sysname
			,@agent_name nvarchar(100)
			,@perfmon_delivery_rate int
			,@agentclassname sysname

    
    -- security check
    -- only db_owner can execute this
    
    if (is_member ('db_owner') != 1)
    begin
        raiserror(14260, 16, -1)
        return (1)
    end
	/*
	** Status const defined in sqlrepl.h
	*/
	select @startup = 1
			,@succeed = 2
			,@inprogress = 3
			,@idle = 4
			,@retry = 5
			,@failure = 6
			,@current_time = GETDATE()

    -- Get named information
    select @publisher = srvname, @publisher_db = publisher_db, @publication = publication,
        @agent_name = name from master.dbo.sysservers, MSsnapshot_agents where
        id = @agent_id and
        publisher_id = srvid

    -- Update Perfmon counter
    if @perfmon_increment = 1
    begin
        if @runstatus = @startup
            dbcc incrementinstance ("SQL Replication Agents", "Running", "Snapshot", 1)
        else if (@runstatus = @succeed or @runstatus = @retry or @runstatus = @failure)
            dbcc incrementinstance ("SQL Replication Agents", "Running", "Snapshot", -1)
    end

    /* Get start_time for latest agent run */
    IF @runstatus <> 1  -- Start status
    BEGIN
        IF @start_time_string IS NULL OR @start_time_string = N''
        BEGIN
            SELECT TOP 1 @start_time = start_time,
            		@lastrow_timestamp = timestamp
                FROM MSsnapshot_history with (rowlock)
                WHERE agent_id = @agent_id
                ORDER BY timestamp DESC
        END
        ELSE
        BEGIN
            SELECT @start_time = @start_time_string
            SELECT TOP 1 @lastrow_timestamp = timestamp
              FROM MSsnapshot_history with (rowlock)
             WHERE agent_id = @agent_id
            ORDER BY timestamp DESC
        END
    END
    ELSE
    BEGIN
        WAITFOR DELAY '000:00:01'
        SELECT @current_time = DATEADD(ms, CONVERT(INT, 1000.0 * (RAND(@@spid) + RAND() + RAND())/3.0), @current_time)
        SELECT @start_time = @current_time
    END
    /* Calculate agent run duration if an explicit duration is not specified
       by the snapshot agent */

	IF @start_time is NULL or @start_time = N''
	BEGIN
		select @start_time = getdate()
    END

    IF @duration IS NULL
    BEGIN
        SELECT @duration = DATEDIFF(second, @start_time, @current_time)
    END

    /* Calculate delivery_rate */
    IF @duration <> 0
       SELECT @delivery_rate = (@delivered_commands * 1.0)/@duration
    ELSE
       SELECT @delivery_rate = 0

    -- Set Perfmon counters
    if @runstatus = @idle or @runstatus = @inprogress
    begin
        dbcc addinstance ("SQL Replication Snapshot", @agent_name)
        dbcc incrementinstance ("SQL Replication Snapshot", "Snapshot:Delivered Cmds/sec", @agent_name, @delivered_commands)
		dbcc incrementinstance ("SQL Replication Snapshot", "Snapshot:Delivered Trans/sec", @agent_name, @delivered_transactions)
    end

    /*
    ** Set error id to 0 unless the user want to log errors associate with this
    ** history message.
    */
    SELECT @error_id = 0
    IF @log_error = 1
        -- Ignore errors here. @error_id will be set to 0 in case of errors
        EXEC sys.sp_MSget_new_errorid @error_id OUTPUT

    -- Insert idle record or update if history record is already 'idle'
    IF @runstatus = @idle or @update_existing_row = 1
    begin
        -- Attempt to update the last row if it is IDLE
        UPDATE MSsnapshot_history SET runstatus = @runstatus, time = @current_time, duration = @duration,
            comments = @comments,
            delivered_transactions = @delivered_transactions,
            delivered_commands = @delivered_commands,
            delivery_rate = @delivery_rate,
			error_id = case @error_id when 0 then error_id else @error_id end
            WHERE
            agent_id = @agent_id and
            timestamp = @lastrow_timestamp and
            runstatus = @runstatus

        -- Insert idle record if there is not one
        if @@ROWCOUNT = 0
        begin
            INSERT INTO MSsnapshot_history VALUES (@agent_id, @runstatus, @start_time,
            @current_time, @duration, @comments, @delivered_transactions, @delivered_commands,
            @delivery_rate, @error_id, NULL)
        end
    end
    else
    begin
        INSERT INTO MSsnapshot_history VALUES (@agent_id, @runstatus, @start_time,
            @current_time, @duration, @comments, @delivered_transactions, @delivered_commands,
            @delivery_rate, @error_id, NULL)
    end

	-- Raise the appropriate error
	if @do_raiserror = 1
	begin
		select @agentclassname = formatmessage(14551)
		exec sys.sp_MSrepl_raiserror @agentclassname, @agent_name, @runstatus, @comments
	end

    IF @@ERROR <> 0
       RETURN (1)

    if @runstatus = 1
    begin
        select 'start_time' = sys.fn_replformatdatetime(@start_time)
    end
END

 
Last revision 2008RTM
See also

  sp_instdist (Procedure)
sp_MSadd_repl_job_unsafe (Procedure)
sp_MSagent_stethoscope (Procedure)
sp_MSdetect_nonlogged_shutdown (Procedure)
       



News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash