merge history ip address

Hi again,

Is there a way I can find out the ip address of the subscriber machine
in a replication? I can't seem to find it in any of the tables under
the distribution database.

Thanks,
Haroldsp_helpmergesubscription should give you the server name, but you'll
probably need to use an external script (or perhaps xp_cmdshell) to
look up the IP address. There might be a better way, though, so you
might want to post in microsoft.public.sqlserver.replication

Simon

merge error rowguidcol

I setup merge, but when it was attempting to connect to the subscriber I
received an error (don't know exact text) about not being able to find
column rowguidcol. I ran:
SELECT name FROM syscolumns
And found lots of entries for rowguidcol, which is odd because I don't
name it that (each rowguid column in my table have unique names specific
to the table).
What command can I type to figure out what tables these rowguidcol's are
in?
Thank
Darin
*** Sent via Developersdex http://www.codecomments.com ***
Darin,
are you doing a nosync initialization? If so, it looks like the schema on
the publisher and subscriber are different. A rowguid column (with the
rowguid attribute) will be added to the replicated articles if it doesn't
already exist, and this should happen automatically.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I am doing a nosync. The rowguid isn't being added (shouldn't be) since
all of my tables have rowguid column, but it is named (some examples),
ccst_rowguid, chst_rowguid, etc. This is what is confusing me.
I would say the schema should be the same because he (the customer) says
he is copying the data from one computer to the other, then I am setting
up replication.
Darin
*** Sent via Developersdex http://www.codecomments.com ***
|||OK - then adding the rowguid property to the table on the publisher and the
subscriber to the identical table should make it ok.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
sql

Merge Dynamic filter problem

Hi!

I'm a merge newbie and have a couple of questions. I'm about to setup a merge replication with Sql Server 2005 and Sql Server CE as a subscriber. Situation is like this, we have 10 service technicians using pda.

I want to each pda user have their own data. What I understand I need to use dynamic filter and SUSER_NAME()? Do I need to create a "translation" table to map my system's UserId against SUSER_NAME? How have you solved this problem?

/Magnus

Hello magnus,

One easy approach is to have a column in the table for filtering purpose.

Please take a look on sp_addmergearticle (Transact-SQL) , from http://msdn2.microsoft.com/en-us/library/ms174329.aspx.

[ @.subset_filterclause = ] 'subset_filterclause'
Is a WHERE clause specifying the horizontal filtering of a table article without the word WHERE included. subset_filterclause is of nvarchar(1000), with a default of an empty string.

Important:
For performance reasons, we recommended that you not apply functions to column names in parameterized row filter clauses, such as LEFT([MyColumn]) = SUSER_SNAME(). If you use HOST_NAME in a filter clause and override the HOST_NAME value, you might have to convert data types by using CONVERT. For more information about best practices for this case, see the section "Overriding the HOST_NAME() Value" in Parameterized Row Filters.

Parameterized Row Filters - http://msdn2.microsoft.com/en-us/library/ms152478.aspx.

and

[ @.partition_options = ] partition_options
Defines the way in which data in the article is partitioned, which enables performance optimizations when all rows belong in only one partition or in only one subscription. partition_options is tinyint, and can be one of the following values.

Value Description
0 (default)
The filtering for the article either is static or does not yield a unique subset of data for each partition, that is, an "overlapping" partition.
1
The partitions are overlapping, and data manipulation language (DML) updates made at the Subscriber cannot change the partition to which a row belongs.
2
The filtering for the article yields non-overlapping partitions, but multiple Subscribers can receive the same partition.
3
The filtering for the article yields non-overlapping partitions that are unique for each subscription.

Merge Conflicts and Referential Integrity

