Programmatically create article script

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Replication (2005)

Programmatically create article script

Author

Topic

Henrik Svensson
Starting Member

25 Posts

Posted - 2009-06-25 : 08:48:42

Hi!

I'm sure this must have been done by someone. In our system we use transactional replication in SQL Server 2005 and I'm trying to build a T-SQL template for DDL changes that cannot be done without removing an article from its publication, such as changing the name of a primary key column. A requirement is that all subscriptions on the publication must be deleted before applying this script. So, what I need is a script template to:

1. Select, and store in a temporary table for example, all article properties for the table in question.
2. Remove the article from the publication.
3. Make the DDL changes
4. Add the article to the publication with the exact same settings as it had before, using the stored information from step 1 above (we don't use column filters in this case, so column level information is not important).

I know there is a sysarticles view, but since there is no 1:1 correlation between sp_addarticle and the contents in sysarticles and the fact that not all parameters in sp_addarticle are present in sysarticles something more is also needed. If anyone has any suggestions or links to provide I would be very grateful.

Thanks in advance.

With best regards,
Henrik Svensson

-----
"Rhubarb, that is Barbapapa backwards. It's called a palindrome."

Henrik Svensson
Starting Member

25 Posts

Posted - 2009-08-17 : 09:16:17

Here is one type of solution. Perhaps someone facing the same problem might have at least some use of it...

------------------------------8<------------------------------
/*

Template script for making DDL changes to ONE article in ONE publication.

The template code retreives the article properties and stores them in a temporary table
and then re-adds the article to its publication after the DDL changes have been applied.
The DDL changes need to be added manually.

Instructions:
- Locate the row starting with "SET @TableName = 'tableName'" and set the table name.
- Add code with DDL changes in the "Put your DDL changes here" section.

NOTE:
This script will not work if the same table is included as an article in several publications.
This script will not work if column level filters are set on the article.
This script will not work if the article name and table name differ.

*/

SET NOCOUNT ON

IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE name LIKE '#helparticle%' AND type = 'U')
DROP TABLE #helparticle
GO

IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE name LIKE '#helppublication%' AND type = 'U')
DROP TABLE #helppublication
GO

CREATE TABLE #helppublication (publication_name sysname, table_id int)
GO

CREATE TABLE #helparticle (
[article id] int PRIMARY KEY CLUSTERED,
[article name] sysname NULL,
[base object] nvarchar(257) NULL,
[destination object] sysname NULL,
[synchronization object] nvarchar(257) NULL,
[type] smallint NULL,
[status] tinyint NULL,
[filter] nvarchar(257) NULL,
[description] nvarchar(255) NULL,
[insert_command] nvarchar(255) NULL,
[update_command] nvarchar(255) NULL,
[delete_command] nvarchar(255) NULL,
[creation script path] nvarchar(255) NULL,
[vertical partition] bit NULL,
[pre_creation_cmd] tinyint NULL,
[filter_clause] ntext NULL,
[schema_option] binary(8) NULL,
[dest_owner] sysname NULL,
[source_owner] sysname NULL,
[unqua_source_object] sysname NULL,
[sync_object_owner] sysname NULL,
[unqualified_sync_object] sysname NULL,
[filter_owner] sysname NULL,
[unqua_filter] sysname NULL,
[auto_identity_range] int NULL,
[publisher_identity_range] int NULL,
[identity_range] bigint NULL,
[threshold] bigint NULL,
[identityrangemanagementoption] int NULL,
[fire_triggers_on_snapshot] bit NULL)
GO

DECLARE @TableName sysname
DECLARE @TableIsPublished bit
DECLARE @TableId int

SET @TableName = 'tableName' -- set name of table that needs to be removed from, modified, and then added to publication (if published).

SELECT
@TableIsPublished = is_published,
@TableId = [object_id]
FROM
sys.objects
WHERE
name = @TableName
AND
type = 'U'

IF @TableIsPublished = 1
BEGIN
DECLARE @creation_script nvarchar(255)
DECLARE @del_cmd nvarchar(255)
DECLARE @description nvarchar(255)
DECLARE @dest_table sysname
DECLARE @filter int
DECLARE @filter_clause sysname
DECLARE @ins_cmd nvarchar(255)
DECLARE @name sysname
DECLARE @objid int
DECLARE @pubid int
DECLARE @pre_creation_cmd tinyint
DECLARE @status tinyint
DECLARE @sync_objid int
DECLARE @type tinyint
DECLARE @upd_cmd nvarchar(255)
DECLARE @schema_option binary(8)
DECLARE @dest_owner sysname
DECLARE @ins_scripting_proc int
DECLARE @del_scripting_proc int
DECLARE @upd_scripting_proc int
DECLARE @custom_script nvarchar(2048)
DECLARE @fire_triggers_on_snapshot_bit bit
DECLARE @fire_triggers_on_snapshot_nvarchar5 nvarchar(5)

