Share via

  • Article

How to: Specify Schema Options (Replication Transact-SQL Programming)

When publishing a table or view, replication allows you to control the object creation options that are replicated for the published object. Schema options can be set programmatically when an article is created using replication stored procedures. They can also be changed at a later time. If you do not explicitly specify these options for an article, a default set of options will be defined.

Note

The default schema options when using replication stored procedures may differ from the default options when articles are adding using Microsoft SQL Server Management Studio.

Schema options are specified as a hexadecimal value that is the | (Bitwise OR) result of one or more options. For more information, see sp_addarticle and sp_addmergearticle.

Note

You must convert schema option values from binary to int before performing a bitwise operation. For more information, see CONVERT (Transact-SQL).

To specify schema options when defining an article for a snapshot or transactional publication

  • At the Publisher on the publication database, execute sp_addarticle. Specify the name of the publication to which the article belongs for @publication, a name for the article for @article, the database object being published for @source_object, the type of database object for @type, and the | (Bitwise OR) result of one or more schema options for @schema_option. For more information, see How to: Define an Article (Replication Transact-SQL Programming).

To specify schema options when defining an article for a merge publication

To change schema options for an existing article in a snapshot or transactional publication

  1. At the Publisher on the publication database, execute sp_helparticle. Specify the name of the publication to which the article belongs for @publication and the name of the article for @article. Note the value of the schema_option column in the result set.

  2. Execute a & (Bitwise AND) operation using the value from step 1 and the desired schema option value to determine if the option is set.

    • If the result is 0, the option is not set.

    • If the result is the option value, the option is already set.

  3. If the option is not set, execute a | (Bitwise OR) operation using the value from step 1 and the desired schema option value.

  4. At the Publisher on the publication database, execute sp_changearticle. Specify the name of the publication to which the article belongs for @publication, the name of the article for @article, a value of schema_option for @property, and the hexadecimal result from step 3 for @value.

  5. Run the Snapshot Agent to generate a new snapshot. For more information, see How to: Create the Initial Snapshot (Replication Transact-SQL Programming).

To change schema options for an existing article in a merge publication

  1. At the Publisher on the publication database, execute sp_helpmergearticle. Specify the name of the publication to which the article belongs for @publication and the name of the article for @article. Note the value of the schema_option column in the result set.

  2. Execute a & (Bitwise AND) operation using the value from step 1 and the desired schema option value to determine if the option is set.

    • If the result is 0, the option is not set.

    • If the result is the option value, the option is already set.

  3. If the option is not set, execute a | (Bitwise OR) operation using the value from step 1 and the desired schema option value.

  4. At the Publisher on the publication database, execute sp_changemergearticle. Specify the name of the publication to which the article belongs for @publication, the name of the article for @article, a value of schema_option for @property, and the hexadecimal result from step 3 for @value.

  5. Run the Snapshot Agent to generate a new snapshot. For more information, see How to: Create the Initial Snapshot (Replication Transact-SQL Programming).