I am trying to set up merge replication between a
publisher and 1 subscriber. I have selected the "include
declared referential intetegrity" option. I have
added "Not for Replication" to all of my triggers.
Occasionally, I end up with replication conflicts (either
at the subscriber or the publisher) saying that a row
could not be inserted because it violates a referential
constraint.
I don't understand why this is happening. According to
the online help, the declared referential integrity option
should ensure that articles are processed in order based
on the relationships (ie. inserting parent rows before
related child rows). The replication works most of the
time but I can usually recreate the problem by doing
multiple inserts at both the subscriber and publisher in a
short period of time. I have the replication schedule set
to 1 minute for my testing.
This causes a major issue for me because I can not resolve
the data even though the referential constraint would no
longer apply (ie. the row that it was complaining about is
there now). I am using an identity column and Replication
automatically adds constraints at the publisher and the
subscriber on this field. Therefore, it will not allow me
to resolve the conflict (ie. by adding the row now) since
its id is outside of the identity range.
After the snap shot was applied, I ensured that all of the
referential relationships, indexes, etc were copied over
and are still active. I have the latest service pack (ie.
SP3).
Any help would be greatly apreciated. I have included
part of my replication script to show how the articles are
set up.
exec sp_addmergearticle @.publication = N'BESMgmt',
@.article = N'UserComponentMap', @.source_owner = N'dbo',
@.source_object = N'UserComponentMap', @.type = N'table',
@.description = null, @.column_tracking = N'true',
@.pre_creation_cmd = N'drop', @.creation_script = null,
@.schema_option = 0x000000000000CFF1, @.article_resolver =
null, @.subset_filterclause = null, @.vertical_partition =
N'false', @.destination_owner = N'dbo',
@.auto_identity_range = N'true', @.pub_identity_range = 100,
@.identity_range = 100, @.threshold = 80,
@.verify_resolver_signature = 0,
@.allow_interactive_resolver = N'false',
@.fast_multicol_updateproc = N'true', @.check_permissions = 0
GO
exec sp_addmergearticle @.publication = N'BESMgmt',
@.article = N'UserComponentAffinity', @.source_owner =
N'dbo', @.source_object = N'UserComponentAffinity', @.type =
N'table', @.description = null, @.column_tracking = N'true',
@.pre_creation_cmd = N'drop', @.creation_script = null,
@.schema_option = 0x000000000000CFF1, @.article_resolver =
null, @.subset_filterclause = null, @.vertical_partition =
N'false', @.destination_owner = N'dbo',
@.auto_identity_range = N'true', @.pub_identity_range = 100,
@.identity_range = 100, @.threshold = 80,
@.verify_resolver_signature = 0,
@.allow_interactive_resolver = N'false',
@.fast_multicol_updateproc = N'true', @.check_permissions = 0
GO
This problem is related to how changes are processed, sometimes child
records are processed before their parents are. The way to get around this
is to set the number of generations processed in a batch, preferrably to
2000.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Jim" <jmisasi@.rim.com> wrote in message
news:036a01c4abdc$648948d0$a301280a@.phx.gbl...
> I am trying to set up merge replication between a
> publisher and 1 subscriber. I have selected the "include
> declared referential intetegrity" option. I have
> added "Not for Replication" to all of my triggers.
> Occasionally, I end up with replication conflicts (either
> at the subscriber or the publisher) saying that a row
> could not be inserted because it violates a referential
> constraint.
> I don't understand why this is happening. According to
> the online help, the declared referential integrity option
> should ensure that articles are processed in order based
> on the relationships (ie. inserting parent rows before
> related child rows). The replication works most of the
> time but I can usually recreate the problem by doing
> multiple inserts at both the subscriber and publisher in a
> short period of time. I have the replication schedule set
> to 1 minute for my testing.
> This causes a major issue for me because I can not resolve
> the data even though the referential constraint would no
> longer apply (ie. the row that it was complaining about is
> there now). I am using an identity column and Replication
> automatically adds constraints at the publisher and the
> subscriber on this field. Therefore, it will not allow me
> to resolve the conflict (ie. by adding the row now) since
> its id is outside of the identity range.
> After the snap shot was applied, I ensured that all of the
> referential relationships, indexes, etc were copied over
> and are still active. I have the latest service pack (ie.
> SP3).
> Any help would be greatly apreciated. I have included
> part of my replication script to show how the articles are
> set up.
>
> exec sp_addmergearticle @.publication = N'BESMgmt',
> @.article = N'UserComponentMap', @.source_owner = N'dbo',
> @.source_object = N'UserComponentMap', @.type = N'table',
> @.description = null, @.column_tracking = N'true',
> @.pre_creation_cmd = N'drop', @.creation_script = null,
> @.schema_option = 0x000000000000CFF1, @.article_resolver =
> null, @.subset_filterclause = null, @.vertical_partition =
> N'false', @.destination_owner = N'dbo',
> @.auto_identity_range = N'true', @.pub_identity_range = 100,
> @.identity_range = 100, @.threshold = 80,
> @.verify_resolver_signature = 0,
> @.allow_interactive_resolver = N'false',
> @.fast_multicol_updateproc = N'true', @.check_permissions = 0
> GO
> exec sp_addmergearticle @.publication = N'BESMgmt',
> @.article = N'UserComponentAffinity', @.source_owner =
> N'dbo', @.source_object = N'UserComponentAffinity', @.type =
> N'table', @.description = null, @.column_tracking = N'true',
> @.pre_creation_cmd = N'drop', @.creation_script = null,
> @.schema_option = 0x000000000000CFF1, @.article_resolver =
> null, @.subset_filterclause = null, @.vertical_partition =
> N'false', @.destination_owner = N'dbo',
> @.auto_identity_range = N'true', @.pub_identity_range = 100,
> @.identity_range = 100, @.threshold = 80,
> @.verify_resolver_signature = 0,
> @.allow_interactive_resolver = N'false',
> @.fast_multicol_updateproc = N'true', @.check_permissions = 0
> GO
|||Jim,
BTW this is not an issue in SQL 2005, where PKs are processed before PKs
without any extra work on behalf of the programmer.
Rgds,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I am new to replication and SQL Server for that matter.
So, how do I increase the number of generations processed
in a batch.
I am guessing I need to set this somewhere in my agent
profile. I am using the Default Agent Profile which has
the following parameters and values:
BcpBatchSize 10000
ChangesPerHistory 100
DestThreads 4
DownloadGenerationsPerBatch 100
DownloadReadChangesPerBatch 100
DownloadWriteChangesPerBatch 100
FastRowCount 1
HistoryVerboseLevel 1
KeepAliveMessageInterval 300
Login Timeout 15
MaxDownloadChanges 0
MaxUploadChanges 0
MetaDataRetentionCleanup 1
NumDeadlockRetries 5
PollingInterval 60
QueryTimeout 300
SrcThreads 3
StartQueueTimeout 0
UploadGenerationsPerBatch 100
UploadReadGenerationsPerBatch 100
UploadWriteGenerationsPerBatch 100
Validate 0
ValidateInterval 60
Do I set the Upload and Download Generations to 2000? Do
I set the Read and Write values as well? It looks like I
will have to create my own profile which is fine, but am I
on the right track?
Thank you,
Jim

