Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSrepl_getsubscription_status_hsnapshot

  No additional text.


Syntax


-- Name:
--          sp_MSrepl_getsubscription_status_hsnapshot

-- Description:
--          Specialized stored procedure for heterogeneous snapshot agent to
--          determine subscription status.

-- Security:
--          Internal
-- Requires Certificate signature for catalog access

-- Returns:
--          Result set:  Subscription status

-- Owner:
--          sward

create procedure sys.sp_MSrepl_getsubscription_status_hsnapshot
(
	@publication	sysname,
	@article	sysname,
	@publisher	sysname
)
AS
BEGIN
    SET NOCOUNT ON

    -- Declarations.
    DECLARE @retcode	int
    DECLARE @subscription_status int
    DECLARE @eventcount	int
    DECLARE @InsColumnList	nvarchar(200)
    DECLARE @SelectColumnList	nvarchar(200)
    DECLARE @articleid int
    DECLARE @tableid   int
    DECLARE @publicationid int

    -- Initialization
    SET @subscription_status = 0

    -- Security Check
    exec @retcode = sys.sp_MSreplcheck_publish
    if @@ERROR <> 0 or @retcode <> 0
        return(1)

    -- Check if the database is published.
    IF sys.fn_MSrepl_istranpublished(DB_NAME(),1) <> 1
    BEGIN
        RAISERROR (14013, 16, -1)
        RETURN (1)
    END

    -- Parameter Check:  @publication.
    -- Check to make sure that the publication name
    -- conforms to the rules for identifiers and that the publication
    -- actually exists.  Disallow NULL.
    IF @publication IS NULL
    BEGIN
        RAISERROR (14043, 16, -1, '@publication', 'sp_MSrepl_getsubscription_status_hsnapshot')
        RETURN (1)
    END

    EXECUTE @retcode = sys.sp_validname @publication

    IF @retcode <> 0
    BEGIN
        RETURN (1)
    END

    -- Parameter Check:  @article.
    -- Check to make sure that the article name conforms
    -- to the rules for identifiers and that the article
    -- actually exists.  Disallow NULL.
    IF @article IS NULL
    BEGIN
        RAISERROR (14043, 16, -1, '@article', 'sp_MSrepl_getsubscription_status_hsnapshot')
        RETURN (1)
    END

    CREATE TABLE #dist_agent_status
	(
		status  int NULL,
		job_id 	VARBINARY(16) NULL,
	)

    INSERT INTO #dist_agent_status
    EXECUTE @retcode = sys.sp_distagentstatus @publication, @publisher

    IF @retcode <> 0
    BEGIN
        RETURN (1)
    END

    SELECT	@subscription_status =
			case
				when sub.status = 2 and das.status = 0 then 0
				else sub.status
			end,
		@articleid = art.article_id,
		@tableid   = art.table_id,
		@publicationid = art.publication_id
    FROM	master.dbo.sysservers ss,
 			master.dbo.sysservers ssub,   			
			dbo.MSpublications mpub,
			dbo.IHarticles art WITH (INDEX( idx_IHarticles_name )),
			dbo.IHsubscriptions sub WITH (INDEX( idx_IHsubscriptions_article_id )),
			#dist_agent_status das
    WHERE	UPPER(ss.srvname collate database_default) = UPPER(@publisher) collate database_default
      AND	mpub.publisher_id = ss.srvid
      AND	mpub.publication = @publication
      AND	art.publisher_id = ss.srvid
      AND	art.publication_id = mpub.publication_id
      AND	art.name = @article
      AND	sub.article_id = art.article_id
      AND	das.job_id = sub.distribution_jobid
      AND   sub.srvid = ssub.srvid

   DROP TABLE #dist_agent_status

    -- If @subscription_status IS NULL, there were no subscriptions.
    -- Return an empty result set.
    IF @subscription_status IS NULL
    BEGIN
        RETURN (0)
    END	

    IF @subscription_status <> 3
    BEGIN
        SELECT @subscription_status as [subscription status]
        RETURN (0)
    END	

    -- If the subscription status is initiated (3), we need to also check event status at the
    -- Oracle publisher to determine whether the snapshot agent failed to post a SYNCDONE event
    -- to indicate that the snapshot completed. If no SYNCDONE event was posted but we are still
    -- in reconcilation for the article, return subscribed (1) rather than initiated to force the
    -- snapshot agent to generate a new snapshot.

    -- Define sp_ORAremotequery support table
    create table #hquery
    (
        seq	int identity(2,1),
        cmd	nvarchar(4000)
    )

    -- Define #EVENT support table
    create table #EVENT
    (
        eventcount int
    )

    -- Populate a temp table with the snapshot event information from the publisher
    SELECT @InsColumnList    = 'eventcount'
    SELECT @SelectColumnList = 'EVENTCOUNT'

    -- Query publisher for event information
    INSERT INTO #hquery(cmd) VALUES ('SELECT COUNT(*) AS EVENTCOUNT FROM HREPL_EVENT ')
    INSERT INTO #hquery(cmd) VALUES ('WHERE EVENT_ARTICLE_ID = ' + CONVERT(NVARCHAR(10),@articleid) )
    INSERT INTO #hquery(cmd) VALUES ('  AND EVENT_TABLE_ID = ' + CONVERT(NVARCHAR(10),@tableid) )
    INSERT INTO #hquery(cmd) VALUES ('  AND EVENT_PUBLICATION_ID = ' + CONVERT(NVARCHAR(10),@publicationid) )
    INSERT INTO #hquery(cmd) VALUES ('  AND EVENT_OPERATION IN (6, 11) ')
    INSERT INTO #hquery(cmd) VALUES ('  AND EVENT_POLLID = 0 ')

    EXEC @retcode = sp_ORAremotequery
            @Server		= @publisher,
            @InsTable		= '#EVENT',
            @InsColumnList	= @InsColumnList,
            @SelectColumnList	= @SelectColumnList

    if @@ERROR <> 0 or @retcode <> 0
    BEGIN
        -- If event information cannot be returned from the publisher
        -- return the subscription status obtained from the distributor
        DROP TABLE #EVENT
        DROP TABLE #hquery
        SELECT @subscription_status as [subscription status]
        RETURN (0)
    END

    SET @eventcount = (SELECT eventcount from #EVENT)

    -- If the event count is 1, no SYNCDONE has been registered and the snapshot agent terminated before completion.
    -- Return a status of subscribed to the snapshot agent to force the generation of a new snapshot.
    IF @eventcount = 1
    BEGIN
        SELECT @subscription_status = 1
    END

    DROP TABLE #EVENT
    DROP TABLE #hquery

    SELECT @subscription_status as [subscription status]
    RETURN (0)
END    	

 
Last revision 2008RTM
See also

  sp_getsubscription_status_hsnapshot (Procedure)
sp_MSdrop_repltran (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