Home Microsoft SQL Server DigiMailing iRN Contact


  No additional text.


-- Name: sp_MScreatemergedynamicsnapshot

-- Description:
--      This procedure checks if a dynamic snapshot job is available
--      with the given filter criterion. If not it creates such a job
--      by calling sp_MSaddmergedynamicsnapshotjob. If one already
--      exists, it starts this job and subsequently waits for it complete.
--      The procedure returns success or failure depending on whether the
--      job succeded or failed.

-- Notes: 1) The dynamic snapshot location is computed from the working directory
--           on the distributor (got from sp_helpdistributor)
--           or the alternate snapshot folder for the publication if one is specified.
--           this is done by calling proc sp_MSgetpartitionsnapshotfolder
--		  2) This procedure will not check whether the given path is already in
--			 use by another dynamic snapshot generation job. Sharing the same
--			 dynamic snapshot location among different dynamic snapshot
--			 generation jobs can lead to file corruption and/or snapshot files
--			 being overwritten.	
--		  3) calls sp_MSaddmergedynamicsnapshotjob to add the job if it does not exist
--        4) starts the dynamic snapshot job and waits for it to finish. Checks if the
--           job finished by attempting to acquire the applock that the dynamic snapshot
--           will release when it is done.

-- Parameters: @publication sysname (mandatory)

-- Returns: 0 - succeeded
--			1 - failed

-- Security: Only users in the PAL role for the publication @publication or
--           db_owners or sysadmins can run this proc.
-- Requires Certificate signature for catalog access

