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
>

Merge Snapshot execution does not create any BCP files for data tables.

Good Day,
After running Merge Snapshot Subscription Job No BCP files are created
for the data tables:
here is my code for the Merge Articles:
Dim lbResult As Boolean
Dim lobjMArt As MergeArticle2
Dim lbHasIdentity As Boolean
Set lobjMArt = New MergeArticle2
lobjMArt.Name = asTableName
lobjMArt.SourceObjectName = asTableName
lobjMArt.SourceObjectOwner = "dbo"
UpdateLog "CreationScriptOptions Default Value is: " &
lobjMArt.CreationScriptOptions
If abHasIdentity Then
lobjMArt.AutoIdentityRange = True
lobjMArt.PublisherIdentityRangeSize = 1000000
lobjMArt.SubscriberIdentityRangeSize = 1000000
lobjMArt.IdentityRangeThreshold = 85
End If
pobjSQLDMOMerge.MergeArticles.Add lobjMArt
Set lobjMArt = Nothing
lbResult = True
The Job History Shows:
bulk copying snapshot data for article [TableName] (0 rows).
This is shown for all tables. 90 % of my tables contain records. For
Example the Patient Table has 675 records.
What am i Missing?
Thanks In Advance.
Brian
The agent just ran and created the bcp files.
The agent is scheduled to run @. 12:00 am. Why did it work through
scheduled execution and not through code.
Am I missing something from this code:
Dim lbResult As Boolean
Dim lsErrSource As String
Dim lsJobID As String
Dim liX As Long
Dim lobjDis As SQLDMO.Distributor2
Dim lobjDisPubs As SQLDMO.DistributionPublishers
Dim lobjDisPub As SQLDMO.DistributionPublication2
Dim lobjJobs As SQLDMO.Jobs
Dim lobjJob As SQLDMO.Job
Dim loJobHis As SQLDMO.JobHistoryFilter
Dim loQR As SQLDMO.QueryResults2
Dim liY As Integer 'Rows
UpdateLog "Refresh Snapshot Function"
'===Load an Default Vars
lsErrSource = "clsPublisher.RefreshSnapShot"
'===Parse the Connection String:
If ParseConnectionString(asConnect) Then
If ConnectToServer() Then
'pobjSQLServer now equals the Sever object.
'We need to get a copy of the Publication DB.
Set lobjDis = pobjSQLServer.Replication.Distributor
Set lobjDisPubs = lobjDis.DistributionPublishers
'Set lobjDisPub =
lobjDis.DistributionPublishers(psPubServer).Distri butionPublications.Item("Promise:Promise")
Set lobjDisPub =
lobjDisPubs(psPubServer).DistributionPublications. Item(1)
UpdateLog ("DisPub Name: " &
lobjDisPubs(psPubServer).DistributionPublications. Item(1).Name)
lsJobID = lobjDisPub.SnapshotJobID
psJobName = lobjDisPub.SnapshotAgent
Set lobjDisPub = Nothing
Set lobjDisPubs = Nothing
Set lobjDis = Nothing
Set lobjJobs = pobjSQLServer.JobServer.Jobs
'Get the Job to Start it.
UpdateLog ("Job Info: ID:" & lsJobID & " Name: " &
psJobName)
Call SaveSetting("Horizon Healthware Inc", "Promise",
"ReplicationJobName", psJobName)
Set lobjJob = lobjJobs.Item(psJobName)
UpdateLog ("Job: Starting Job.")
lobjJob.Invoke
liX = 0
lobjJob.Refresh
Do While lobjJob.CurrentRunStatus <>
SQLDMOJobExecution_Idle
liX = liX + 1
If liX = 200000 Then
lobjJob.Refresh
DoEvents
liX = 0
End If
Loop
' loJobHis.JobName = asJobName
' loJobHis.OldestFirst = False
'
' Set loQR =
pobjSQLServer.JobServer.EnumJobHistory(loJobHis)
'
' liX = 1
'
'
' For liX = 1 To loQR.Columns
' 'UpdateLog ("Job History ColumnName :" &
loQR.ColumnName(liX) & " Value:" & loQR.GetColumnString(1, liX))
'
' Next
Set loQR = Nothing
Set loJobHis = Nothing
Set lobjJob = Nothing
Set lobjJobs = Nothing
Call DisconnectFromServer
lbResult = True
Else
Err.Raise hhwErrorNum.lErrUnableToConnectToServer,
lsErrSource, ptErrorDesc.sErrParsingConnectionString
End If
Else
lbResult = False
Err.Raise hhwErrorNum.lErrParsingConnectionString, lsErrSource,
ptErrorDesc.sErrUnableToConnectToServer
End If
Thanks
Brian
sql

Merge Replication with vertical Filters

Hello
it's possible to create a Merge-Publication with vertical Filter on an
Sql-Server 2000 with SP3?
in our developer System with SP4 i can make a replication, but in the Target
System i didn't.
Error 20009 add the article...
Thanks
Daniel
Yes it is, but ensure that you have defaults in place for the columns you
are not replicating or that they are nullable.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Dani Schwegler" <d.schwegler@.hispeed.ch> wrote in message
news:ezRNYPmUGHA.4956@.TK2MSFTNGP09.phx.gbl...
> Hello
> it's possible to create a Merge-Publication with vertical Filter on an
> Sql-Server 2000 with SP3?
> in our developer System with SP4 i can make a replication, but in the
> Target System i didn't.
> Error 20009 add the article...
> Thanks
> Daniel
>
sql

merge replication with pull subscriber(compress snapshot)