>--Original Message--
>This problem is related to how changes are processed,
sometimes child
>records are processed before their parents are. The way
to get around this
>is to set the number of generations processed in a batch,
preferrably to[vbcol=seagreen]
>2000.
>
>--
>Hilary Cotter
>Looking for a SQL Server replication book?
>http://www.nwsu.com/0974973602.html
>
>"Jim" <jmisasi@.rim.com> wrote in message
>news:036a01c4abdc$648948d0$a301280a@.phx.gbl...
the "include[vbcol=seagreen]
(either[vbcol=seagreen]
option[vbcol=seagreen]
in a[vbcol=seagreen]
set[vbcol=seagreen]
resolve[vbcol=seagreen]
is[vbcol=seagreen]
Replication[vbcol=seagreen]
me[vbcol=seagreen]
since[vbcol=seagreen]
the[vbcol=seagreen]
(ie.[vbcol=seagreen]
are[vbcol=seagreen]
100,[vbcol=seagreen]
= 0[vbcol=seagreen]
@.type =[vbcol=seagreen]
N'true',[vbcol=seagreen]
100,[vbcol=seagreen]
= 0
>
>.
>
|||Jim,
Increase the -UploadGenerationsPerBatch and -
DownloadGenerationsPerBatch Merge Agent parameters to
their maximum value of 2000, which virtually eliminates
the possibility of processing a child article's
generation in a batch separate from the parent article's
generation.
HTH,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Believe it or not, I am still getting conflicts (although
not as many). Is there anything else I can do?
Jim

>--Original Message--
>Jim,
>Increase the -UploadGenerationsPerBatch and -
>DownloadGenerationsPerBatch Merge Agent parameters to
>their maximum value of 2000, which virtually eliminates
>the possibility of processing a child article's
>generation in a batch separate from the parent article's
>generation.
>HTH,
>Paul Ibison
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>.
>
|||I suppose you could have 2 separate publications - one for the parent and
one for the child, and run them as parent then child yourself. However as
far as I understand this shouldn't be necessary unless you have an enormous
number of records before synchronization. How many records do you have?
Could you synchronize more frequently perhaps to avoid a backlog?
Rgds,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Jim,
the order of processing is not so straightforward (unlike
SQL 5005). Have a look at this article to better
understand the process:
http://support.microsoft.com/default.aspx?scid=kb;
[LN];307356.
You might try setting the FKs to NOT FOR REPLICATION so
this bypasses the constraint checks.
HTH,
Paul Ibison

>.
>

Merge always deletes inserts on subscriber

Hey.
Table1 replicates to table3
Table2, just another table with the same schema as Table1 and 3.
In my setup, I created a copy table, copied 66 rows from table1 on publisher
to table2 on subscriber and deleted all 66 rows. The changes were replicated
to the subscriber to Table2(66 deletes made). Now i insert all 66 rows from
Table2 to table3 on the subscriber side. The change should have gone to
publisher. Instead, the inserts get deleted on the subscriber... Why would
SQL do this?
The inserts done later should be taken as new changes on subscriber and
should be replicated to publisher, isn't it?
Forgot to add, it took it as a conflict between pub and sub and for me, by
default, the pub wins... The problem with this is though, it shouldn't be a
conflict. I've ADDED these rows right now and ONLY to the subscriber...
Any clues.

Merge agent login problem

I have a push merge replication up and working in development SQL Server 2005 (publisher) and SQL Express (subscriber). Because the upgrade from 2000 to 2005 for our production servers has been delayed for who knows how long, I am working to set the same replication up with 2000 and MSDE.

The merge agent is failing in 2000 with the error:

Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection. (Source: MSSQLServer, Error number: 18452)

The 2005 merge agent runs fine. I think it is because the 2005 subscription wizard allowed me to set the 'Run as: SQL Agent Service Account' option, instead of a windows authentication uid. There is no opportunity to set this in the 2000 subscription wizard nor on the merge agen job step itself. I didn't take a screen dump, but am confident that I used the SECURITYMODE=SQL parameter on the MSDE client install.

Any help on whether I'm correct on what the problem is and what to change to get the 2000 merge to log in with the SQL agent service account would be great. Thanks.

Is the SQL agent service account a windows account? If so, then you have to change the subscriber machine to allow windows authentication. SECURITYMODE=SQL means only sql server logins can connect. You should be able to use Enterprise Manager to change the subscriber's server properties to allow both SQL and Windows authentication mode.|||

The install document with MSDE 2000 says that SECURITYMODE=SQL enables mixed mode. The server shows Windows and SQL authentication enabled.

I've looked at the replication jobs with both Enterpise Manager and Management Studio and cannot see how and what the merge is trying to log in to the client with, except for the difference noted in my original post.

I also noticed that the program sqlmangr.exe fails when the MSDE PC is started. It seems to operate ok except for the merge connection, though.

|||The difference appears to be that 2005 can impersonate the service account and 2000 cannot. I added the publication server service account as a user with the same password on the subscription machine and the merge agent was able to log in.

Merge agent error

We have set up a merge publication which has about 34 subscribers. Suddenly
when i try to add a new subscriber, everything works fine until i start the
merge agent on the subscriber.
I get the error : Publication 'xxx' does not exist
Invalid cursor position.
I have included a part of the agent logging (verboselogging 2)
Start logging --
Microsoft SQL Server Merge Agent 8.00.382
Copyright (c) 2000 Microsoft Corporation
Microsoft SQL Server Replication Agent: SQLSRV-CRM-BOOSTmerge-BNM-CRM- 0
Percent Complete: 0
Connecting to Subscriber 'BNM'
Connecting to Subscriber 'BNM.CRM'
Server: BNM
DBMS: Microsoft SQL Server
Version: 08.00.0384
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[4-3-2005 14:03:42]BNM.CRM: {call sp_MSgetversion }
[4-3-2005 14:03:42]BNM.CRM: {?=call sp_helpsubscription_properties
(N'SQLSRV', N'CRM', N'BOOSTmerge')}
Distributor security mode: 0, login name: BNM, password: ********.
[4-3-2005 14:03:42]BNM.CRM: {?=call sp_helpsubscription_properties
(N'SQLSRV', N'CRM', N'BOOSTmerge')}
Publisher security mode: 0, login name: BNM, password: bn159.
Percent Complete: 0
Connecting to Distributor 'SQLSRV'
Connecting to Distributor 'SQLSRV.'
Server: SQLSRV
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: BNM
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[4-3-2005 14:03:42]SQLSRV.: {call sp_MSgetversion }
[4-3-2005 14:03:42]SQLSRV.: {call sp_helpdistpublisher (N'SQLSRV') }
[4-3-2005 14:03:42]SQLSRV.distribution: select datasource, srvid from
master..sysservers where upper(srvname) = upper(N'SQLSRV')
[4-3-2005 14:03:42]SQLSRV.distribution: select datasource, srvid from
master..sysservers where upper(srvname) = upper(N'BNM')
[4-3-2005 14:03:42]SQLSRV.distribution: {call sp_MShelp_merge_agentid (0,
N'CRM', N'BOOSTmerge', 33, N'CRM')}
[4-3-2005 14:03:42]SQLSRV.distribution: {call sp_MShelp_profile (81, 4, N'')}
Percent Complete: 0
Connecting to Publisher 'SQLSRV.CRM'
Initializing
Server: SQLSRV
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: BNM
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[4-3-2005 14:03:42]SQLSRV.CRM: set nocount on declare @.dbname sysname select
@.dbname = db_name() declare @.collation nvarchar(255) select @.collation =
convert(nvarchar(255), databasepropertyex(@.dbname, N'COLLATION')) select
collationproperty(@.collation, N'CODEPAGE') as 'CodePage',
collationproperty(@.collation, N'LCID') as 'LCID',
collationproperty(@.collation, N'COMPARISONSTYLE') as 'ComparisonStyle'
Connecting to Publisher 'SQLSRV.CRM'
Server: SQLSRV
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: BNM
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[4-3-2005 14:03:42]SQLSRV.CRM: {call sp_MSgetversion }
Publication 'BOOSTmerge' does not exist.
Percent Complete: 0
Publication 'BOOSTmerge' does not exist.
Percent Complete: 1
Connecting to Publisher 'SQLSRV'
Percent Complete: 0
Category:NULL
Source: Merge Replication Provider
Number: -2147201022
Message: Publication 'BOOSTmerge' does not exist.
Percent Complete: 0
Category:SQLSERVER
Source: SQLSRV
Number: 0
Message: Invalid cursor state
Disconnecting from Subscriber 'BNM'
Disconnecting from Publisher 'SQLSRV'
Disconnecting from Publisher 'SQLSRV'
Disconnecting from Distributor 'SQLSRV'
On the server SQLSRV can you run sp_helppublication to ensure that
BOOSTmerge exists.
Rgds,
Paul Ibison
"Nol Thoelen" <NolThoelen@.discussions.microsoft.com> wrote in message
news:801D793F-9162-434D-8DA9-620C0C1A48F0@.microsoft.com...
> We have set up a merge publication which has about 34 subscribers.
> Suddenly
> when i try to add a new subscriber, everything works fine until i start
> the
> merge agent on the subscriber.
> I get the error : Publication 'xxx' does not exist
> Invalid cursor position.
> I have included a part of the agent logging (verboselogging 2)
> Start logging --
> Microsoft SQL Server Merge Agent 8.00.382
> Copyright (c) 2000 Microsoft Corporation
> Microsoft SQL Server Replication Agent: SQLSRV-CRM-BOOSTmerge-BNM-CRM- 0
> Percent Complete: 0
> Connecting to Subscriber 'BNM'
> Connecting to Subscriber 'BNM.CRM'
> Server: BNM
> DBMS: Microsoft SQL Server
> Version: 08.00.0384
> user name: dbo
> API conformance: 2
> SQL conformance: 1
> transaction capable: 2
> read only: N
> identifier quote char: "
> non_nullable_columns: 1
> owner usage: 31
> max table name len: 128
> max column name len: 128
> need long data len: Y
> max columns in table: 1024
> max columns in index: 16
> max char literal len: 524288
> max statement len: 524288
> max row size: 524288
> [4-3-2005 14:03:42]BNM.CRM: {call sp_MSgetversion }
> [4-3-2005 14:03:42]BNM.CRM: {?=call sp_helpsubscription_properties
> (N'SQLSRV', N'CRM', N'BOOSTmerge')}
> Distributor security mode: 0, login name: BNM, password: ********.
> [4-3-2005 14:03:42]BNM.CRM: {?=call sp_helpsubscription_properties
> (N'SQLSRV', N'CRM', N'BOOSTmerge')}
> Publisher security mode: 0, login name: BNM, password: bn159.
> Percent Complete: 0
> Connecting to Distributor 'SQLSRV'
> Connecting to Distributor 'SQLSRV.'
> Server: SQLSRV
> DBMS: Microsoft SQL Server
> Version: 08.00.0760
> user name: BNM
> API conformance: 2
> SQL conformance: 1
> transaction capable: 2
> read only: N
> identifier quote char: "
> non_nullable_columns: 1
> owner usage: 31
> max table name len: 128
> max column name len: 128
> need long data len: Y
> max columns in table: 1024
> max columns in index: 16
> max char literal len: 524288
> max statement len: 524288
> max row size: 524288
> [4-3-2005 14:03:42]SQLSRV.: {call sp_MSgetversion }
> [4-3-2005 14:03:42]SQLSRV.: {call sp_helpdistpublisher (N'SQLSRV') }
> [4-3-2005 14:03:42]SQLSRV.distribution: select datasource, srvid from
> master..sysservers where upper(srvname) = upper(N'SQLSRV')
> [4-3-2005 14:03:42]SQLSRV.distribution: select datasource, srvid from
> master..sysservers where upper(srvname) = upper(N'BNM')
> [4-3-2005 14:03:42]SQLSRV.distribution: {call sp_MShelp_merge_agentid (0,
> N'CRM', N'BOOSTmerge', 33, N'CRM')}
> [4-3-2005 14:03:42]SQLSRV.distribution: {call sp_MShelp_profile (81, 4,
> N'')}
> Percent Complete: 0
> Connecting to Publisher 'SQLSRV.CRM'
> Initializing
> Server: SQLSRV
> DBMS: Microsoft SQL Server
> Version: 08.00.0760
> user name: BNM
> API conformance: 2
> SQL conformance: 1
> transaction capable: 2
> read only: N
> identifier quote char: "
> non_nullable_columns: 1
> owner usage: 31
> max table name len: 128
> max column name len: 128
> need long data len: Y
> max columns in table: 1024
> max columns in index: 16
> max char literal len: 524288
> max statement len: 524288
> max row size: 524288
> [4-3-2005 14:03:42]SQLSRV.CRM: set nocount on declare @.dbname sysname
> select
> @.dbname = db_name() declare @.collation nvarchar(255) select @.collation =
> convert(nvarchar(255), databasepropertyex(@.dbname, N'COLLATION')) select
> collationproperty(@.collation, N'CODEPAGE') as 'CodePage',
> collationproperty(@.collation, N'LCID') as 'LCID',
> collationproperty(@.collation, N'COMPARISONSTYLE') as 'ComparisonStyle'
> Connecting to Publisher 'SQLSRV.CRM'
> Server: SQLSRV
> DBMS: Microsoft SQL Server
> Version: 08.00.0760
> user name: BNM
> API conformance: 2
> SQL conformance: 1
> transaction capable: 2
> read only: N
> identifier quote char: "
> non_nullable_columns: 1
> owner usage: 31
> max table name len: 128
> max column name len: 128
> need long data len: Y
> max columns in table: 1024
> max columns in index: 16
> max char literal len: 524288
> max statement len: 524288
> max row size: 524288
> [4-3-2005 14:03:42]SQLSRV.CRM: {call sp_MSgetversion }
> Publication 'BOOSTmerge' does not exist.
> Percent Complete: 0
> Publication 'BOOSTmerge' does not exist.
> Percent Complete: 1
> Connecting to Publisher 'SQLSRV'
> Percent Complete: 0
> Category:NULL
> Source: Merge Replication Provider
> Number: -2147201022
> Message: Publication 'BOOSTmerge' does not exist.
> Percent Complete: 0
> Category:SQLSERVER
> Source: SQLSRV
> Number: 0
> Message: Invalid cursor state
> Disconnecting from Subscriber 'BNM'
> Disconnecting from Publisher 'SQLSRV'
> Disconnecting from Publisher 'SQLSRV'
> Disconnecting from Distributor 'SQLSRV'
>
|||I have tried this procedure and it runs correctly. My opinion is that it has
something todo with data which is not cleaned up correctly.
I had the same subscriber running before on the same publication, but the
machine needed to be re?nstalled after a disc crash.
When i try to subscribe now, i get this error.
Another weird thing is that adding the subscribtion works fine, but in the
list of all the subscribers, the subscription has status deactivated. Even
after running the snapshot agent.
"Paul Ibison" wrote:

> On the server SQLSRV can you run sp_helppublication to ensure that
> BOOSTmerge exists.
> Rgds,
> Paul Ibison
> "No?l Thoelen" <NolThoelen@.discussions.microsoft.com> wrote in message
> news:801D793F-9162-434D-8DA9-620C0C1A48F0@.microsoft.com...
>
>
|||I can't tell what happened when the disk crashed and the
machine was rebuilt. Somehow the subscriber details on
the publisher seem to refer to the old subscription, and
have been deactivated presumably because of not
synchronizing. I'd drop the subscription/subscriber, make
sure that the builds are done to the correct service pack
level, perhaps even reapply sp3a on the subscriber if
there's any doubt, then add the subscriber/subscription
from scratch.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
sql

merge agent displaying never started

Hi friends,
Recently i configured merge replication with pull subscriber the snapshot
agent ran successfully but merge agent failed(never started)raising the
problem . any help would be appriciated.
Message posted via http://www.droptable.com
One posibility is that the job owner is invalid - if the job owner is a
domain login, try changing it to sa and restart the job.
Rgds,
Paul Ibison
|||Thnaks for reply but that didn't work.
when we see merge agent history shows nothing.
Message posted via http://www.droptable.com
|||sql server could not start error:22022
can any one tell the solution please.
Message posted via http://www.droptable.com
|||OK - I've found this article which hopefully explains what is happening:
http://support.microsoft.com/?kbid=870674
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
sql

Merge ActiveX component removes subscriber records

Hi,
I am trying to synchronize data between a publisher running in SQL Server 2000 enterprise edition and a subscriber running in MSDE 2000 using DMO with Merge ActiveX component. My program is in VB.NET.
Now, everytime I try to synchronize data, the changes at subscriber don't get reflected at publisher. Here is the code segment:
objMerge.ReinitializeSubscription(true)
objMerge.Initialize()
objMerge.Run()
objMerge.Terminate()
We are using dynamic filters with SUSER_SNAME(). However, the data for specific users filtered by SUSER_SNAME works fine while getting data from publisher to the subscriber.
Thanks.
You shouldn't be reinitializing. This is one way you can guarantee you will loose your Subscriber records.
Looking for a SQL Server replication book?
If you want to upload the Subscriber changes to the Publisher before the initialization runs use the bUploadBeforeReinit property of the ReInitializeSubscription method to true.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
|||Hi,
I didn't get your answer. It says not to use ReinitializeSubscription and again it says use ReinitializeSubscription with bUploadBeforeReinit parameter set to true.
Please explain.
Thanks.
|||Hmm.. Sorry I seem to have missed that you were using objMerge.ReinitializeSubscription(true) in your first post. I somehow was under the impression you were doing this objMerge.ReinitializeSubscription() or objMerge.ReinitializeSubscription(false)
Basically what I was trying to ask you is why are you doing a reinitialization? You normally only do this if there has been a schema/publication change.
If there is no need to do a reinitialization I would not use this statement.
If you are doing a objMerge.ReinitializeSubscription(true) then transactions that occur on the Subscriber should not be deleted unless there are some conflicts. Does conflict viewer reveal anything?
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
|||Thanks Hilary for the suggestion. You are right. There were actually conflicts as we didn't find the Identity Range tab for articles enabled and hence could not set the proper identity range. We generated the publication script, manually set the identity
range to sp_addmergearticle and then created publication by running this script - now everything worked just fine even without using ReinitializeSubscription(True).
Now the question is why the identity range tab for the artcles were not active in the first place?
sql

Merge - Pull Snapshot Error

I'm struggling with this error with one of my subscribers. Both the
publisher and subscriber have the services running under the same account &
password. My subscribers use a dial-up connection. To help
troubleshoot, I had the subscriber get connected to the publisher via RAS
and had them run a directory listing on the snapshot location. They
received an error: Logon failure: unknown user name or bad password.
The RAS user account has full access to the snapshot location. This is the
first subscriber out of 15 that I'm experiencing this problem with. The
difference between this one and the other 14 is that this subscriber is
running a full version of SQL Server where as the rest are running MSDE.
The process could not deliver the snapshot to the Subscriber.
(Source: Merge Replication Provider (Agent); Error number: -2147201001)
The process could not bulk copy into table '"dbo"."sysmergesubsetfilters"'.
(Source: cookieland (Agent); Error number: 20037)
Unable to open BCP host data-file
(Source: ODBC SQL Server Driver (ODBC); Error number: 0)
This is not a replication problem. It is a problem relating to network connectivity or RAS.
Unfortunately my NT/RAS/dialup skills aren't good enough to help you. I suggest you open a ticket with PSS for Win2k networking, or post this question in one of the networking newsgroups.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Tina Smith" wrote:

> I'm struggling with this error with one of my subscribers. Both the
> publisher and subscriber have the services running under the same account &
> password. My subscribers use a dial-up connection. To help
> troubleshoot, I had the subscriber get connected to the publisher via RAS
> and had them run a directory listing on the snapshot location. They
> received an error: Logon failure: unknown user name or bad password.
> The RAS user account has full access to the snapshot location. This is the
> first subscriber out of 15 that I'm experiencing this problem with. The
> difference between this one and the other 14 is that this subscriber is
> running a full version of SQL Server where as the rest are running MSDE.
> The process could not deliver the snapshot to the Subscriber.
> (Source: Merge Replication Provider (Agent); Error number: -2147201001)
> ----
> The process could not bulk copy into table '"dbo"."sysmergesubsetfilters"'.
> (Source: cookieland (Agent); Error number: 20037)
> ----
> Unable to open BCP host data-file
> (Source: ODBC SQL Server Driver (ODBC); Error number: 0)
>
>
|||Thanks Hilary. I have since learned the credentials are cached in XP and
used when you try to access any
resources in the remote network. The same feature is not present pre XP.
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:8BE474D2-F7A1-414E-9B35-5DD6239B8833@.microsoft.com...
> This is not a replication problem. It is a problem relating to network
connectivity or RAS.
> Unfortunately my NT/RAS/dialup skills aren't good enough to help you. I
suggest you open a ticket with PSS for Win2k networking, or post this
question in one of the networking newsgroups.[vbcol=seagreen]
> --
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
>
> "Tina Smith" wrote:
account &[vbcol=seagreen]
RAS[vbcol=seagreen]
the[vbcol=seagreen]
The
> ----
--[vbcol=seagreen]
'"dbo"."sysmergesubsetfilters"'.
> ----
--[vbcol=seagreen]

Merg replication - data from subscriber is not updated at publisher

Hi,
I try to set Merg replication.
no data that is changed on the subscriber is updated at the publisher.
The updated/new data is removed from the subscriber and he return to
present the publisher data.
I checked scurity issus can find somthing.
Thanks in advance.
Yohai
Yohai,
this sounds like a compensating changes issue. Please have a look at
http://support.microsoft.com/default...&Product=sql2k
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks for the answer.
I didnt mention that we are using MS-SQL 2000 SP4.
does the patch mention on the article is included in this SP ?
Yohai
|||Yes - you have the option to control this process in sp4 (see
http://www.replicationanswers.com/sp4.asp).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)