Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_ORAaddarticle

  No additional text.


Syntax


-- Name:
--          sp_ORAaddarticle

-- Description:
--          Add Oracle article

-- Security:
--          Internal

-- Returns:
--          Success/failure

-- Notes:
--			This stored procedure calls the procudure HREPL.PublishTable to create
--			the replication trigger, the tracking table, and the views needed to gather change data
--			for a table published in a transactional publication.  For a table published in a
--			snapshot publication, HREPL.PublishTable is called to create the article view needed
--			by the snapshot agent.

-- Owner:
--          


CREATE PROCEDURE sys.sp_ORAaddarticle
(
	@publisher			sysname,
	@owner				sysname,
	@tablename			sysname,
	@table_id			int,
	@OptimizeTrigger	bit,
	@RecreateTriggers	bit,
	@XCALL				bit,
	@article_view		nvarchar(255),
	@columns			binary(128),
	@publishedcolumns	binary(128),
	@repl_freq			int,
	@filter_clause		ntext,
	@distributortimestamp	datetime,
	@instance_id		int
)
AS	
BEGIN
	DECLARE @retcode int
	DECLARE @triggerstyle int

	SET NOCOUNT ON

	-- Define sp_IHquery support table
	create table #hquery
	(
		seq	int identity(2,1),
		cmd	nvarchar(4000)
	)
	
	-- Set trigger style
	set @triggerstyle = @OptimizeTrigger
	if @triggerstyle = 1 and @XCALL = 1
		set @triggerstyle = 2
	
	if @filter_clause is not null and DATALENGTH(@filter_clause) > 0
	BEGIN
		--  Setup Call to the remote routine to publish the table
		INSERT INTO #hquery (cmd) VALUES (N'{call HREPL.PublishTable(')
		INSERT INTO #hquery (cmd) VALUES (QUOTENAME(@owner, '''') + N',')
		INSERT INTO #hquery (cmd) VALUES (QUOTENAME(@tablename, '''') + N',')
		INSERT INTO #hquery (cmd) VALUES (CONVERT(NVARCHAR(255), @table_id) + N',')
		INSERT INTO #hquery (cmd) VALUES (CONVERT(NVARCHAR(255), @triggerstyle) + N',')
		INSERT INTO #hquery (cmd) VALUES (CONVERT(NVARCHAR(255), @RecreateTriggers) + N',')
		INSERT INTO #hquery (cmd) VALUES (QUOTENAME(@article_view, '''') + N',')
		INSERT INTO #hquery (cmd) VALUES (CONVERT(NVARCHAR(255), @repl_freq) + N',')
		INSERT INTO #hquery (cmd) VALUES (QUOTENAME(CONVERT(NVARCHAR(255), @distributortimestamp, 121), '''') + N',')
		INSERT INTO #hquery (cmd) VALUES (CONVERT(NVARCHAR(255), @instance_id) + N',')
		INSERT INTO #hquery (cmd) VALUES (N'''' + RIGHT(sys.fn_varbintohexstr(@columns), 256) + N''',')
		INSERT INTO #hquery (cmd) VALUES (N'''' + RIGHT(sys.fn_varbintohexstr(@publishedcolumns), 256) + N''',')
		INSERT INTO #hquery (cmd) VALUES (sys.fn_replquotename(@filter_clause, '''') collate database_default + N')}')
	END	
	else
	BEGIN
		--  Setup Call to the remote routine to publish the table
		INSERT INTO #hquery (cmd) VALUES (N'{call HREPL.PublishTable(')
		INSERT INTO #hquery (cmd) VALUES (QUOTENAME(@owner, '''') + N',')
		INSERT INTO #hquery (cmd) VALUES (QUOTENAME(@tablename, '''') + N',')
		INSERT INTO #hquery (cmd) VALUES (CONVERT(NVARCHAR(255), @table_id) + N',')
		INSERT INTO #hquery (cmd) VALUES (CONVERT(NVARCHAR(255), @triggerstyle) + N',')
		INSERT INTO #hquery (cmd) VALUES (CONVERT(NVARCHAR(255), @RecreateTriggers) + N',')
		INSERT INTO #hquery (cmd) VALUES (QUOTENAME(@article_view, '''') + N',')
		INSERT INTO #hquery (cmd) VALUES (CONVERT(NVARCHAR(255), @repl_freq) + N',')
		INSERT INTO #hquery (cmd) VALUES (QUOTENAME(CONVERT(NVARCHAR(255), @distributortimestamp, 121), '''') + N',')
		INSERT INTO #hquery (cmd) VALUES (CONVERT(NVARCHAR(255), @instance_id) + N',')
		INSERT INTO #hquery (cmd) VALUES (N'''' + RIGHT(sys.fn_varbintohexstr(@columns),256) + N''',')
		INSERT INTO #hquery (cmd) VALUES (N'''' + RIGHT(sys.fn_varbintohexstr(@publishedcolumns),256) + N''', NULL)}')
	END

	EXEC @retcode = sys.sp_IHquery @publisher

	IF  (@@error <> 0 OR @retcode <> 0)
	BEGIN
		RAISERROR (21790, 16, -1, @owner, @tablename, @publisher)
		RETURN (1)
	END

	RETURN(0)
END	

 
Last revision 2008RTM
See also

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