Hi
I am trying to implement merge publication with compress snapshot, copy it
to the subscriber,
create a pull subscriber with the alternate folder(where the snapshot was
copied to)
and then run the agent for synchronization (first time).
i get error that the table in the publication does not exist in the
subscriber database.
It seems that the agent ignores the snapshot and it tries to just merge the
changes between publisher and subscriber.
when i create the table manually at the subscriber with the rowguid column,
it does not fail but it stills does not aplly the snapshot.
if i update/insert new row and run the agent it does merge the changes.
am i missing a crucial step in the process that causes that problem?
Thanks in advance
Daniel
daniel
Please can you post up the error message in its entirety. I suspect that it
is related to a script missing. Also, please try not using the CAB file
method. If you need to compress the snapshot, zip it up and unzip on the
subscriber (I only say this because I've never tested using an alternative
folder with a CAB file).
Paul Ibison
|||Hi and thank u for the replying.
When i start the agent for the first time i get the following error:
"Table 't3' does not exist in the Subscriber database."
At the log i can see that he does try to get the snapshot from alternate
folder:
" Snapshot will be applied from the alternate folder ..."
thanks in advance
Daniel
daniel
"Paul Ibison" wrote:

> Please can you post up the error message in its entirety. I suspect that it
> is related to a script missing. Also, please try not using the CAB file
> method. If you need to compress the snapshot, zip it up and unzip on the
> subscriber (I only say this because I've never tested using an alternative
> folder with a CAB file).
> Paul Ibison
|||Interesting - if you enable full logging is there more info? Also, are you
using the uncompressed snapshot files (if not please try this way also)? In
the snapshot files do you see the table creation script for t3?
(the alternative to this investigation if you like is to do a nosync
initialization).
Paul Ibison
|||Hi Paul
Yes there is a script for table creation.
I tried to use the uncompressed and i got the same error.
How do i do the no sync initialization?
daniel
"Paul Ibison" wrote:

> Interesting - if you enable full logging is there more info? Also, are you
> using the uncompressed snapshot files (if not please try this way also)? In
> the snapshot files do you see the table creation script for t3?
> (the alternative to this investigation if you like is to do a nosync
> initialization).
> Paul Ibison
|||Please have a look here. It largely applies to transactional, but mostly the
same info applies: http://www.replicationanswers.com/NoSyncInitializations.asp
HTH,
Paul Ibison

merge replication with msmq

My company supports sales people with handheld devices
used to track and create orders. They are generally
offline and have a client-side copy of sql server on
their machines. Currently, we are using merge
replication and they log in each night to update our
corporate servers and download pertinent changes.
We will be moving to .NET and I have been looking into
MSMQ and most info seems to be stand-alone (ie not much
info on MSMQ in relation to Replication). My question
is: is it possible to integrate MSMQ with replication
and if so, how? What would the benefits/drawbacks be?
Also, not knowing much about replication, would MSMQ be a
better solution than replication in my situation?
thanks,
Greg
Greg,
you can use MSMQ for queued updating subscribers, as an alternative to a SQL
table (MSreplication_queue). I don't know of any performance/functionality
advantages in using MSMQ, and personally don't use it as it is another
potential point of failure - also MS discourage its use
(http://support.microsoft.com/default.aspx?kbid=830839).
As a general point, queued updating subscribers are recommended to be used
in preference to merge replication when:
Replicated data is mostly read-only at the Subscriber.
Conflicts caused by multiple updates to the same data are infrequent.
You need updates to be propagated on a transaction basis, and conflicts to
be evaluated and resolved on a transaction basis (the entire transaction is
either committed or rolled back).
HTH,
Paul Ibison
|||Queued Updating is good when your number of subscribers is less than 10.
Merge replication was designed from the ground up for mobile subscribers who frequently go off line. MSMQ was designed from the ground up to provide asynchronous messaging. Out of the box it doesn't really do bi-directional replication.
You can use MSMQ to provide a subset of the functionality or merge replication, but it really is the wrong tool for bi-directional asynchronous replication.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
|||Thanks To both of you for your replies.
As MSMQ provides asynchronous messaging, would it be the
appropriate solution in the following situation: the
sales force is online most of the time and must send in
orders. Also, they run our app and must receive updates
which are personalized for their application. For
example, our menus are dynamic and each rep's menu
structure is stored in our db; but on a rep's local db,
only their own menu structure is stored. Therefore, a
change to a particular rep's menu structure would only be
pushed down to that particular rep, not all reps. So
basically, 1 rep might be able to get to a particular
function by selecting it in their menu, but another rep
might not see that functionality at all.
thanks,
Greg

>--Original Message--
>Queued Updating is good when your number of subscribers
is less than 10.
>Merge replication was designed from the ground up for
mobile subscribers who frequently go off line. MSMQ was
designed from the ground up to provide asynchronous
messaging. Out of the box it doesn't really do bi-
directional replication.
>You can use MSMQ to provide a subset of the
functionality or merge replication, but it really is the
wrong tool for bi-directional asynchronous replication.
>Looking for a SQL Server replication book?
>http://www.nwsu.com/0974973602.html
>
>.
>
|||Greg,
I'd recommend merge replication as it performs this type of partitioning
well and is vastly simpler than involving MSMQ.
Basically, each member of the salesforce would have their own SQL Server
(MSDE?) installed against which your client application functions. Each of
these nodes exists as a subscriber to the central publisher. Data would be
partitioned according to salesperson based on the merge agent's -HOSTNAME
parameter.
HTH,
Paul Ibison
|||Paul,
Wouldn't this require reps to manually run the merge
replication process? (BTW, this is our implementation
now). As I understand, MSMQ works automatically, so
messages are sent and received real-time without any
explicit action by the user, as long as there is a
connection to the server and the server has resources to
process the sent message.
thanks,
Greg

>--Original Message--
>Greg,
>I'd recommend merge replication as it performs this type
of partitioning
>well and is vastly simpler than involving MSMQ.
>Basically, each member of the salesforce would have
their own SQL Server
>(MSDE?) installed against which your client application
functions. Each of
>these nodes exists as a subscriber to the central
publisher. Data would be
>partitioned according to salesperson based on the merge
agent's -HOSTNAME
>parameter.
>HTH,
>Paul Ibison
>
>.
>
|||Greg,
The merge agent can be initiated by them manually, by your program, using
windows synchronization manager or if the reps are always connected to the
network, you can have the merge agent running in continuous mode.
HTH,
Paul Ibison
|||Thanks for all the help Paul.
I guess my last question would be ... would MSMQ only be
preferrable if having a local db on a rep's machine was
impossible for some reason or if the messages/changes
were really only 1 way?
thanks!
Greg

>--Original Message--
>Greg,
>The merge agent can be initiated by them manually, by
your program, using
>windows synchronization manager or if the reps are
always connected to the
>network, you can have the merge agent running in
continuous mode.
>HTH,
>Paul Ibison
>
>.
>
|||Greg,
I agree, although MSDE is essentially freely distributable.
If message changes are only one way, merge can be configured this way (using
the -EXCHANGETYPE parameter).
Cheers,
Paul Ibison

Merge Replication structural change

Hi

Created a table User with the fields of Uname varchar(30) and pwd varchar(30) in SQL server 2005.

I need to create a publication and Merge subscription with the below structural changes

User table with the fields of Uname varchar(25) and pwd carchar(30).

The publication table having the Uname varchar(30) but we change this to the subscription table as Uname varchar(25).

Is it possible? If you, please give the details.

Thanks.

Hello,

Is it intended to have different size of Uname? Otherwise, please try to ALTER COLUMN Uname to varchar(25) on the publisher side and this change should be populated to the subscriber side.

Thanks.

This posting is provided AS IS with no warranties, and confers no rights

|||

Hi

Thanks for your reply.

I don't want to change this structural change in publication side. I need this change in subscription side only. so that i keep my Db with original structure and i changed the subscription db with modified structure.

|||as a workaround, you can alter table ..varchar(25), propagate the changes, then set @.replicate_ddl to 0, and alter your table back to varchar(30).|||

Thanks Greg I got some idea from your reply.

If I have only one subscription means this is OK. but I am creating multiple subscription at any time.

Here is my complete requirement.

I have two publications. there is some difference in these two publications.

I am creating subscription from WinMobile device. I configure the publication name there. So that it refer the corresponding publication. These two publications are refer same tables but there is some article changes. I don't want to change the structure in both. I need to change this in one publication and the other one having the same as the DB structure.

The users subscribe the publication at any time. so we are not able to alter the tables each and every time.

Thanks again.

|||I would say you have a design issue then. Replication is used primarily to keep data in sync in multiple locations, if the column sizes must be different between data and source, you can try the workaround I mentioned above or fix your apps so that the schemas are always consistent.|||OK Greg. Thanks.

Merge Replication structural change

Hi

Created a table User with the fields of Uname varchar(30) and pwd varchar(30) in SQL server 2005.

I need to create a publication and Merge subscription with the below structural changes

User table with the fields of Uname varchar(25) and pwd carchar(30).

The publication table having the Uname varchar(30) but we change this to the subscription table as Uname varchar(25).

Is it possible? If you, please give the details.

Thanks.

Hello,

Is it intended to have different size of Uname? Otherwise, please try to ALTER COLUMN Uname to varchar(25) on the publisher side and this change should be populated to the subscriber side.

Thanks.

This posting is provided AS IS with no warranties, and confers no rights

|||

Hi

Thanks for your reply.

I don't want to change this structural change in publication side. I need this change in subscription side only. so that i keep my Db with original structure and i changed the subscription db with modified structure.

|||as a workaround, you can alter table ..varchar(25), propagate the changes, then set @.replicate_ddl to 0, and alter your table back to varchar(30).|||

Thanks Greg I got some idea from your reply.

If I have only one subscription means this is OK. but I am creating multiple subscription at any time.

Here is my complete requirement.

I have two publications. there is some difference in these two publications.

I am creating subscription from WinMobile device. I configure the publication name there. So that it refer the corresponding publication. These two publications are refer same tables but there is some article changes. I don't want to change the structure in both. I need to change this in one publication and the other one having the same as the DB structure.

The users subscribe the publication at any time. so we are not able to alter the tables each and every time.

Thanks again.

|||I would say you have a design issue then. Replication is used primarily to keep data in sync in multiple locations, if the column sizes must be different between data and source, you can try the workaround I mentioned above or fix your apps so that the schemas are always consistent.|||OK Greg. Thanks.

Merge replication set-up problem

Hi All,

I'm trying to set up merge replication between two servers, and can create the snapshot without any issues, however, when I try to get a subscriber to pick up the snapshot, I get the following error

Command attempted:

{call sp_MSsetconflicttable (N'ADDRESS', N'MSmerge_conflict_HK2SH_ADDRESS', N'PYRITE', N'ProlawV11', N'HK2SH')}

Error messages:

Incorrect syntax near 'ADDRESS'. (Source: MSSQLServer, Error number: 102)
Get help: http://help/102

The merge process was unable to deliver the snapshot to the Subscriber. If using Web synchronization, the merge process may have been unable to create or write to the message file. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147201001)
Get help: http://help/MSSQL_REPL-2147201001

I've had a look at various forums, but the only thing I could find was in russian and it wasn't especially clear. None of the MS links help and I've tried searching the MS site. Before I log a call with them, has anyone any ideas how to get round this?

Thanks
Dave

Can you post the schema of the Address table back here.

Also can you confirm that your publisher is Pyrite, your publication database is ProlawV11, and publication name is HK2SH?|||

Schema for Address table

