sql server replication: I am trying to create a merge publication with a snapshot that includes all
tables, views, procs, and UDFs using SQLDMO. I have no problems creating the
publication with tables only. When I try to add procs using the following
code I get the error

The @schema_option parameter for a procedure or function schema article can
include only the options 0x0000000000000001 or 0x0000000000002000.

I assume I am not setting up the objMrgArt object properly. Any help is
appreciated.


For i = 1 To objRepSps.Count
Set objRepSp = objRepSps(i)
Set objMrgArt = New SQLDMO.MergeArticle2
objMrgArt.Name = objRepSp.Name
objMrgArt.SourceObjectName = objRepSp.Name
objMrgArt.SourceObjectOwner = objRepSp.Owner
objMrgArt.ArticleType = SQLDMORep_ProcSchemaOnly
objMrgPub.MergeArticles.Add objMrgArt
Next i

Since different article types accept different set of schema options (or in
SQLDMO terminology, MergeArticle.CreationScriptOptions) and the (arguably
semi-buggy) SQLDMO behavior of having a set of default CreationScriptOptions
that is not compatible with all article types, you would need to explicitly
specify a compatible set of CreationScriptOptions when creating a non-table
article. Extending the example that you have given below, you can write:

For i = 1 To objRepSps.Count
Set objRepSp = objRepSps(i)
Set objMrgArt = New SQLDMO.MergeArticle2
objMrgArt.Name = objRepSp.Name
objMrgArt.SourceObjectName = objRepSp.Name
objMrgArt.SourceObjectOwner = objRepSp.Owner
objMrgArt.ArticleType = SQLDMORep_ProcSchemaOnly
objMrgArt.CreationScriptOptions =
SQLDMO_CREATIONSCRIPT_TYPE.PrimaryObject Or
SQLDMO_CREATIONSCRIPT_TYPE.ExtendedProperties
objMrgPub.MergeArticles.Add objMrgArt
Next i

To ensure that the base stored procedure and the associated extended
properties are replicated with the initial snapshot.

HTH.

-Raymond
--
This posting is provided "as is" with no warranties and confers no rights.


[quoted text, click to view]