create procedure sys.sp_MScreatemergedynamicsnapshot (
    @publication sysname
    declare @retcode int
    declare @pub_uses_host_name_for_filtering bit
    declare @pub_uses_suser_sname_for_filtering bit
    declare @host_name sysname
    declare @suser_sname sysname
    declare @jobid uniqueidentifier
    declare @pubid uniqueidentifier
    declare @snapshot_location nvarchar(255)
    declare @partition_id int
    declare @when_generated datetime
    declare @got_applock int
    declare @wait_counter int
    declare @agentid int
    declare @timestamp timestamp

    ** Security Check and parameter check for @publication
    SELECT @pubid = NULL
    exec @retcode = sys.sp_MSmerge_validate_publication_presence @publication, NULL, NULL, @pubid output
    if @retcode <> 0 or @@error <> 0
        return 1

    set @pub_uses_host_name_for_filtering = 0
    set @pub_uses_suser_sname_for_filtering = 0
    select @host_name = NULL
    select @suser_sname = NULL
    exec sys.sp_MSget_dynamic_filtering_information @pubid = @pubid,
                @uses_host_name = @pub_uses_host_name_for_filtering OUTPUT,
                @uses_suser_sname = @pub_uses_suser_sname_for_filtering OUTPUT
    if @@error <> 0
        goto FAILURE

    if @pub_uses_host_name_for_filtering = 1
        select @host_name = HOST_NAME()

    if @pub_uses_suser_sname_for_filtering = 1
        select @suser_sname = SUSER_SNAME()

    -- find the partition id for the subscriber
    -- deriving the partition id here and not accepting it as a parameter (from the merge agent)
    -- helps in avoiding false identity.
    select @partition_id = -1
    exec @retcode = sys.sp_MSget_subscriber_partition_id
                            @publication = @publication,
                            @partition_id = @partition_id OUTPUT,
                            @maxgen_whenadded = NULL,
                            @host_name_override = @host_name,
                            @suser_sname_override = @suser_sname
    if @@error <> 0 or @retcode <> 0 or @partition_id = -1
        raiserror(20629, 16, -1)
        return 1

    -- check if a job already exists for the given dynamic_filter_login and dynamic_filter_hostname
    select @jobid = NULL
    select @jobid = job_id, @agentid = agent_id from dbo.MSdynamicsnapshotjobs
        where pubid = @pubid and
              ((@suser_sname is NULL and dynamic_filter_login is NULL) or dynamic_filter_login = @suser_sname)  and
              ((@host_name is NULL and dynamic_filter_hostname is NULL) or dynamic_filter_hostname = @host_name)
    if (@jobid is NULL)
        -- we need to create the job here
        -- sp_MSaddmergedynamicsnapshotjob will select the location correctly if none is specified.
        select @snapshot_location = NULL

        -- set the frequency type of the job to 1 to indicate run on demand
        exec @retcode = sys.sp_MSaddmergedynamicsnapshotjob
                            @publication = @publication,
                            @dynamic_filter_login = @suser_sname,
                            @dynamic_filter_hostname = @host_name,
                            @dynamic_snapshot_location = @snapshot_location,
                            @dynamic_snapshot_jobname = NULL,
                            @dynamic_snapshot_jobid = @jobid OUTPUT,
                            @frequency_type = 1,
                            @dynamic_snapshot_agentid = @agentid output,
                            @ignore_select = 1
        if @@error <> 0 or @retcode <> 0 or @jobid is NULL
            raiserror(20632, 16, -1)
            return 1
        select @got_applock = -1
        -- now that the job exists check if someone else is running the dynamic snapshot now
        -- check this by attempting to obtain the dynamic snapshot applock with zero wait.
        exec @retcode = sys.sp_MSgetdynamicsnapshotapplock @publication, @partition_id, @got_applock OUTPUT, 0
        if @@error <> 0 or @retcode <> 0
            goto FAILURE

        -- if we got the applock it means that the job is not already running and hence we need to start it
        if @got_applock >= 0
            exec @retcode = sys.sp_MSreleasedynamicsnapshotapplock @publication, @partition_id
            if @@error <> 0
                goto FAILURE
            -- if we did not get the applock wait for the applock till 2000. Once we get
            -- the applock check once more to see if the dynamic snapshot location has been updated.
            exec @retcode = sys.sp_MSgetdynamicsnapshotapplock @publication, @partition_id, @got_applock OUTPUT, 2000
            if @@error <> 0 or @retcode <> 0
                goto FAILURE
            if @got_applock >= 0
                exec @retcode = sys.sp_MSreleasedynamicsnapshotapplock @publication, @partition_id
                if @@error <> 0
                    goto FAILURE

                select @snapshot_location = NULL
                select @snapshot_location = dynamic_snapshot_location from dbo.MSmerge_dynamic_snapshots where partition_id = @partition_id
                if @snapshot_location is not NULL
                    --select @dynamic_snapshot_location = @snapshot_location
                    select @snapshot_location, 0, 0x00000
                    return 0

    -- start the dynamic snapshot job on the distributor
    -- Get distributor information for RPC
    declare @rpcsrvname sysname
    declare @distributor sysname
    declare @distproc nvarchar(300)
    declare @distribdb sysname
    declare @retcode2 int
    declare @command nvarchar(1000)

    exec @retcode = sys.sp_MSrepl_getdistributorinfo @distributor = @distributor output,
                                           @distribdb = @distribdb output,
                                           @rpcsrvname = @rpcsrvname output
    if @@error <> 0 or @retcode <> 0
        goto FAILURE

    -- before starting the job get the max timestamp from the MSsnaphsot_history table so that
    -- we can look for history information higher than that timestamp
    select @distproc = quotename(rtrim(@rpcsrvname)) + '.' + quotename(@distribdb) + '.sys.sp_executesql '
    select @command = N' exec @retcode2 = ' + quotename(@distribdb) + '.sys.sp_MSgetmaxsnapshottimestamp ' + convert(nvarchar(5),@agentid) + ', @timestamp output'
    EXEC @retcode = @distproc @command, N'@timestamp timestamp output, @retcode2 int output', @retcode2=@retcode2 output, @timestamp=@timestamp output
    if @@error <> 0 or @retcode <> 0 or @retcode2 <> 0
        raiserror(20655, 16, -1)
        goto FAILURE

    select @distproc = rtrim(@rpcsrvname) + N'.msdb.dbo.sp_start_job'
    exec @retcode = @distproc @job_id = @jobid
    if @@error <> 0 or @retcode <> 0
        raiserror(20633, 16, -1)
        goto FAILURE

    -- now return the the job id so that the merge agent can monitor the job
    select '', @agentid, @timestamp
    return 0

    raiserror(20628, 16, -1)
    return 1

Last revision 2008RTM
See also

  sp_MSdrop_rladmin (Procedure)


  Query a named instance
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
Zoekmachine optimalisatie
SQL Servers hidden objects
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
sIFR; de combinatie tussen HTML en Flash