CREATE TABLE [dbo].[ADDRESS](

[ADDRESS] [varchar](36) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[CITY] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[STATE] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[ZIP] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[COUNTRY] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[Contacts] [varchar](36) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[AddressType] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[addrlines] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[IsMailing] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL DEFAULT ('N'),

[rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [MSmerge_df_rowguid_4DDB619441DB4A7889BBCE245C68FF1C] DEFAULT (newsequentialid()),

CONSTRAINT [PK__ADDRESS__7D439ABD] PRIMARY KEY CLUSTERED

(

[ADDRESS] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

And you're correct in your assumptions about the publisher/tables/name/etc

Thanks

Dave

Merge replication set-up problem

Hi All,

I'm trying to set up merge replication between two servers, and can create the snapshot without any issues, however, when I try to get a subscriber to pick up the snapshot, I get the following error

Command attempted:

{call sp_MSsetconflicttable (N'ADDRESS', N'MSmerge_conflict_HK2SH_ADDRESS', N'PYRITE', N'ProlawV11', N'HK2SH')}

Error messages:

Incorrect syntax near 'ADDRESS'. (Source: MSSQLServer, Error number: 102)
Get help: http://help/102

The merge process was unable to deliver the snapshot to the Subscriber. If using Web synchronization, the merge process may have been unable to create or write to the message file. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147201001)
Get help: http://help/MSSQL_REPL-2147201001

I've had a look at various forums, but the only thing I could find was in russian and it wasn't especially clear. None of the MS links help and I've tried searching the MS site. Before I log a call with them, has anyone any ideas how to get round this?

Thanks
Dave

Can you post the schema of the Address table back here.

Also can you confirm that your publisher is Pyrite, your publication database is ProlawV11, and publication name is HK2SH?|||

Schema for Address table

CREATETABLE [dbo].[ADDRESS](

[ADDRESS] [varchar](36)COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL,

[CITY] [varchar](30)COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[STATE] [varchar](30)COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[ZIP] [varchar](30)COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[COUNTRY] [varchar](30)COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[Contacts] [varchar](36)COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[AddressType] [varchar](40)COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[addrlines] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[IsMailing] [varchar](1)COLLATE SQL_Latin1_General_CP1_CI_AS NULLDEFAULT('N'),

[rowguid] [uniqueidentifier] ROWGUIDCOLNOTNULLCONSTRAINT [MSmerge_df_rowguid_4DDB619441DB4A7889BBCE245C68FF1C] DEFAULT(newsequentialid()),

CONSTRAINT [PK__ADDRESS__7D439ABD] PRIMARYKEYCLUSTERED

(

[ADDRESS] ASC

)WITH(IGNORE_DUP_KEY =OFF)ON [PRIMARY]

)ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

And you're correct in your assumptions about the publisher/tables/name/etc

Thanks

Dave

Merge Replication Question

When I create the publication (SQL 2000) I have to execute the following shell command to get the snapshot created:

Set @.cmd = 'c:\Progra~1\Micros~2\80\Com\snapshot -Publisher ' + @.DBServer + ' -PublisherDB ' + @.Database + ' -Publication ' + Cast(@.publication as varchar(100)) + ' -Distributor ' + @.DBServer + ' -DistributorLogin ' + @.DBUserName + ' -DistributorPassword ' + @.DBPassword + ' -ReplicationType 2'

EXEC master..xp_cmdshell @.cmd;

problem is the following:

Creating Snapshot took 14:24

Here's the log of the entire process:

Finding Publication took 0:09

About to Create Publication

Creating Publication took 0:13

About to Add Aritcles

Adding articles took 0:09

About to define filters

Defining filters took 0:11

About to Add Snapshot Agent

Adding snapshot agent took 0:01

About to Execute command to start snapshot agent

(339 row(s) affected)

Creating Snapshot took 14:24

My question is, is there a way to speed this up?

Look at the history tables to see if there's a breakdown to see where the time is being spent. If not there, then add the following parameters to the above: -HistoryVerboseLevel 2 -OutputVerboseLevel 3. This should generate more output as well as log more history in distribution.dbo.MSmerge_history.

At the same time, you didn't mention how big the snapshot is, or how fast/slow your disk subsystem is. That definitely coudl play a part in performrance.

Merge replication problem

I create a merge publication of a database I have on sql server 2005.
The target platform for this database is a pocket pc. Everything
works great, until I dig under the covers a little. Lets say I have a
table a, and that table is filter to only show a subset of its
records. When I look at the database/table in VS2005 I see exactly
what I expect. However, when I deploy it to a pocket pc and then look
using query analyzer on the pocket pc I see all the records that are
in the original table not just those that I filtered out. What is
happening?
It is possible that your filtering criteria is not being resolved correctly
in your pocket pc. What is your filtering criteria?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"BetaD" <dhorth@.horth.com> wrote in message
news:1173818653.274797.32580@.b75g2000hsg.googlegro ups.com...
>I create a merge publication of a database I have on sql server 2005.
> The target platform for this database is a pocket pc. Everything
> works great, until I dig under the covers a little. Lets say I have a
> table a, and that table is filter to only show a subset of its
> records. When I look at the database/table in VS2005 I see exactly
> what I expect. However, when I deploy it to a pocket pc and then look
> using query analyzer on the pocket pc I see all the records that are
> in the original table not just those that I filtered out. What is
> happening?
>

Merge Replication on Windows XP

Hi

Is it possible to create Merge Replication between two server running on Windows XP(SP2).

Why i ask this, as i am trying to do so, but got the error

"The process could not connect to Distributor",

As i already change the login for these two services for "local system" to with Administrative permisstions, but noting seem good for me.. getting the same error.)

when i googling around this, what i find, i need the Domain Account to login to as the services (SQL SERVER & SQL AGENT) one. is it so i need the domain controler for the this ?

How can i create a domain account, without PDC (primary domain controller).

or

so help me out, is there any boby who setup the same?

if so help me.... or ..... any other solution or hint to do so

Regards,

Thanks.

Gurpreet S. Gill

yes replication does work on XP, check your firewall settings on the machines. localsystem account should work, but you may lose some secuerity functionality.|||

Greg Y--

Thanks.

i had checked the Firewall settings, on the both of the machines these are already disabled, as i dont need much security, so i can go for that.

Now the point is, to who should i set the logon for the services, i mean the user, i already set it to the Administrator but doesnt work for me, what could be the cause, as i am continue getting the same error....

The process could not connect to Distributor 'server name'

Regads,

Thanks

Gurpreet S. Gill

|||

Hi all

Finally i am able to solve the problem, now my Merge Replication works very fine.

I would like to thank all who help me.

now i want to share that what i made

1) Create a Distributor & publisher one SERVER 1

2) Create the Subscriber on the SERVER 2.

point to note

i) i am on XP(with SP2) & SQL-Server 2005