DECLARE @PublicationName sysname
SELECT @PublicationName = syspub.name FROM sysarticles sysart
INNER JOIN syspublications syspub ON sysart.pubid = syspub.pubid
WHERE sysart.[objid] = @TableId

INSERT INTO #helparticle
EXEC sp_helparticle @PublicationName, @TableName

ALTER TABLE #helparticle ADD publication_name sysname NULL, article_dropped bit
INSERT INTO #helppublication SELECT @PublicationName, @TableId
END
ELSE
BEGIN
ALTER TABLE #helparticle ADD publication_name sysname NULL, article_dropped bit
END
GO

IF EXISTS (SELECT * FROM #helparticle)
BEGIN
DECLARE @PublicationName sysname
DECLARE @TableId int
DECLARE @TableName sysname

SELECT @PublicationName = publication_name FROM #helppublication
SELECT @TableId = table_id FROM #helppublication
SELECT @TableName = name FROM sys.objects WHERE [object_id] = @TableId

UPDATE #helparticle SET publication_name = @PublicationName, article_dropped = 0

IF EXISTS (SELECT * FROM syssubscriptions syssub INNER JOIN sysarticles sysart ON sysart.artid = syssub.artid WHERE sysart.[objid] = @TableId)
BEGIN
PRINT 'Table "'+@TableName+'" is included in publication "'+@PublicationName+'" that has at least one subscriber. Drop subscription(s) manually, then re-run this script.'
UPDATE #helparticle SET article_dropped = 0
END
ELSE BEGIN
PRINT 'Dropping article '+@TableName+'.'
EXEC sp_droparticle @publication = @PublicationName, @article = @TableName
UPDATE #helparticle SET article_dropped = 1
END
END
GO

----------------------------------------------------------
-- Put your DDL changes here (and other changes, e.g. code to temporary store data to add after table changes have been applied).

-- IMPORTANT: Check if article was dropped within EACH batch by using "IF EXISTS (SELECT * FROM #helparticle WHERE article_dropped = 1) OR NOT EXISTS (SELECT * FROM #helparticle) BEGIN [...] END"

----------------------------------------------------------

--- Add article again:
DECLARE @publication_name sysname
DECLARE @type sysname
DECLARE @article sysname
DECLARE @destination_table sysname
DECLARE @source_object sysname
DECLARE @vertical_partition nvarchar(5)
DECLARE @filter nvarchar(257)
DECLARE @sync_object nvarchar(257)
DECLARE @ins_cmd nvarchar(255)
DECLARE @upd_cmd nvarchar(255)
DECLARE @del_cmd nvarchar(255)
DECLARE @creation_script nvarchar(255)
DECLARE @pre_creation_cmd nvarchar(10)
DECLARE @filter_clause nvarchar(max)
DECLARE @schema_option binary(8)
DECLARE @destination_owner sysname
DECLARE @status tinyint
DECLARE @source_owner sysname
DECLARE @sync_object_owner sysname
DECLARE @filter_owner sysname
DECLARE @auto_identity_range nvarchar(5)
DECLARE @pub_identity_range bigint
DECLARE @identity_range bigint
DECLARE @threshold bigint
DECLARE @force_invalidate_snapshot bit
DECLARE @use_default_datatypes bit
DECLARE @identityrangemanagementoption nvarchar(10)
DECLARE @publisher sysname
DECLARE @fire_triggers_on_snapshot nvarchar(5)
DECLARE @description nvarchar(255)

IF EXISTS (SELECT * FROM #helparticle WHERE article_dropped = 1)
BEGIN

SELECT
@publication_name = publication_name,
@article = [article name],
@destination_table = [destination object],
@source_object = [unqua_source_object],
@filter = [filter],
@description = @description,
--special case: this variable should be NULL if sync_object was automatically created, which will give it the prefix "syncobj_", so we look for that string on the line below:
@sync_object = CASE WHEN [synchronization object] IS NOT NULL THEN CASE WHEN CHARINDEX('syncobj_',[synchronization object]) <> 0 THEN NULL ELSE [synchronization object] END ELSE [synchronization object] END,
@ins_cmd = [insert_command],
@upd_cmd = [update_command],
@del_cmd = [delete_command],
@creation_script = [creation script path],
@filter_clause = [filter_clause],
@schema_option = [schema_option],
@destination_owner = [dest_owner],
@status = [status],
@source_owner = [source_owner],
@pub_identity_range = [publisher_identity_range],
@identity_range = [identity_range],
@threshold = [threshold],
@force_invalidate_snapshot = 1, --in this case we have already dropped the subscriptions, so there must be a new snapshot in any case.
@use_default_datatypes = 1, --only of interest when publishing from Oracle
@publisher = NULL --we only use SQL Servers as publishers, so this parameter should be set to NULL.
FROM #helparticle

IF NOT @sync_object IS NULL
SELECT @sync_object_owner = [sync_object_owner] FROM #helparticle
ELSE
SET @sync_object_owner = NULL

IF NOT @filter IS NULL
SELECT @filter_owner = [filter_owner] FROM #helparticle
ELSE
SET @filter_owner = NULL

IF 1 = (SELECT [vertical partition] FROM #helparticle)
SET @vertical_partition = 'true'
ELSE
SET @vertical_partition = 'false'

IF 1 = (SELECT [fire_triggers_on_snapshot] FROM #helparticle)
SET @fire_triggers_on_snapshot = 'true'
ELSE
SET @fire_triggers_on_snapshot = 'false'

SELECT @type =
CASE
WHEN [type] = 1 THEN 'logbased'
WHEN [type] = 3 THEN 'logbased manualfilter'
WHEN [type] = 5 THEN 'logbased manualview'
WHEN [type] = 7 THEN 'logbased manualboth'
WHEN [type] = 8 THEN 'proc exec'
WHEN [type] = 24 THEN 'serializable proc exec'
WHEN [type] = 32 THEN 'proc schema only'
WHEN [type] = 64 THEN 'view schema only'
WHEN [type] = 128 THEN 'func schema only'
ELSE 'logbased' --default
END
FROM
#helparticle

SELECT @pre_creation_cmd =
CASE
WHEN [pre_creation_cmd] = 0 THEN 'none'
WHEN [pre_creation_cmd] = 1 THEN 'delete'
WHEN [pre_creation_cmd] = 2 THEN 'drop'
WHEN [pre_creation_cmd] = 3 THEN 'truncate'
ELSE 'drop' --default
END
FROM
#helparticle

SELECT @auto_identity_range =
CASE
WHEN [auto_identity_range] = 1 THEN 'true'
WHEN [auto_identity_range] = 0 THEN 'false'
ELSE NULL
END
FROM
#helparticle

SELECT @identityrangemanagementoption =
CASE
WHEN identityrangemanagementoption = 0 THEN 'none'
WHEN identityrangemanagementoption = 1 THEN 'manual'
WHEN identityrangemanagementoption = 2 THEN 'auto'
ELSE NULL --default
END
FROM
#helparticle

PRINT 'Adding article ' + @article + '.'

EXEC sp_addarticle
@publication = @publication_name,
@article = @article,
@source_table = NULL, --do not use, use source_object instead
@destination_table = @destination_table,
@vertical_partition = @vertical_partition,
@type = @type,
@filter = @filter,
@sync_object = @sync_object,
@ins_cmd = @ins_cmd,
@del_cmd = @del_cmd,
@upd_cmd = @upd_cmd,
@creation_script = @creation_script,
@description = @description,
@pre_creation_cmd = @pre_creation_cmd,
@filter_clause = @filter_clause,
@schema_option = @schema_option,
@destination_owner = @destination_owner,
@status = @status,
@source_owner = @source_owner,
@sync_object_owner = @sync_object_owner,
@filter_owner = @filter_owner,
@source_object = @source_object,
@artid = NULL,
@auto_identity_range = @auto_identity_range,
@pub_identity_range = @pub_identity_range,
@identity_range = @identity_range,
@threshold = @threshold,
@force_invalidate_snapshot = @force_invalidate_snapshot ,
@use_default_datatypes = @use_default_datatypes,
@identityrangemanagementoption = @identityrangemanagementoption,
@publisher = @publisher,
@fire_triggers_on_snapshot = @fire_triggers_on_snapshot

END

IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE name LIKE '#helparticle%' AND type = 'U')
DROP TABLE #helparticle
GO

IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE name LIKE '#helppublication%' AND type = 'U')
DROP TABLE #helppublication
GO

------------------------------>8------------------------------

With best regards,
Henrik Svensson

-----
"Rhubarb, that is Barbapapa backwards. It's called a palindrome."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources