Merge snapshot problem with views and ArticleType property with SQ

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.
"mspradley" <mspradley@.discussions.microsoft.com> wrote in message
news:57624519-68F3-4073-BF90-D2DD80EBCEF3@.microsoft.com...
> 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
>