ii) Default Snapshort Folder path at SERVER 1 must be in UNC (this is where i was making mistake)

iii) at theSERVER 2, the Subscription Properties page

Security Setting as as follows(number 6 in list)

a) Agent process accont

Account name : SERVER2\Administrator

Password :*********

b) Distributor Connection : SQL Server Authentication(sa)

c) Publisher Connection : SQL Server Authentication(sa)

iv) Both Machines having same user name & password for the SQL Server as well as the Machine login(Administrator)

if somebody still have any problem, then do post in this as a reply

Thanks

Regards

Gurpreet S. Gill

Merge Replication of Stored Procedures and its execution.

Hi,
Am planning Merge Replication for High Availability.
1. Keep an additional server with same configuations.
2. Create a Backup of the database and restore it to the second server.
3. Implement Merge Replicaiton between the two.
4. Replicate only stored procedures and its execution to improve performance.
My stored procedures have dyamic statements , Recursive Procedures , Nested
Proceudures.
Am not planning to replicate the tables but only the User defined functions
and User Stored procedures.
Does some one fore-see any issues with this architecuture. As i may have to
drop and recreate stored procedures during any application release for
enhancements.
I will be applying all my DDL and DML changes on both servers to keep them
in sync at any point of time.
High availability option is to change the DB Connection from 1st server to
2nd Server during Releases and make all the DDL Table changes on server 1
mean while Stop the Merge agent where in all the Stored procedure executions
will be qued from Server2 to Server1 . Once my changes are done enable the
Merge agent which will propogate all the Stored procedure exectutions to
server 1. After which change the DB Connection to Server1 then make all my
DDL Table changes on Sever2 to keep the schema in sync.
Any help would be appriciated.
Regards
Pradeep.
Pradeep,
replicating execution of stored procedures is a facet of transactional
replication rather than merge.
For the other point, to apply DDL changes you could use sp_addscriptexec or
use the Schema Replication option of SQL Server 2005.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||To piggy back on Paul's comments, merge replication is not recommended for
High Availability as it creates a greater latency for each transaction as
tracking triggers must be fired. Secondly the sync operations take longer
than a sync would take with transactional replication.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"PRADEEP M.M." <PRADEEPMM@.discussions.microsoft.com> wrote in message
news:601010A4-98E2-4A70-997A-68E2EEA44306@.microsoft.com...
> Hi,
> Am planning Merge Replication for High Availability.
> 1. Keep an additional server with same configuations.
> 2. Create a Backup of the database and restore it to the second server.
> 3. Implement Merge Replicaiton between the two.
> 4. Replicate only stored procedures and its execution to improve
> performance.
> My stored procedures have dyamic statements , Recursive Procedures ,
> Nested
> Proceudures.
> Am not planning to replicate the tables but only the User defined
> functions
> and User Stored procedures.
> Does some one fore-see any issues with this architecuture. As i may have
> to
> drop and recreate stored procedures during any application release for
> enhancements.
> I will be applying all my DDL and DML changes on both servers to keep them
> in sync at any point of time.
> High availability option is to change the DB Connection from 1st server to
> 2nd Server during Releases and make all the DDL Table changes on server 1
> mean while Stop the Merge agent where in all the Stored procedure
> executions
> will be qued from Server2 to Server1 . Once my changes are done enable the
> Merge agent which will propogate all the Stored procedure exectutions to
> server 1. After which change the DB Connection to Server1 then make all my
> DDL Table changes on Sever2 to keep the schema in sync.
> Any help would be appriciated.
> Regards
> Pradeep.
|||Hi Paul/Hilary,
Thanks a lot for your feedback.
I do understand your concerns of Performance as i had reviewed many reviews
whcih specify Merge Replication as an Overhead for the system.
My environment is SQL SERVER 2000. At present am working on a POC for this.
The basic feasability study has been done for all the functionality except
performance monitoring.
am merge replicating all my tables, i do understand that Merge replication
is not a high availability option but for the current appliction it suits.
Here at any point of time there will not be any inserts or updates or deletes
on a Same table in both nodes. Hence Conflict resolution will not be used and
i believe Conflict resolution consumes most of the resource in merge
replication.
If this could be implemented there are 2 big advantages if a SAN Frame or
the cluster fails for 1 cluster the other would take over this without any
down time. AT APPLICATION end there is a switch which changes the connection
to the second cluster.
I have tested all the Schema changes options which are feasable where in
Dropping a table , RE-Naming a table , Changing the Primary key would
re-generate the whole snapshot and apply it.
In your experience what are the options to make the database highly
available during Database changes , Patching servers and also during any
hardware failures.
Regards
Pradeep.
"Hilary Cotter" wrote:

> To piggy back on Paul's comments, merge replication is not recommended for
> High Availability as it creates a greater latency for each transaction as
> tracking triggers must be fired. Secondly the sync operations take longer
> than a sync would take with transactional replication.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "PRADEEP M.M." <PRADEEPMM@.discussions.microsoft.com> wrote in message
> news:601010A4-98E2-4A70-997A-68E2EEA44306@.microsoft.com...
>
>
|||Pradeep,
I'd urge you to investigate log-shipping and database mirroring and
transactional replication as potential alternatives before coming to a
choice - there's a lot of factors to take into account. Merge replication in
SQL Server 2005 has a nice benefit that it can now be set up as RO, but in
my experience it will always be a much slower means of synchronization than
transactional replication. Log-Shipping on the other hand will be able to
replicate all schema changes, unlike replication. As I say, there are many
things to consider before making a decision.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Merge replication new subscriber problem

