Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MScreate_peer_tables

  No additional text.


Syntax
CREATE PROCEDURE sys.sp_MScreate_peer_tables
AS
BEGIN
    DECLARE @retcode bit

    begin transaction tran_sp_MScreate_peer_tables
    save transaction tran_sp_MScreate_peer_tables

    IF OBJECT_ID(N'MSpeer_lsns', 'U') is NULL
    BEGIN
        CREATE TABLE dbo.MSpeer_lsns
        (
            id                         	int identity(1,1) primary key nonclustered,
            last_updated               	datetime default getdate(),
            originator                  sysname,
            originator_db               sysname,
            originator_publication      sysname,
            originator_publication_id   int,
            originator_db_version       int,
            originator_lsn              varbinary(16),
            originator_version	    int NULL, --server build version of the peer
            originator_id		    int NULL -- 4 bytes are used
        )
        IF @@ERROR <> 0
            GOTO UNDO

        EXEC @retcode = dbo.sp_MS_marksystemobject N'MSpeer_lsns'
        IF @@ERROR <> 0 or @retcode <> 0
            GOTO UNDO

        CREATE UNIQUE CLUSTERED INDEX uci_MSpeer_lsns
    	ON dbo.MSpeer_lsns
    	(
			originator,
			originator_db,
			originator_publication_id,
			originator_db_version,
			originator_lsn
    	)
    END

    IF EXISTS (select * from sys.objects where name = 'syspublications')
    BEGIN
    	    IF OBJECT_ID(N'MSpeer_request', 'U') is NULL
	    BEGIN
	        CREATE TABLE dbo.MSpeer_request
       	 (
	            id            int    identity(1,1),
	            publication    sysname,
	            sent_date    datetime default getdate(),
	            description    nvarchar(4000)
       	 )
	        IF @@ERROR <> 0
       	     GOTO UNDO

	        EXEC @retcode = dbo.sp_MS_marksystemobject N'MSpeer_request'
       	 IF @@ERROR <> 0 or @retcode <> 0
	            GOTO UNDO
	    END

	    IF OBJECT_ID(N'MSpeer_response', 'U') is NULL
	    BEGIN
       	 CREATE TABLE dbo.MSpeer_response
	        (
       	     request_id        int,
	            peer            sysname,
	            peer_db            sysname,
	            received_date    datetime NULL
       	 )
	        IF @@ERROR <> 0
       	     GOTO UNDO

	        EXEC @retcode = dbo.sp_MS_marksystemobject N'MSpeer_response'
	        IF @@ERROR <> 0  or @retcode <> 0
       	     GOTO UNDO
	    END

	    IF OBJECT_ID(N'MSpeer_topologyrequest', 'U') is NULL
	    BEGIN
       	 CREATE TABLE dbo.MSpeer_topologyrequest
	        (
       	     id            int    identity(1,1),
	            publication    sysname,
       	     sent_date    datetime default getdate()
	        )
	        IF @@ERROR <> 0
       	     GOTO UNDO

	        EXEC @retcode = dbo.sp_MS_marksystemobject N'MSpeer_topologyrequest'
	        IF @@ERROR <> 0  or @retcode <> 0
       	     GOTO UNDO
	    END

	    IF OBJECT_ID(N'MSpeer_topologyresponse', 'U') is NULL
	    BEGIN
	        CREATE TABLE dbo.MSpeer_topologyresponse
       	 (
	            request_id        int,
	            peer            sysname,
	            peer_version	int NULL,  --server build version of the peer
	            peer_db            sysname,
	            originator_id	int  NULL,  -- 4  bytes are used
	            peer_conflict_retention int NULL,
	            received_date    datetime NULL,
	            connection_info    XML
       	 )
	        IF @@ERROR <> 0
       	     GOTO UNDO

	        EXEC @retcode = dbo.sp_MS_marksystemobject N'MSpeer_topologyresponse'
	        IF @@ERROR <> 0  or @retcode <> 0
       	     GOTO UNDO
	    END


	    IF OBJECT_ID(N'MSpeer_originatorid_history', 'U') is NULL
	    BEGIN
	        CREATE TABLE dbo.MSpeer_originatorid_history
	        (
	            originator_publication sysname,
	            originator_id		int not null, -- 4 bytes are used
	            originator_node        sysname,
	            originator_db            sysname,
	            originator_db_version int not null,
	            originator_version	int not null,  --server build version of the peer
		     inserted_date    datetime not null default getdate()
	        )
	        IF @@ERROR <> 0
       	     GOTO UNDO

	        EXEC @retcode = dbo.sp_MS_marksystemobject N'MSpeer_originatorid_history'
	        IF @@ERROR <> 0 or @retcode <> 0
       	     GOTO UNDO

	       CREATE UNIQUE CLUSTERED INDEX uci_MSpeer_originatorid_history
	    	ON dbo.MSpeer_originatorid_history
	    	(
	    		originator_publication,
	    		originator_id,
	    		originator_node,
	    		originator_db,
	    		originator_db_version
    		)
	    END

	    IF OBJECT_ID(N'MSpeer_conflictdetectionconfigrequest', 'U') is NULL
	    BEGIN
	        CREATE TABLE dbo.MSpeer_conflictdetectionconfigrequest
	        (
	            id					int   identity(1,1) not null primary key,
	            publication				sysname,
	            sent_date				datetime not null default getdate(),
	            timeout				int not null, --seconds
	            modified_date			datetime not null default getdate(),
	            progress_phase			nvarchar(32) not null,
	            phase_timed_out		bit not null
	        )
	        IF @@ERROR <> 0
       	     GOTO UNDO

	        EXEC @retcode = dbo.sp_MS_marksystemobject N'MSpeer_conflictdetectionconfigrequest'
	        IF @@ERROR <> 0 or @retcode <> 0
       	     GOTO UNDO
   		END

	  IF OBJECT_ID(N'MSpeer_conflictdetectionconfigresponse', 'U') is NULL
	  BEGIN
       	CREATE TABLE dbo.MSpeer_conflictdetectionconfigresponse
	        (
       	     request_id				int not null,
	            peer_node			       sysname,
	            peer_db				sysname,
	            peer_version			int NULL,  --server build version of the peer
	            peer_db_version		int NULL,
       	     is_peer				bit NULL, --once set, later rounds would expect response from it
	            conflictdetection_enabled bit NULL,
	            originator_id			int NULL,
	            peer_conflict_retention 	int NULL,
	            peer_continue_onconflict  bit NULL,
	            peer_subscriptions		xml NULL, --list of (sub_node, sub_db)
	            progress_phase			nvarchar(32) not null,
		     modified_date			datetime default getdate()
       	 )
	        IF @@ERROR <> 0
       	     GOTO UNDO

	        EXEC @retcode = dbo.sp_MS_marksystemobject N'MSpeer_conflictdetectionconfigresponse'
       	 IF @@ERROR <> 0 or @retcode <> 0
	            GOTO UNDO

	       CREATE UNIQUE CLUSTERED INDEX uci_MSpeer_conflictdetectionconfigresponse
	    	ON dbo.MSpeer_conflictdetectionconfigresponse
	    	(
    			request_id,
			peer_node,
            		peer_db
    		)
      END
    END

	--add conflict detection alert on subscriber
    declare @alert_name nvarchar(max) = formatmessage(22827)
				,@alert_id int = 22815
				,@category_name sysname

 	SELECT	@category_name = name
    	FROM	msdb.dbo.syscategories
    	WHERE	category_id = 20

    IF NOT EXISTS
    (
    	SELECT	*
    	FROM	msdb.dbo.sysalerts
    	WHERE	message_id = @alert_id
    )
    BEGIN
        EXEC @retcode = msdb.dbo.sp_add_alert	@enabled = 0,
        										@name = @alert_name,
        										@category_name = @category_name,
        										@message_id = @alert_id

        IF (@@ERROR != 0 OR @retcode != 0)
        BEGIN
             goto UNDO
        END
    END


    commit transaction tran_sp_MScreate_peer_tables

    return 0
UNDO:
    rollback transaction tran_sp_MScreate_peer_tables
    commit transaction tran_sp_MScreate_peer_tables

    return 1
END

 
Last revision 2008RTM
See also

  sp_MScreate_pub_tables (Procedure)
sp_MScreate_sub_tables_internal (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