Hi all,
i can't create new subscriptions for my publication since i made a
change to a view:
i used to have a view called vlAnag
i changed the view adding a reference (join) to a new view called
vlAnagValidi
then added the new view to the articles in my publication
since that moment when i try to synchronize i get the error
'the schema script ...\vlAnag_999.sch could not be propagated to the
subscriber'
it seems that he tries to create the view vlAnag *before* creating the
vlAnagValidi
in fact i can't find it in the new database
how can i change the order in which the merge process initializes the
schema?
or is there any workaround?
thanx in advance
lorenzo
The workaround on sql 2000 is to recreate the views and then sql server
should create the correct dependencied and therefore establish the required
replication order. Alternatively you could use sp_addscriptexec. In SQL
Server 2005 you can specify the article order and dependencies are
apparently more robust.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||i can't delete the views:
error 3724 cannot drop the view ... because it is being used for
replication
of course
now i'm going to study the sp_addscriptexec...
see you
lorenzo
|||I would recommend removing all the views and any other programming objects
from your merge publication if you have to reinitialize at some point. In my
case they are part of a snapshot publication only, which gives me the
flexibility that I need. The sp_addscriptexec can be used but you have to
hand script the file. In your case this is probably ideal as you can
manually determine the replicaiton order of articles.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||thanx for your reply
i have to manage a 150 articles publication, therefore i'm afraid i
won't edit a manual script...
the (ugly but working) solution i'm taking is to launch the sync, wait
for it to stop for the error, launch a little script that creates the
view, and finally restart the sync
(no comments, please)
my subscribers are very few, so i can handle it :-)
ciao
lorenzo

Merge replication fails

Hi all,
Finally snapshot is generated.
Then i create a pull subscription (i am using merge replication). But after executing 193 actions it gives following error
Line 1: Incorrect syntax near 'No_'

Details are as follow.

1. The subscription has been marked for reinitialization. Reinitialization will occur the next time you synchronize this subscription.

2. Category:SQLSERVER
Source: IM66\WM_MSDE
Number: 170
Message: Line 1: Incorrect syntax near 'No_'.

3. Category:NULL
Source: Merge Replication Provider
Number: -2147201001
Message: The process could not deliver the snapshot to the Subscriber.

4. Line 1: Incorrect syntax near 'No_'. The step failed.

Please Help.
Do i need to create a snapshot again? ( It takes almost 2-3 hours to create a snapshot of 995 articles).
Hi,

Is this SQL 2000 or SQL 2005?
Do you have any tables like %No_%?
Are there any special characters in this name?
Can you narrow down which table is it failing on?|||

It's SQL 2000.
There are lots many table having No_ as primary key.
Above history i have taken from Job History.
From where can i get the exact table name?

When i right click on merge agent and click on Error Details , i see following info.

Category Source Number
Data Source Subscriber Name 170
Agent Merge Replication Provider -2147201001

Please help.

|||995 articles is certainly a large publication. Are they all table articles, or are some of the stored procedures, views, functions, etc.?

I recomment you split this into two publications, one for just schema objects (stored procedures, views, functions, etc.) and one for table articles. This way if you change a stored procedure, it'll be much easier to propogate it to all the subscribers by reinit'ing just that one publication with schema objects, as opposed to a single publication with 995 objects.

and vice versa, if you need to reinit due to a change in a table article, then you don't have to spend valuable time regenerating bcp files, scripts, etc. for all 995 objects, just for that subset.|||

Can you query table MSmerge_history in distribution database and see if there are more details leading up to the failure? WHen applying the snapshot, it might tell you what file it's trying to apply. If you open that file, you may be able to see the TSQL that's failing.

If the history table doesn't tell you anything, can you change the -HistoryVerboseLevel from 1 to 2, as well as pipe the output of the merge agent to a file by using -Output "c:\somefilename.txt". If you open up your merge agent profile, you can add or modify these parameters. THen rerun the merge agent.

Is this SQL Server 2000 Sp4?

|||They are all table articles. I have not included schema objects yet.
Actually i am trying to implement replication for navision. I am using Microsoft Business Solution Navision (With sql option).

|||

I have SQL Server 2000 with Sp4.

On the distributor side there is no Msmerge_history, instead Msmerge_genhistory is there. I opened it but is contains data which gives information regarding snapshot generation.

Then i created a new merge agent profile. with -Historyverboselevel set to 2. where can i find -Output property. There is no such property in agent's profile.

Please help.

|||You have to add the parameter to the existing profile, or you can create your own custom profile. Just add -Output "<some file name>". If you still need help with this, please reference: http://msdn2.microsoft.com/en-us/library/ms221382.

Merge Replication Error: 'Failure to connect to SQL Server with provided connection

Hi!
Im trying to create a merge replication publication for a SQL Mobile
Application .
Everything works fine creating the publication and I'm able to do
the
http://xxx.xxx.xxx.xxx/aaaaaaa/sqlcesa30.dll, and it display's the
"sql server mobile server agent 3.0".

But when I run the application on the PDA and its doing the
replication it appears the following error:
'Failure to connect to SQL Server with provided connection
information . SQL Server does not exist , access is denied because
the
IIS user is not a valid user on the SQL Server , or the password is
incorrect' .

any idea of which could be the reason...?

Thanks in advance!!!We found the solution: to put also the name of the Sql Server Instance
as the publisher: Sql_Server\Sql_Server_Instance

On Mar 5, 9:11 am, "CFTK" <crodriguezdepa...@.gmail.comwrote:

Quote:

Originally Posted by

Hi!
Im trying to create a merge replication publication for a SQL Mobile
Application .
Everything works fine creating the publication and I'm able to do
thehttp://xxx.xxx.xxx.xxx/aaaaaaa/sqlcesa30.dll, and it display's the
"sql server mobile server agent 3.0".
>
But when I run the application on the PDA and its doing the
replication it appears the following error:
'Failure to connect to SQL Server with provided connection
information . SQL Server does not exist , access is denied because
the
IIS user is not a valid user on the SQL Server , or the password is
incorrect' .
>
any idea of which could be the reason...?
>
Thanks in advance!!!

merge replication error

Hi All,
I,m trying to pull a suscription for the first time so I create the database
when I create the suscription but I'm getting this error.
could not be propagated to the subscriber.
(Source: Merge Replication Provider (Agent); Error number: -2147201001)
Unable to replicate a view or function because the referenced objects or
columns are not present on the Subscriber.
(Source: SQLServer (Agent); Error number: 0)
Invalid object name 'dbo.Clients'.
(Source: SQLServer (Data source); Error number: 208)
Invalid object name 'dbo.EmailJobReports'.
(Source: SQLServer (Data source); Error number: 208)
Invalid object name 'dbo.EmailLinks'.
(Source: SQLServer (Data source); Error number: 208)
Invalid object name 'dbo.Employees'.
(Source: SQLServer (Data source); Error number: 208)
Why is not creating this tables?
Tks in advance for you help..
Johnny
Tks Paul,
I will put my views in a separate publication.
Johnny
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:044401c4d7c8$5d17d310$a601280a@.phx.gbl...
> JFB,
> on SQL Server 2005 you can specify the article processing
> order, but not on SQL Server 2000. To avoid this type of
> issue, you could put the views into a separate
> publication or use sp_addscriptexec to send over
> separately (not forgetting the permissions though).
> Rgds,
> Paul Ibison
>
|||Are these object present on the subscriber? Are these objects part of your
publication?
If they are not present on your subcsriber and are not part of your
publication you will have to manually script them out and deploy them to
your subscriber. Possibly by using sp_addscriptexec if all of your
subscriptions were deployed using a UNC.
you could also use a pre snapshot script to deploy these objects.
If they are not present on your subscriber and are part of your publication
they are not being created in the correct order.
Replication does order the article creation scripts correctly, however it
depends on sysdepends on the publisher which can be inacurate. Your best
option again is a pre snapshot command containing a script which will
precreate these objects for you.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"JFB" <jfb@.newSQL.com> wrote in message
news:OyqGUZ81EHA.3820@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> I,m trying to pull a suscription for the first time so I create the
> database when I create the suscription but I'm getting this error.
> could not be propagated to the subscriber.
> (Source: Merge Replication Provider (Agent); Error number: -2147201001)
> ----
> Unable to replicate a view or function because the referenced objects or
> columns are not present on the Subscriber.
> (Source: SQLServer (Agent); Error number: 0)
> ----
> Invalid object name 'dbo.Clients'.
> (Source: SQLServer (Data source); Error number: 208)
> ----
> Invalid object name 'dbo.EmailJobReports'.
> (Source: SQLServer (Data source); Error number: 208)
> ----
> Invalid object name 'dbo.EmailLinks'.
> (Source: SQLServer (Data source); Error number: 208)
> ----
> Invalid object name 'dbo.Employees'.
> (Source: SQLServer (Data source); Error number: 208)
> ----
> Why is not creating this tables?
> Tks in advance for you help..
> Johnny
>
|||Tks for reply Hilary,
These objects are not in the suscriber and are part of the publication.
How can i create it in the correct order?
can you show me how to use a pre snapshot command?
Sorry but I'm new in replication.
Also Paul tell me that I can include the views in a different publication,
because I soon I get out the views the replication works but of course my
database is not complete.
Tks again
Johnny
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OhWVHs81EHA.3504@.TK2MSFTNGP12.phx.gbl...
> Are these object present on the subscriber? Are these objects part of your
> publication?
> If they are not present on your subcsriber and are not part of your
> publication you will have to manually script them out and deploy them to
> your subscriber. Possibly by using sp_addscriptexec if all of your
> subscriptions were deployed using a UNC.
> you could also use a pre snapshot script to deploy these objects.
> If they are not present on your subscriber and are part of your
> publication they are not being created in the correct order.
> Replication does order the article creation scripts correctly, however it
> depends on sysdepends on the publisher which can be inacurate. Your best
> option again is a pre snapshot command containing a script which will
> precreate these objects for you.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> Now available for purchase at:
> http://www.nwsu.com/0974973602.html
> "JFB" <jfb@.newSQL.com> wrote in message
> news:OyqGUZ81EHA.3820@.TK2MSFTNGP11.phx.gbl...
>
|||Note that in my case I don't have inter-view
dependencies, so this is possible. If you have views that
query other views, then the same issue may arise. In that
case you'll need to consider sp_addscriptexec.
Rgds,
Paul Ibison
|||Tks for u reply Paul,
How can I use sp_addscriptexec in my snapshot or publication?
You know I'm new and I dont know how to use this... I create the script for
the views in a sql file..
Regards
Johnny
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:082701c4d865$fbb42440$a601280a@.phx.gbl...
> Note that in my case I don't have inter-view
> dependencies, so this is possible. If you have views that
> query other views, then the same issue may arise. In that
> case you'll need to consider sp_addscriptexec.
> Rgds,
> Paul Ibison
>
|||The details are in BOL
(http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/tsqlref/ts_sp_repl_6qzn.asp). It isn't
really added to the publication, but is a way of using
the replication 'plumbing' to run a script (using osql).
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Merge Replication and ExchangeType param

Kuba,
It might seem possible to create a new profile and add
the new parameter (-ExchangeType in this case) to
MSagent_parameters for the relevant profile_id
(MSagent_profiles). Unfortunately, the parameters picked
up from the profile are hardcoded in
sp_MSvalidate_agent_parameter so not all the ones listed
on the MS site are acceptable. To try to add a particular
parameter, use sp_add_agent_parameter - it'll return an
error if it is not acceptable, in your case you must
resort to the command-line arguments.
HTH,
Paul Ibison
(The ONLY sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Paul!
unfortunately, it doesn't work, as you said, i'm obtaining ExhangeType is
InvalidParameter for MergeAgent, message ...
thanks for your time...
br
kg
|||did you try Exchange or Exchange?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Kuba" <k.gluszkiewicz@.citysoftware.com.pl> wrote in message
news:OKVEmhDlEHA.1644@.tk2msftngp13.phx.gbl...
> Paul!
> unfortunately, it doesn't work, as you said, i'm obtaining ExhangeType is
> InvalidParameter for MergeAgent, message ...
> thanks for your time...
> br
> kg
>
|||Hilary!
i tried this:
sp_add_agent_parameter 6, 'ExchangeType', 1
thanks
kg
|||Does it work now?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Kuba" <k.gluszkiewicz@.citysoftware.com.pl> wrote in message
news:OL%23ti6NlEHA.3520@.TK2MSFTNGP11.phx.gbl...
> Hilary!
> i tried this:
> sp_add_agent_parameter 6, 'ExchangeType', 1
> thanks
> kg
>
|||no, Hilary, as i wrote before, i'm obtaining ExchangeType is
InvalidParameter for MergeAgent message, yes i know i made mistake in
ExchangeType word in previous post, but i tried it with correct
"ExchangeType"
thanks anyway...
kg

Merge Replication and Dynamic and Static Filtering

Hi Guys - I have some basic questions about horizontal filtering and
would appreciate any help.
Dynimic Filtering
When I create a snapshot of a publication that has dynamic filtering,
what will it include? In other words, when the subscriber synchronizes for
the first time, will it apply the snapshot and then filter the data or is
the snapshot already filtered and, if so, by what (since filtering is
different for every subscriber)?
Static Filtering
If a subscriber gets data that is filtered by a particular region for
example, then you enter data in the subscriber for a different region. When
you synchonize, since the region information you entered is not part of the
filter, will it be deleted from the subscriber after it gets propagated to
the publisher or will it remain there?
My last question is if it is possible to do the following:
1) Create a static filter with several criteria (or is only one
allowed?)
2) Then before synchronizing, change the filter clause (through
SQLDMO)
If it seems like that if I change the filter clause then I have to
create another snapshot and reinitialize the subscriber.
Here is our situation, we would like for each subscriber to be able to
choose how they want to filter their data (some may want their data to be
filtered by more than one criteria). Is this flexibility possible?
Thanks,
Maer
Hello Maer,
Per your question, in Dynimic Filtering, when creating the subscription or
reinitilization the subscription, the snapshot will enable the dynamic
filtering. However, syncrhornization process itself will only syncronize
the data and does not have impact on snapshot. Anytime, you change filter,
you have to reinitilizae the replication.

>My last question is if it is possible to do the following:
>1) Create a static filter with several criteria (or is only one allowed?)
Yes, if you have server articles in the publication. There is only one row
filter for a specific article

> 2) Then before synchronizing, change the filter clause (through
SQLDMO)
Again, you have to reinitialize the repliction after a filter is changed.
Thanks & Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>From: "Maer" <maer@.auditleverage.com>
>Subject: Merge Replication and Dynamic and Static Filtering
>Date: Tue, 25 Oct 2005 23:45:31 -0400
>Lines: 38
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
>X-RFC2646: Format=Flowed; Original
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
>Message-ID: <uGguf9d2FHA.3228@.TK2MSFTNGP15.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.replication
>NNTP-Posting-Host: pcp08364781pcs.lndsd201.pa.comcast.net 68.42.19.117
>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGP15.phx.gbl
>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.replication:17361
>X-Tomcat-NG: microsoft.public.sqlserver.replication
> Hi Guys - I have some basic questions about horizontal filtering and
>would appreciate any help.
> Dynimic Filtering
> When I create a snapshot of a publication that has dynamic filtering,
>what will it include? In other words, when the subscriber synchronizes for
>the first time, will it apply the snapshot and then filter the data or is
>the snapshot already filtered and, if so, by what (since filtering is
>different for every subscriber)?
> Static Filtering
> If a subscriber gets data that is filtered by a particular region for
>example, then you enter data in the subscriber for a different region.
When
>you synchonize, since the region information you entered is not part of
the
>filter, will it be deleted from the subscriber after it gets propagated to
>the publisher or will it remain there?
> My last question is if it is possible to do the following:
> 1) Create a static filter with several criteria (or is only one
>allowed?)
> 2) Then before synchronizing, change the filter clause (through
>SQLDMO)
> If it seems like that if I change the filter clause then I have to
>create another snapshot and reinitialize the subscriber.
> Here is our situation, we would like for each subscriber to be able to
>choose how they want to filter their data (some may want their data to be
>filtered by more than one criteria). Is this flexibility possible?
> Thanks,
> Maer
>
>
|||Thank you, Peter. This answers my questions It also shows that the
approach of dynamically changing the filter before synchronization will not
work.
Thank you,
Maer
"Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
news:ev98V7t2FHA.1144@.TK2MSFTNGXA01.phx.gbl...
> Hello Maer,
> Per your question, in Dynimic Filtering, when creating the subscription or
> reinitilization the subscription, the snapshot will enable the dynamic
> filtering. However, syncrhornization process itself will only syncronize
> the data and does not have impact on snapshot. Anytime, you change filter,
> you have to reinitilizae the replication.
>
> Yes, if you have server articles in the publication. There is only one row
> filter for a specific article
> SQLDMO)
> Again, you have to reinitialize the repliction after a filter is changed.
> Thanks & Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> --
> When
> the
>
|||Hello Maer,
You are welcome! :-)
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>From: "Maer" <maer@.auditleverage.com>
>References: <uGguf9d2FHA.3228@.TK2MSFTNGP15.phx.gbl>
<ev98V7t2FHA.1144@.TK2MSFTNGXA01.phx.gbl>
>Subject: Re: Merge Replication and Dynamic and Static Filtering
>Date: Thu, 27 Oct 2005 11:58:29 -0400
>Lines: 106
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
>X-RFC2646: Format=Flowed; Original
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
>Message-ID: <#qciv7w2FHA.2292@.tk2msftngp13.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.replication
>NNTP-Posting-Host: pcp08364781pcs.lndsd201.pa.comcast.net 68.42.19.117
>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msft ngp13.phx.gbl
>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.replication:17417
>X-Tomcat-NG: microsoft.public.sqlserver.replication
> Thank you, Peter. This answers my questions It also shows that the
>approach of dynamically changing the filter before synchronization will
not[vbcol=seagreen]
>work.
> Thank you,
> Maer
>
>"Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
>news:ev98V7t2FHA.1144@.TK2MSFTNGXA01.phx.gbl...
or[vbcol=seagreen]
filter,[vbcol=seagreen]
row[vbcol=seagreen]
for[vbcol=seagreen]
to[vbcol=seagreen]
to
>
>