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
>
Showing posts with label snapshot. Show all posts
Showing posts with label snapshot. Show all posts
Friday, March 30, 2012
Merge snapshot frequency
I have the following scenario,
1.merge replication with 3000 subscribers
2.I have dynamic publication (NOT dynamic snapshot)
3.we observed that synchronization is slow whenever we do a data import at
the publisher
4.because of this we ran the snapshot agent immediately after the data import
5.after the snapshot run, we find the subscriber syncs faster
I would like to know what are the implications of running snapshot agent.
Will it remove the delta changes which are pending for sync? Or is it okay if
I run snapshot as much as I like?
Expect slowness whenever you do a data import as there is always the impact
of the dataload and then there is the added data you have to merge.
Are you saying if you run the snapshot you get faster sync's? This only
makes sense if you re-initialize your subscribers after regenerating the
snapshot.
If you have anonymous subscribers the snapshot is always generated each time
you run it. If you have named it is only regenerated if subscribers expire
or require reinitialization.
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
"Ravi Lobo" <RaviLobo@.discussions.microsoft.com> wrote in message
news:45E03380-6DE7-4AB1-B20D-A97C18B04717@.microsoft.com...
>I have the following scenario,
> 1.merge replication with 3000 subscribers
> 2.I have dynamic publication (NOT dynamic snapshot)
> 3.we observed that synchronization is slow whenever we do a data import at
> the publisher
> 4.because of this we ran the snapshot agent immediately after the data
> import
> 5.after the snapshot run, we find the subscriber syncs faster
>
> I would like to know what are the implications of running snapshot agent.
> Will it remove the delta changes which are pending for sync? Or is it okay
> if
> I run snapshot as much as I like?
>
>
|||Thank you Hilary for you time. I have some more clarifications,
1.I have sql server ce subscribers
2.Hence I need to use anonymous subscription
I have the following questions here,
a)Can I use pre-generated snapshot in my case? (Anonymous + sql ce
subscribers)
b)I also have dynamic filters on the publisher. What impact I will have by
re-running the snapshot second time, on the subscriber?
"Hilary Cotter" wrote:
> Expect slowness whenever you do a data import as there is always the impact
> of the dataload and then there is the added data you have to merge.
> Are you saying if you run the snapshot you get faster sync's? This only
> makes sense if you re-initialize your subscribers after regenerating the
> snapshot.
> If you have anonymous subscribers the snapshot is always generated each time
> you run it. If you have named it is only regenerated if subscribers expire
> or require reinitialization.
> --
> 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
>
> "Ravi Lobo" <RaviLobo@.discussions.microsoft.com> wrote in message
> news:45E03380-6DE7-4AB1-B20D-A97C18B04717@.microsoft.com...
>
>
1.merge replication with 3000 subscribers
2.I have dynamic publication (NOT dynamic snapshot)
3.we observed that synchronization is slow whenever we do a data import at
the publisher
4.because of this we ran the snapshot agent immediately after the data import
5.after the snapshot run, we find the subscriber syncs faster
I would like to know what are the implications of running snapshot agent.
Will it remove the delta changes which are pending for sync? Or is it okay if
I run snapshot as much as I like?
Expect slowness whenever you do a data import as there is always the impact
of the dataload and then there is the added data you have to merge.
Are you saying if you run the snapshot you get faster sync's? This only
makes sense if you re-initialize your subscribers after regenerating the
snapshot.
If you have anonymous subscribers the snapshot is always generated each time
you run it. If you have named it is only regenerated if subscribers expire
or require reinitialization.
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
"Ravi Lobo" <RaviLobo@.discussions.microsoft.com> wrote in message
news:45E03380-6DE7-4AB1-B20D-A97C18B04717@.microsoft.com...
>I have the following scenario,
> 1.merge replication with 3000 subscribers
> 2.I have dynamic publication (NOT dynamic snapshot)
> 3.we observed that synchronization is slow whenever we do a data import at
> the publisher
> 4.because of this we ran the snapshot agent immediately after the data
> import
> 5.after the snapshot run, we find the subscriber syncs faster
>
> I would like to know what are the implications of running snapshot agent.
> Will it remove the delta changes which are pending for sync? Or is it okay
> if
> I run snapshot as much as I like?
>
>
|||Thank you Hilary for you time. I have some more clarifications,
1.I have sql server ce subscribers
2.Hence I need to use anonymous subscription
I have the following questions here,
a)Can I use pre-generated snapshot in my case? (Anonymous + sql ce
subscribers)
b)I also have dynamic filters on the publisher. What impact I will have by
re-running the snapshot second time, on the subscriber?
"Hilary Cotter" wrote:
> Expect slowness whenever you do a data import as there is always the impact
> of the dataload and then there is the added data you have to merge.
> Are you saying if you run the snapshot you get faster sync's? This only
> makes sense if you re-initialize your subscribers after regenerating the
> snapshot.
> If you have anonymous subscribers the snapshot is always generated each time
> you run it. If you have named it is only regenerated if subscribers expire
> or require reinitialization.
> --
> 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
>
> "Ravi Lobo" <RaviLobo@.discussions.microsoft.com> wrote in message
> news:45E03380-6DE7-4AB1-B20D-A97C18B04717@.microsoft.com...
>
>
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
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
Wednesday, March 28, 2012
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
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 filters
I have a merge replication the last weekend, these fail and send me the
message error "you would reinitialice the snapshot because the snapshot was
obsolete", but whe I try rerun the snapshot, it send me another error like
"i couldn't find the jobid @. x x x x x x x x x ".
whatever I made a new subscription and restore my 26 subscribers, and now my
central store fail because the range of the identity is full and don't want
renew the range I used sp_adjustpublisheridentityrange but nothing happends
some body heard somthing like that , I'm gonna crazy now and doesn't work
I check the MSrepl_identity_range and it is updated but not my subcribers
Regards, David
Sounds like your subscriptions have expired and have been dropped. When you
set your retention period (right click on your publication, select
publication properties, and note the value in teh Subscription expiration
section. This should be something small, but large enough that it will be
longer than a long weekend. So try 5 days.
Then make sure your history is also retained for this period. Right click on
replication monitor, select distributor properties, click on properties, and
then retain the history for 5 days also.
Now your challenge is to make sure your agents run and connect with the
subscriber at least once every 5 days. Put your replication agents in
infinite loops so that they will retry on failure.
That's the good news. The bad news is that you are going to have to redeploy
the snapshot to all of your subscribers again.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"David" <davbarquero@.hotmail.com> wrote in message
news:uLTDI$xwEHA.392@.TK2MSFTNGP12.phx.gbl...
> I have a merge replication the last weekend, these fail and send me
the
> message error "you would reinitialice the snapshot because the snapshot
was
> obsolete", but whe I try rerun the snapshot, it send me another error
like
> "i couldn't find the jobid @. x x x x x x x x x ".
> whatever I made a new subscription and restore my 26 subscribers, and now
my
> central store fail because the range of the identity is full and don't
want
> renew the range I used sp_adjustpublisheridentityrange but nothing
happends
> some body heard somthing like that , I'm gonna crazy now and doesn't work
> I check the MSrepl_identity_range and it is updated but not my subcribers
>
> Regards, David
>
message error "you would reinitialice the snapshot because the snapshot was
obsolete", but whe I try rerun the snapshot, it send me another error like
"i couldn't find the jobid @. x x x x x x x x x ".
whatever I made a new subscription and restore my 26 subscribers, and now my
central store fail because the range of the identity is full and don't want
renew the range I used sp_adjustpublisheridentityrange but nothing happends
some body heard somthing like that , I'm gonna crazy now and doesn't work
I check the MSrepl_identity_range and it is updated but not my subcribers
Regards, David
Sounds like your subscriptions have expired and have been dropped. When you
set your retention period (right click on your publication, select
publication properties, and note the value in teh Subscription expiration
section. This should be something small, but large enough that it will be
longer than a long weekend. So try 5 days.
Then make sure your history is also retained for this period. Right click on
replication monitor, select distributor properties, click on properties, and
then retain the history for 5 days also.
Now your challenge is to make sure your agents run and connect with the
subscriber at least once every 5 days. Put your replication agents in
infinite loops so that they will retry on failure.
That's the good news. The bad news is that you are going to have to redeploy
the snapshot to all of your subscribers again.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"David" <davbarquero@.hotmail.com> wrote in message
news:uLTDI$xwEHA.392@.TK2MSFTNGP12.phx.gbl...
> I have a merge replication the last weekend, these fail and send me
the
> message error "you would reinitialice the snapshot because the snapshot
was
> obsolete", but whe I try rerun the snapshot, it send me another error
like
> "i couldn't find the jobid @. x x x x x x x x x ".
> whatever I made a new subscription and restore my 26 subscribers, and now
my
> central store fail because the range of the identity is full and don't
want
> renew the range I used sp_adjustpublisheridentityrange but nothing
happends
> some body heard somthing like that , I'm gonna crazy now and doesn't work
> I check the MSrepl_identity_range and it is updated but not my subcribers
>
> Regards, David
>
Friday, March 23, 2012
Merge Replication Snapshot Generation - Need Best Practice
SQL Server 2005 & Express
All,
What is a best practice for generating snapshots in my merge
replication topology?
In my topology subscriptions will be coming and going over time.
There will be @.150 users with new employees coming online over time.
Some of the users share hardware and so they may have a subscription
on one machine for several months and then stop using the system for a
few months, and then start a new subscription on a different machine.
In such a scenario would it be better for me to expire subscriptions
on a regular basis?
I would evaluate using anonymous subscribers with dynamic filtering based on
user_name.
I think you will find a smaller retention period will mean shorted sync
times especially if your users are off line for appreciable amounts of time.
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
"Crash" <sourcenexus@.san.rr.com> wrote in message
news:1174326055.221629.90730@.n76g2000hsh.googlegro ups.com...
> SQL Server 2005 & Express
> All,
> What is a best practice for generating snapshots in my merge
> replication topology?
> In my topology subscriptions will be coming and going over time.
> There will be @.150 users with new employees coming online over time.
> Some of the users share hardware and so they may have a subscription
> on one machine for several months and then stop using the system for a
> few months, and then start a new subscription on a different machine.
> In such a scenario would it be better for me to expire subscriptions
> on a regular basis?
>
sql
All,
What is a best practice for generating snapshots in my merge
replication topology?
In my topology subscriptions will be coming and going over time.
There will be @.150 users with new employees coming online over time.
Some of the users share hardware and so they may have a subscription
on one machine for several months and then stop using the system for a
few months, and then start a new subscription on a different machine.
In such a scenario would it be better for me to expire subscriptions
on a regular basis?
I would evaluate using anonymous subscribers with dynamic filtering based on
user_name.
I think you will find a smaller retention period will mean shorted sync
times especially if your users are off line for appreciable amounts of time.
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
"Crash" <sourcenexus@.san.rr.com> wrote in message
news:1174326055.221629.90730@.n76g2000hsh.googlegro ups.com...
> SQL Server 2005 & Express
> All,
> What is a best practice for generating snapshots in my merge
> replication topology?
> In my topology subscriptions will be coming and going over time.
> There will be @.150 users with new employees coming online over time.
> Some of the users share hardware and so they may have a subscription
> on one machine for several months and then stop using the system for a
> few months, and then start a new subscription on a different machine.
> In such a scenario would it be better for me to expire subscriptions
> on a regular basis?
>
sql
merge replication snapshot agent fails
I'm trying to setup merge replication and get the following error from
the snapshot agent after I've finished the 'New Publication Wizard'.
This doesn't make sense to me as it is an automatic process. Also, it
looks like it is complaining about a 'dbo.MSmerge_cont...' view which
is a view created automatically for the merge publication.
ERROR MESSAGE:
The identifier that starts with
'<EVENT_INSTANCE><EventType>CREATE_VIEW</EventType><PostTime>2006-11-09T09:56:48.390</PostTime><SPID>152</SPID><ServerName>XF4SWC'
is too long. Maximum length is 128.
FULL DETAILS:
Message: The identifier that starts with
'<EVENT_INSTANCE><EventType>CREATE_VIEW</EventType><PostTime>2006-11-09T09:56:48.390</PostTime><SPID>152</SPID><ServerName>XF4SWC'
is too long. Maximum length is 128.
Command Text: IF
OBJECT_ID(N'[<EVENT_INSTANCE><EventType>CREATE_VIE W</EventType><PostTime>2006-11-09T09:56:48.390</PostTime><SPID>152</SPID><ServerName>XF4SWCS464</ServerName><LoginName>NT
AUTHORITY\SYSTEM</LoginName><UserName>dbo</UserName><DatabaseName>liport2_gts</DatabaseName><SchemaName>dbo</SchemaName><ObjectName>MSmerge_contAE2EB588ABFA4C1 3825D4D44CB2D50CB_90</ObjectName><ObjectType>VIEW</ObjectType><TSQLCommand><SetOptions
ANSI_NULLS="OFF" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON"
QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" /><CommandText>create view
dbo.MSmerge_contAE2EB588ABFA4C13825D4D44CB2D50CB_9 0 as select * from
MSmerge_contents where 1 =
2</CommandText></TSQLCommand></EVENT_INSTANCE>]', 'V') IS NOT NULL DROP
VIEW
[<EVENT_INSTANCE><EventType>CREATE_VIEW</EventType><PostTime>2006-11-09T09:56:48.390</PostTime><SPID>152</SPID><ServerName>XF4SWCS464</ServerName><LoginName>NT
AUTHORITY\SYSTEM</LoginName><UserName>dbo</UserName><DatabaseName>liport2_gts</DatabaseName><SchemaName>dbo</SchemaName><ObjectName>MSmerge_contAE2EB588ABFA4C1 3825D4D44CB2D50CB_90</ObjectName><ObjectType>VIEW</ObjectType><TSQLCommand><SetOptions
ANSI_NULLS="OFF" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON"
QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" /><CommandText>create view
dbo.MSmerge_contAE2EB588ABFA4C13825D4D44CB2D50CB_9 0 as select * from
MSmerge_contents where 1 =
2</CommandText></TSQLCommand></EVENT_INSTANCE>]
Parameters:
Stack: at
Microsoft.SqlServer.Replication.AgentCore.ReMapSql Exception(SqlException
e, SqlCommand command)
at
Microsoft.SqlServer.Replication.AgentCore.AgentExe cuteNonQuery(SqlCommand
command, Int32 queryTimeout)
at
Microsoft.SqlServer.Replication.AgentCore.ExecuteD iscardResults(CommandSetupDelegate
commandSetupDelegate, Int32 queryTimeout)
at
Microsoft.SqlServer.Replication.AgentCore.ExecuteA dHocQueryDiscardResults(String
strQuery, SqlConnection connection)
at
Microsoft.SqlServer.Replication.Snapshot.MergeSnap shotProvider.GenerateSystemTableBcpFiles()
at
Microsoft.SqlServer.Replication.Snapshot.MergeSnap shotProvider.DoPostArticleFilesGenerationProcessin g()
at
Microsoft.SqlServer.Replication.Snapshot.SqlServer SnapshotProvider.GenerateSnapshot()
at
Microsoft.SqlServer.Replication.SnapshotGeneration Agent.InternalRun()
at Microsoft.SqlServer.Replication.AgentCore.Run() (Source:
MSSQLServer, Error number: 103)
Get help: http://help/103
Server XF4SWCS464, Level 15, State 4, Procedure , Line 1
The identifier that starts with
'<EVENT_INSTANCE><EventType>CREATE_VIEW</EventType><PostTime>2006-11-09T09:56:48.390</PostTime><SPID>152</SPID><ServerName>XF4SWC'
is too long. Maximum length is 128. (Source: MSSQLServer, Error number:
103)
Get help: http://help/103
can you enable logging and post the log back here?
http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B312292&sd=tech
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
"gabe" <gschnaub@.gmail.com> wrote in message
news:1163095482.849374.43790@.m73g2000cwd.googlegro ups.com...
> I'm trying to setup merge replication and get the following error from
> the snapshot agent after I've finished the 'New Publication Wizard'.
> This doesn't make sense to me as it is an automatic process. Also, it
> looks like it is complaining about a 'dbo.MSmerge_cont...' view which
> is a view created automatically for the merge publication.
> ERROR MESSAGE:
> The identifier that starts with
> '<EVENT_INSTANCE><EventType>CREATE_VIEW</EventType><PostTime>2006-11-09T09:56:48.390</PostTime><SPID>152</SPID><ServerName>XF4SWC'
> is too long. Maximum length is 128.
> FULL DETAILS:
> Message: The identifier that starts with
> '<EVENT_INSTANCE><EventType>CREATE_VIEW</EventType><PostTime>2006-11-09T09:56:48.390</PostTime><SPID>152</SPID><ServerName>XF4SWC'
> is too long. Maximum length is 128.
> Command Text: IF
> OBJECT_ID(N'[<EVENT_INSTANCE><EventType>CREATE_VIE W</EventType><PostTime>2006-11-09T09:56:48.390</PostTime><SPID>152</SPID><ServerName>XF4SWCS464</ServerName><LoginName>NT
> AUTHORITY\SYSTEM</LoginName><UserName>dbo</UserName><DatabaseName>liport2_gts</DatabaseName><SchemaName>dbo</SchemaName><ObjectName>MSmerge_contAE2EB588ABFA4C1 3825D4D44CB2D50CB_90</ObjectName><ObjectType>VIEW</ObjectType><TSQLCommand><SetOptions
> ANSI_NULLS="OFF" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON"
> QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" /><CommandText>create view
> dbo.MSmerge_contAE2EB588ABFA4C13825D4D44CB2D50CB_9 0 as select * from
> MSmerge_contents where 1 =
> 2</CommandText></TSQLCommand></EVENT_INSTANCE>]', 'V') IS NOT NULL DROP
> VIEW
> [<EVENT_INSTANCE><EventType>CREATE_VIEW</EventType><PostTime>2006-11-09T09:56:48.390</PostTime><SPID>152</SPID><ServerName>XF4SWCS464</ServerName><LoginName>NT
> AUTHORITY\SYSTEM</LoginName><UserName>dbo</UserName><DatabaseName>liport2_gts</DatabaseName><SchemaName>dbo</SchemaName><ObjectName>MSmerge_contAE2EB588ABFA4C1 3825D4D44CB2D50CB_90</ObjectName><ObjectType>VIEW</ObjectType><TSQLCommand><SetOptions
> ANSI_NULLS="OFF" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON"
> QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" /><CommandText>create view
> dbo.MSmerge_contAE2EB588ABFA4C13825D4D44CB2D50CB_9 0 as select * from
> MSmerge_contents where 1 =
> 2</CommandText></TSQLCommand></EVENT_INSTANCE>]
> Parameters:
> Stack: at
> Microsoft.SqlServer.Replication.AgentCore.ReMapSql Exception(SqlException
> e, SqlCommand command)
> at
> Microsoft.SqlServer.Replication.AgentCore.AgentExe cuteNonQuery(SqlCommand
> command, Int32 queryTimeout)
> at
> Microsoft.SqlServer.Replication.AgentCore.ExecuteD iscardResults(CommandSetupDelegate
> commandSetupDelegate, Int32 queryTimeout)
> at
> Microsoft.SqlServer.Replication.AgentCore.ExecuteA dHocQueryDiscardResults(String
> strQuery, SqlConnection connection)
> at
> Microsoft.SqlServer.Replication.Snapshot.MergeSnap shotProvider.GenerateSystemTableBcpFiles()
> at
> Microsoft.SqlServer.Replication.Snapshot.MergeSnap shotProvider.DoPostArticleFilesGenerationProcessin g()
> at
> Microsoft.SqlServer.Replication.Snapshot.SqlServer SnapshotProvider.GenerateSnapshot()
> at
> Microsoft.SqlServer.Replication.SnapshotGeneration Agent.InternalRun()
> at Microsoft.SqlServer.Replication.AgentCore.Run() (Source:
> MSSQLServer, Error number: 103)
> Get help: http://help/103
> Server XF4SWCS464, Level 15, State 4, Procedure , Line 1
> The identifier that starts with
> '<EVENT_INSTANCE><EventType>CREATE_VIEW</EventType><PostTime>2006-11-09T09:56:48.390</PostTime><SPID>152</SPID><ServerName>XF4SWC'
> is too long. Maximum length is 128. (Source: MSSQLServer, Error number:
> 103)
> Get help: http://help/103
>
the snapshot agent after I've finished the 'New Publication Wizard'.
This doesn't make sense to me as it is an automatic process. Also, it
looks like it is complaining about a 'dbo.MSmerge_cont...' view which
is a view created automatically for the merge publication.
ERROR MESSAGE:
The identifier that starts with
'<EVENT_INSTANCE><EventType>CREATE_VIEW</EventType><PostTime>2006-11-09T09:56:48.390</PostTime><SPID>152</SPID><ServerName>XF4SWC'
is too long. Maximum length is 128.
FULL DETAILS:
Message: The identifier that starts with
'<EVENT_INSTANCE><EventType>CREATE_VIEW</EventType><PostTime>2006-11-09T09:56:48.390</PostTime><SPID>152</SPID><ServerName>XF4SWC'
is too long. Maximum length is 128.
Command Text: IF
OBJECT_ID(N'[<EVENT_INSTANCE><EventType>CREATE_VIE W</EventType><PostTime>2006-11-09T09:56:48.390</PostTime><SPID>152</SPID><ServerName>XF4SWCS464</ServerName><LoginName>NT
AUTHORITY\SYSTEM</LoginName><UserName>dbo</UserName><DatabaseName>liport2_gts</DatabaseName><SchemaName>dbo</SchemaName><ObjectName>MSmerge_contAE2EB588ABFA4C1 3825D4D44CB2D50CB_90</ObjectName><ObjectType>VIEW</ObjectType><TSQLCommand><SetOptions
ANSI_NULLS="OFF" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON"
QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" /><CommandText>create view
dbo.MSmerge_contAE2EB588ABFA4C13825D4D44CB2D50CB_9 0 as select * from
MSmerge_contents where 1 =
2</CommandText></TSQLCommand></EVENT_INSTANCE>]', 'V') IS NOT NULL DROP
VIEW
[<EVENT_INSTANCE><EventType>CREATE_VIEW</EventType><PostTime>2006-11-09T09:56:48.390</PostTime><SPID>152</SPID><ServerName>XF4SWCS464</ServerName><LoginName>NT
AUTHORITY\SYSTEM</LoginName><UserName>dbo</UserName><DatabaseName>liport2_gts</DatabaseName><SchemaName>dbo</SchemaName><ObjectName>MSmerge_contAE2EB588ABFA4C1 3825D4D44CB2D50CB_90</ObjectName><ObjectType>VIEW</ObjectType><TSQLCommand><SetOptions
ANSI_NULLS="OFF" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON"
QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" /><CommandText>create view
dbo.MSmerge_contAE2EB588ABFA4C13825D4D44CB2D50CB_9 0 as select * from
MSmerge_contents where 1 =
2</CommandText></TSQLCommand></EVENT_INSTANCE>]
Parameters:
Stack: at
Microsoft.SqlServer.Replication.AgentCore.ReMapSql Exception(SqlException
e, SqlCommand command)
at
Microsoft.SqlServer.Replication.AgentCore.AgentExe cuteNonQuery(SqlCommand
command, Int32 queryTimeout)
at
Microsoft.SqlServer.Replication.AgentCore.ExecuteD iscardResults(CommandSetupDelegate
commandSetupDelegate, Int32 queryTimeout)
at
Microsoft.SqlServer.Replication.AgentCore.ExecuteA dHocQueryDiscardResults(String
strQuery, SqlConnection connection)
at
Microsoft.SqlServer.Replication.Snapshot.MergeSnap shotProvider.GenerateSystemTableBcpFiles()
at
Microsoft.SqlServer.Replication.Snapshot.MergeSnap shotProvider.DoPostArticleFilesGenerationProcessin g()
at
Microsoft.SqlServer.Replication.Snapshot.SqlServer SnapshotProvider.GenerateSnapshot()
at
Microsoft.SqlServer.Replication.SnapshotGeneration Agent.InternalRun()
at Microsoft.SqlServer.Replication.AgentCore.Run() (Source:
MSSQLServer, Error number: 103)
Get help: http://help/103
Server XF4SWCS464, Level 15, State 4, Procedure , Line 1
The identifier that starts with
'<EVENT_INSTANCE><EventType>CREATE_VIEW</EventType><PostTime>2006-11-09T09:56:48.390</PostTime><SPID>152</SPID><ServerName>XF4SWC'
is too long. Maximum length is 128. (Source: MSSQLServer, Error number:
103)
Get help: http://help/103
can you enable logging and post the log back here?
http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B312292&sd=tech
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
"gabe" <gschnaub@.gmail.com> wrote in message
news:1163095482.849374.43790@.m73g2000cwd.googlegro ups.com...
> I'm trying to setup merge replication and get the following error from
> the snapshot agent after I've finished the 'New Publication Wizard'.
> This doesn't make sense to me as it is an automatic process. Also, it
> looks like it is complaining about a 'dbo.MSmerge_cont...' view which
> is a view created automatically for the merge publication.
> ERROR MESSAGE:
> The identifier that starts with
> '<EVENT_INSTANCE><EventType>CREATE_VIEW</EventType><PostTime>2006-11-09T09:56:48.390</PostTime><SPID>152</SPID><ServerName>XF4SWC'
> is too long. Maximum length is 128.
> FULL DETAILS:
> Message: The identifier that starts with
> '<EVENT_INSTANCE><EventType>CREATE_VIEW</EventType><PostTime>2006-11-09T09:56:48.390</PostTime><SPID>152</SPID><ServerName>XF4SWC'
> is too long. Maximum length is 128.
> Command Text: IF
> OBJECT_ID(N'[<EVENT_INSTANCE><EventType>CREATE_VIE W</EventType><PostTime>2006-11-09T09:56:48.390</PostTime><SPID>152</SPID><ServerName>XF4SWCS464</ServerName><LoginName>NT
> AUTHORITY\SYSTEM</LoginName><UserName>dbo</UserName><DatabaseName>liport2_gts</DatabaseName><SchemaName>dbo</SchemaName><ObjectName>MSmerge_contAE2EB588ABFA4C1 3825D4D44CB2D50CB_90</ObjectName><ObjectType>VIEW</ObjectType><TSQLCommand><SetOptions
> ANSI_NULLS="OFF" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON"
> QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" /><CommandText>create view
> dbo.MSmerge_contAE2EB588ABFA4C13825D4D44CB2D50CB_9 0 as select * from
> MSmerge_contents where 1 =
> 2</CommandText></TSQLCommand></EVENT_INSTANCE>]', 'V') IS NOT NULL DROP
> VIEW
> [<EVENT_INSTANCE><EventType>CREATE_VIEW</EventType><PostTime>2006-11-09T09:56:48.390</PostTime><SPID>152</SPID><ServerName>XF4SWCS464</ServerName><LoginName>NT
> AUTHORITY\SYSTEM</LoginName><UserName>dbo</UserName><DatabaseName>liport2_gts</DatabaseName><SchemaName>dbo</SchemaName><ObjectName>MSmerge_contAE2EB588ABFA4C1 3825D4D44CB2D50CB_90</ObjectName><ObjectType>VIEW</ObjectType><TSQLCommand><SetOptions
> ANSI_NULLS="OFF" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON"
> QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" /><CommandText>create view
> dbo.MSmerge_contAE2EB588ABFA4C13825D4D44CB2D50CB_9 0 as select * from
> MSmerge_contents where 1 =
> 2</CommandText></TSQLCommand></EVENT_INSTANCE>]
> Parameters:
> Stack: at
> Microsoft.SqlServer.Replication.AgentCore.ReMapSql Exception(SqlException
> e, SqlCommand command)
> at
> Microsoft.SqlServer.Replication.AgentCore.AgentExe cuteNonQuery(SqlCommand
> command, Int32 queryTimeout)
> at
> Microsoft.SqlServer.Replication.AgentCore.ExecuteD iscardResults(CommandSetupDelegate
> commandSetupDelegate, Int32 queryTimeout)
> at
> Microsoft.SqlServer.Replication.AgentCore.ExecuteA dHocQueryDiscardResults(String
> strQuery, SqlConnection connection)
> at
> Microsoft.SqlServer.Replication.Snapshot.MergeSnap shotProvider.GenerateSystemTableBcpFiles()
> at
> Microsoft.SqlServer.Replication.Snapshot.MergeSnap shotProvider.DoPostArticleFilesGenerationProcessin g()
> at
> Microsoft.SqlServer.Replication.Snapshot.SqlServer SnapshotProvider.GenerateSnapshot()
> at
> Microsoft.SqlServer.Replication.SnapshotGeneration Agent.InternalRun()
> at Microsoft.SqlServer.Replication.AgentCore.Run() (Source:
> MSSQLServer, Error number: 103)
> Get help: http://help/103
> Server XF4SWCS464, Level 15, State 4, Procedure , Line 1
> The identifier that starts with
> '<EVENT_INSTANCE><EventType>CREATE_VIEW</EventType><PostTime>2006-11-09T09:56:48.390</PostTime><SPID>152</SPID><ServerName>XF4SWC'
> is too long. Maximum length is 128. (Source: MSSQLServer, Error number:
> 103)
> Get help: http://help/103
>
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
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
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.
Wednesday, March 21, 2012
Merge Replication programming with SQL Snapshot and SQL Distribution Control
Hello all, I am try to implement Merge replication just
like this
article shows for transaction type replicaion
http://support.microsoft.com/default.aspx?scid=kb;EN-
US;319649
using SQL Snapshot and SQL Distribution Control with
Visual C# .NET
First ..I dont see
Use a Distribution Agent that is independent of other
publications from this database and Snapshot files are
always available to initialize new subscriptions
immediately check boxes under subscription options.
second..
I get an error on SQL Snapshot object initialization
Erro # 24006 and message : The publication does not exist.
Sri,
you're using the wrong article - the one you have applies to transactional
and snapshot. For merge, have a look at this one:
http://support.microsoft.com/default...b;EN-US;319647
HTH,
Paul Ibison
|||Paul, Thank you very much.It worked like a charm.
Once again Thanks a million.
Srinu
>--Original Message--
>Sri,
>you're using the wrong article - the one you have applies
to transactional
>and snapshot. For merge, have a look at this one:
>http://support.microsoft.com/default.aspx?scid=kb;EN-
US;319647
>HTH,
>Paul Ibison
>
>.
>
like this
article shows for transaction type replicaion
http://support.microsoft.com/default.aspx?scid=kb;EN-
US;319649
using SQL Snapshot and SQL Distribution Control with
Visual C# .NET
First ..I dont see
Use a Distribution Agent that is independent of other
publications from this database and Snapshot files are
always available to initialize new subscriptions
immediately check boxes under subscription options.
second..
I get an error on SQL Snapshot object initialization
Erro # 24006 and message : The publication does not exist.
Sri,
you're using the wrong article - the one you have applies to transactional
and snapshot. For merge, have a look at this one:
http://support.microsoft.com/default...b;EN-US;319647
HTH,
Paul Ibison
|||Paul, Thank you very much.It worked like a charm.
Once again Thanks a million.
Srinu
>--Original Message--
>Sri,
>you're using the wrong article - the one you have applies
to transactional
>and snapshot. For merge, have a look at this one:
>http://support.microsoft.com/default.aspx?scid=kb;EN-
US;319647
>HTH,
>Paul Ibison
>
>.
>
Monday, March 12, 2012
Merge Replication Issue SQL Server 2005
I have set up merge replication with 1 publisher and 1 subscriber. Distribution is handled by a 3rd server.
I can generate a snapshot at the publisher and apply it to my subscriber. But when I insert some data (approx 30,000 rows) , the Synchronization agent gives the following error when It runs:
The merge process is retrying a failed operation made to article 'xxx' - Reason: 'The Merge Agent was unable to synchronize the row due to one or more unanticipated errors in the batch of changes. When troubleshooting, increase the -OutputVerboseLevel setting, restart the agent, and check for and resolve any errors generated by the database engine. '.
I have increased the OutputVerboseLevel setting and specified a file path in the -OutputMessageFile but the File is not being populated. All the references on books online say to put the file path in the -Output parameter but when I do it says it can only hold an integer value. So I cant see the errors generated by the database engine.
Could anybody please assist with this issue?
Thanks
If you increase the verbose level you should see the messges in MSmerge_history.
Also Merge replication doesnt use the distribution agent or much of the distribution database so its not useful to have a separate distributor.
Martin
merge replication issue
Hi all,
Setup: SQL2K (Enterprise) [SQLSVR1] in Network 1 - 2 publications (merge
for tables that data changes in often, and snapshot for stuff that is
batched after a few months) - there are a number of MSDE subscribers to
these publications in this network.
The same setup is in Network 2 [SQLSVR2].
SQLSVR2 subscribes to the SQLSVR1 publications, and re-publishes them for
Network 2 MSDE subscribers.
I have an app that works on the local msde installations - any changes to
a specific database is then replicated.
Everything works great in Network 1.
On Network 2, I tried to insert into a specific table and I get a
"Invalid object name 'ctsv_EEF98A7665F64374A3C47E6A3D87155E'." error ?
Other tables do not appear to be effected ?
I read a post that appear to suggest security context in setting up the
publications ? - the publications security context is a sql user, and the
distribution folder is shared so that all users in the network can read
and write to it
(http://groups.google.com/groups?hl=e...3DN%26tab%3Dwg)
Any information on helping me resolve this would be useful.
tia,
Best Regards,
Ranjeet.
how did you deploy the problem subscriber?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"ranjeet" <not-telling> wrote in message
news:opsemtjwu6v8so89@.athwalrt40.castlewood.co.uk. ..
> Hi all,
> Setup: SQL2K (Enterprise) [SQLSVR1] in Network 1 - 2 publications (merge
> for tables that data changes in often, and snapshot for stuff that is
> batched after a few months) - there are a number of MSDE subscribers to
> these publications in this network.
> The same setup is in Network 2 [SQLSVR2].
> SQLSVR2 subscribes to the SQLSVR1 publications, and re-publishes them for
> Network 2 MSDE subscribers.
> I have an app that works on the local msde installations - any changes to
> a specific database is then replicated.
> Everything works great in Network 1.
> On Network 2, I tried to insert into a specific table and I get a
> "Invalid object name 'ctsv_EEF98A7665F64374A3C47E6A3D87155E'." error ?
> Other tables do not appear to be effected ?
> I read a post that appear to suggest security context in setting up the
> publications ? - the publications security context is a sql user, and the
> distribution folder is shared so that all users in the network can read
> and write to it
>
(http://groups.google.com/groups?hl=e...32G%23zQRJBHA.
388%40tkmsftngp02&rnum=1&prev=/groups%3Fq%3Dinvalid%2520object%2520name%2520
%27tsvw_%26hl%3Den%26lr%3D%26ie%3DUTF-8%26sa%3DN%26tab%3Dwg)
> Any information on helping me resolve this would be useful.
> tia,
> Best Regards,
> Ranjeet.
|||used enterprise manager logged on as a user in network 1 (registered the
subscriber in EM, and clicked through to add pull subscription to SQLSVR2
in network 2).
the subscriber and publisher were part of network 2.
|||when you start getting messages like this telling you you have missing
views, or the merge agent expects a different view than the one present on
your subscriber, you have inconsistent replication metadata.
While it is entirely possible to hack your way through the triggers and
adjust them for the views the merge agent expects to use, you are better off
to redeploy your subscriber.
I'm still not exactly sure how you deployed your subscribers. The way to do
it is to create a publication on SQLSRV1, and deploy a subscription to
SQLSRV2. After the subscription is deployed to SQLSRV2, you then create
your publication on SQLSRV2 and deploy the subscriptions to the MSDE
subscribers.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"ranjeet" <not-telling> wrote in message
news:opsem0kgrgv8so89@.athwalrt40.castlewood.co.uk. ..
> used enterprise manager logged on as a user in network 1 (registered the
> subscriber in EM, and clicked through to add pull subscription to SQLSVR2
> in network 2).
> the subscriber and publisher were part of network 2.
|||Hi Hillary,
thanks for the info.
My deployment was as you described.
I am now rebuilding the subscriptions and publications for network 2 and
SQLSVR2 in the hope that this fixes this most frustrating problem. I am
in the fortunate position that my users are not yet ready to go live in
network 2 (phew!).
I will update this post with my results incase it is ever useful to anyone.
thanks again,
Ranjeet.
|||recreating the server subscription and publications resolved this problem.
thanks again for your help hilary.
Ranjeet.
Setup: SQL2K (Enterprise) [SQLSVR1] in Network 1 - 2 publications (merge
for tables that data changes in often, and snapshot for stuff that is
batched after a few months) - there are a number of MSDE subscribers to
these publications in this network.
The same setup is in Network 2 [SQLSVR2].
SQLSVR2 subscribes to the SQLSVR1 publications, and re-publishes them for
Network 2 MSDE subscribers.
I have an app that works on the local msde installations - any changes to
a specific database is then replicated.
Everything works great in Network 1.
On Network 2, I tried to insert into a specific table and I get a
"Invalid object name 'ctsv_EEF98A7665F64374A3C47E6A3D87155E'." error ?
Other tables do not appear to be effected ?
I read a post that appear to suggest security context in setting up the
publications ? - the publications security context is a sql user, and the
distribution folder is shared so that all users in the network can read
and write to it
(http://groups.google.com/groups?hl=e...3DN%26tab%3Dwg)
Any information on helping me resolve this would be useful.
tia,
Best Regards,
Ranjeet.
how did you deploy the problem subscriber?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"ranjeet" <not-telling> wrote in message
news:opsemtjwu6v8so89@.athwalrt40.castlewood.co.uk. ..
> Hi all,
> Setup: SQL2K (Enterprise) [SQLSVR1] in Network 1 - 2 publications (merge
> for tables that data changes in often, and snapshot for stuff that is
> batched after a few months) - there are a number of MSDE subscribers to
> these publications in this network.
> The same setup is in Network 2 [SQLSVR2].
> SQLSVR2 subscribes to the SQLSVR1 publications, and re-publishes them for
> Network 2 MSDE subscribers.
> I have an app that works on the local msde installations - any changes to
> a specific database is then replicated.
> Everything works great in Network 1.
> On Network 2, I tried to insert into a specific table and I get a
> "Invalid object name 'ctsv_EEF98A7665F64374A3C47E6A3D87155E'." error ?
> Other tables do not appear to be effected ?
> I read a post that appear to suggest security context in setting up the
> publications ? - the publications security context is a sql user, and the
> distribution folder is shared so that all users in the network can read
> and write to it
>
(http://groups.google.com/groups?hl=e...32G%23zQRJBHA.
388%40tkmsftngp02&rnum=1&prev=/groups%3Fq%3Dinvalid%2520object%2520name%2520
%27tsvw_%26hl%3Den%26lr%3D%26ie%3DUTF-8%26sa%3DN%26tab%3Dwg)
> Any information on helping me resolve this would be useful.
> tia,
> Best Regards,
> Ranjeet.
|||used enterprise manager logged on as a user in network 1 (registered the
subscriber in EM, and clicked through to add pull subscription to SQLSVR2
in network 2).
the subscriber and publisher were part of network 2.
|||when you start getting messages like this telling you you have missing
views, or the merge agent expects a different view than the one present on
your subscriber, you have inconsistent replication metadata.
While it is entirely possible to hack your way through the triggers and
adjust them for the views the merge agent expects to use, you are better off
to redeploy your subscriber.
I'm still not exactly sure how you deployed your subscribers. The way to do
it is to create a publication on SQLSRV1, and deploy a subscription to
SQLSRV2. After the subscription is deployed to SQLSRV2, you then create
your publication on SQLSRV2 and deploy the subscriptions to the MSDE
subscribers.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"ranjeet" <not-telling> wrote in message
news:opsem0kgrgv8so89@.athwalrt40.castlewood.co.uk. ..
> used enterprise manager logged on as a user in network 1 (registered the
> subscriber in EM, and clicked through to add pull subscription to SQLSVR2
> in network 2).
> the subscriber and publisher were part of network 2.
|||Hi Hillary,
thanks for the info.
My deployment was as you described.
I am now rebuilding the subscriptions and publications for network 2 and
SQLSVR2 in the hope that this fixes this most frustrating problem. I am
in the fortunate position that my users are not yet ready to go live in
network 2 (phew!).
I will update this post with my results incase it is ever useful to anyone.
thanks again,
Ranjeet.
|||recreating the server subscription and publications resolved this problem.
thanks again for your help hilary.
Ranjeet.
Merge Replication Initial Snapshot
SQL 2000.
I have a merge replication system running in a 24/7 environment that
cannot be brought offline or locked up. When i try to run my snapshot
agent, it locks up the primary DB (there are many tables in this that
are very large, like hundreds of millions of rows). is there a way
around the initial snapshot, or a way to speed it up? I have read in
BOL that it may be possible with a backup and removable storage, or
using BCP.
Has anyone been able to get merge replication working without locking
up the db or bringing it offline? If so, how?
Thanks in advance!!
No, AFAIK - there is no way to do this.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"colbycrane" <gstarnes@.gmail.com> wrote in message
news:1104268201.081080.154550@.z14g2000cwz.googlegr oups.com...
> SQL 2000.
> I have a merge replication system running in a 24/7 environment that
> cannot be brought offline or locked up. When i try to run my snapshot
> agent, it locks up the primary DB (there are many tables in this that
> are very large, like hundreds of millions of rows). is there a way
> around the initial snapshot, or a way to speed it up? I have read in
> BOL that it may be possible with a backup and removable storage, or
> using BCP.
> Has anyone been able to get merge replication working without locking
> up the db or bringing it offline? If so, how?
> Thanks in advance!!
>
|||Backup and restore can be used for a nosync initialization, but the data
shouldn't be changed on the publisher while this takes place so effectively
it's the same. In transactional replication there is the option to have
concurrent snapshot bgeneration, but this is not possible for merge. So, if
you must use merge, essentially you'll need some system downtime.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||But the problem with a backup restore is that the rowguid column still has
to be added, preferably before the backup is done. This involves an alter
table and locking.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OqLd1pZ7EHA.2192@.TK2MSFTNGP14.phx.gbl...
> Backup and restore can be used for a nosync initialization, but the data
> shouldn't be changed on the publisher while this takes place so
> effectively
> it's the same. In transactional replication there is the option to have
> concurrent snapshot bgeneration, but this is not possible for merge. So,
> if
> you must use merge, essentially you'll need some system downtime.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Good point
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||We employ a fairly complicated ETL routine to add rows to tables without
locking the db. This can also be used to migrate the data to the
subscriber db. Heres how it works:
Say tableA needs a new column (tableA has 60m rows). We create TableB,
which is TableA + the new column. We insert / select with (nolock) to
migrate the data. This may introduce dirty reads, thus bad data to
tableB so, at the same time, we put an on insert, update, delete trigger
on table that feeds any changes to TableC. After the inital insert /
select is finished, we run a daemon that takes from TableC and applies
it to tableA. Ultimately, we run the daemon repeatedly until TableC
only has a few records (6 second delay is acceptable), so we then put
into a transaction the final 'reconciliation' to lock tableA, push the
final changes from tableC to tableB, do an sp_rename, and off we go...
If we were able to do this, would the initialization of Merge
replication still read all the rows out of TableA, thus still locking
it?
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Greg,
If I read you correctly, in your example, you're saying that you have a
method of making an identical copy of a table without taking a shared lock
on it, or at least the lock used at the final step is minimal and probably
involves a few rows. If you can make such a copy on the subscriber then you
could indeed do a noinit initialization. The guid column with rowguid
attribute could be added using your current methodology. During
initialization, you need to be sure that there are no changes to the data at
the publisher, but this could last as little as 30 secs.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
I have a merge replication system running in a 24/7 environment that
cannot be brought offline or locked up. When i try to run my snapshot
agent, it locks up the primary DB (there are many tables in this that
are very large, like hundreds of millions of rows). is there a way
around the initial snapshot, or a way to speed it up? I have read in
BOL that it may be possible with a backup and removable storage, or
using BCP.
Has anyone been able to get merge replication working without locking
up the db or bringing it offline? If so, how?
Thanks in advance!!
No, AFAIK - there is no way to do this.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"colbycrane" <gstarnes@.gmail.com> wrote in message
news:1104268201.081080.154550@.z14g2000cwz.googlegr oups.com...
> SQL 2000.
> I have a merge replication system running in a 24/7 environment that
> cannot be brought offline or locked up. When i try to run my snapshot
> agent, it locks up the primary DB (there are many tables in this that
> are very large, like hundreds of millions of rows). is there a way
> around the initial snapshot, or a way to speed it up? I have read in
> BOL that it may be possible with a backup and removable storage, or
> using BCP.
> Has anyone been able to get merge replication working without locking
> up the db or bringing it offline? If so, how?
> Thanks in advance!!
>
|||Backup and restore can be used for a nosync initialization, but the data
shouldn't be changed on the publisher while this takes place so effectively
it's the same. In transactional replication there is the option to have
concurrent snapshot bgeneration, but this is not possible for merge. So, if
you must use merge, essentially you'll need some system downtime.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||But the problem with a backup restore is that the rowguid column still has
to be added, preferably before the backup is done. This involves an alter
table and locking.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OqLd1pZ7EHA.2192@.TK2MSFTNGP14.phx.gbl...
> Backup and restore can be used for a nosync initialization, but the data
> shouldn't be changed on the publisher while this takes place so
> effectively
> it's the same. In transactional replication there is the option to have
> concurrent snapshot bgeneration, but this is not possible for merge. So,
> if
> you must use merge, essentially you'll need some system downtime.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Good point
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||We employ a fairly complicated ETL routine to add rows to tables without
locking the db. This can also be used to migrate the data to the
subscriber db. Heres how it works:
Say tableA needs a new column (tableA has 60m rows). We create TableB,
which is TableA + the new column. We insert / select with (nolock) to
migrate the data. This may introduce dirty reads, thus bad data to
tableB so, at the same time, we put an on insert, update, delete trigger
on table that feeds any changes to TableC. After the inital insert /
select is finished, we run a daemon that takes from TableC and applies
it to tableA. Ultimately, we run the daemon repeatedly until TableC
only has a few records (6 second delay is acceptable), so we then put
into a transaction the final 'reconciliation' to lock tableA, push the
final changes from tableC to tableB, do an sp_rename, and off we go...
If we were able to do this, would the initialization of Merge
replication still read all the rows out of TableA, thus still locking
it?
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Greg,
If I read you correctly, in your example, you're saying that you have a
method of making an identical copy of a table without taking a shared lock
on it, or at least the lock used at the final step is minimal and probably
involves a few rows. If you can make such a copy on the subscriber then you
could indeed do a noinit initialization. The guid column with rowguid
attribute could be added using your current methodology. During
initialization, you need to be sure that there are no changes to the data at
the publisher, but this could last as little as 30 secs.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Merge Replication in SQL2K5
Normally when snapshot is applied only publisher data is downloaded to
subscriber.
Is there a way to have subscriber upload its table data when
synchronization is done for the first time i.e. when snapshot is applied.
Thanks!!!
Perhaps I've misunderstood you, but when the snapshot is applied, the
subscriber's table will be dropped and the associated data lost. If there is
valuable subscriber data that you don't want to lose, then I'd use a linked
server (or DataCompare) to copy it over before publishing the table from the
publisher.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul, Thanks!!!. I want to create the rowguid column at subscriber and start
synchronization without the initial snapshot from publisher.
if i copy the subscribers data to publisher and not apply the initial
snapshot then i think i am ok..
Thanks!!!
"Paul Ibison" <PaulIbison@.discussions.microsoft.com> wrote in message
news:E9219D8F-B39F-4D5B-A20E-1E64477493C9@.microsoft.com...
> Perhaps I've misunderstood you, but when the snapshot is applied, the
> subscriber's table will be dropped and the associated data lost. If there
> is
> valuable subscriber data that you don't want to lose, then I'd use a
> linked
> server (or DataCompare) to copy it over before publishing the table from
> the
> publisher.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||You can do a nosync initialization. In this case you'll need to have the
rowguid column on the publisher and subscriber with identical values to start
off with. However, possibly the cleanest and easiest solution is to start
with all the data at the publisher and let replication take care of the
transfer.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
subscriber.
Is there a way to have subscriber upload its table data when
synchronization is done for the first time i.e. when snapshot is applied.
Thanks!!!
Perhaps I've misunderstood you, but when the snapshot is applied, the
subscriber's table will be dropped and the associated data lost. If there is
valuable subscriber data that you don't want to lose, then I'd use a linked
server (or DataCompare) to copy it over before publishing the table from the
publisher.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul, Thanks!!!. I want to create the rowguid column at subscriber and start
synchronization without the initial snapshot from publisher.
if i copy the subscribers data to publisher and not apply the initial
snapshot then i think i am ok..
Thanks!!!
"Paul Ibison" <PaulIbison@.discussions.microsoft.com> wrote in message
news:E9219D8F-B39F-4D5B-A20E-1E64477493C9@.microsoft.com...
> Perhaps I've misunderstood you, but when the snapshot is applied, the
> subscriber's table will be dropped and the associated data lost. If there
> is
> valuable subscriber data that you don't want to lose, then I'd use a
> linked
> server (or DataCompare) to copy it over before publishing the table from
> the
> publisher.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||You can do a nosync initialization. In this case you'll need to have the
rowguid column on the publisher and subscriber with identical values to start
off with. However, possibly the cleanest and easiest solution is to start
with all the data at the publisher and let replication take care of the
transfer.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Friday, March 9, 2012
Merge replication GUIDs.
I have a product that sits on a main server and wish to implement
functionality to allow salesmen to come along, pick up a snapshot of the
database, go away and maybe modify/add to it and then come back and
"synchronise" their data. I'm reading up on Merge Replication for this
purpose. But anyway, I created a publisher on my server and it went away
and generated a "rowguid" column on all of my tables (my tables all have an
Identity column key field).
I would have expected SQL Server to automatically generate a guid for new
inserts (in a similar way to it's TIMESTAMP), but it appears it doesn't,
despite the fact I have "(newid())" as the default for the column. It
always
inserts the same value: {00000000-0000-0000-0000-000000000000}.
So, back to basics, now I have a guid field for each record, how do I manage
inserts, or is the rowguid field maintained by the replication process?
Thanks.
Let replication create this column. If you don't create it exactly right
replication will add a new column of its own.
Merge replication will auto supply the guid values. You should not specify
them in your insert statements. So if your table looks like this
Create Table Test
(pk int not null,
charcol char(20),
rowguid uniqueidentifier default newid())
Your insert would look like this
insert into test (pk, charcol) values (1, 'test')
Note that you do not need to supply the value of the rowguid col.
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
"Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in
message news:d7php9$ca4$1$830fa7a5@.news.demon.co.uk...
> I have a product that sits on a main server and wish to implement
> functionality to allow salesmen to come along, pick up a snapshot of the
> database, go away and maybe modify/add to it and then come back and
> "synchronise" their data. I'm reading up on Merge Replication for this
> purpose. But anyway, I created a publisher on my server and it went away
> and generated a "rowguid" column on all of my tables (my tables all have
an
> Identity column key field).
> I would have expected SQL Server to automatically generate a guid for new
> inserts (in a similar way to it's TIMESTAMP), but it appears it doesn't,
> despite the fact I have "(newid())" as the default for the column. It
> always
> inserts the same value: {00000000-0000-0000-0000-000000000000}.
> So, back to basics, now I have a guid field for each record, how do I
manage
> inserts, or is the rowguid field maintained by the replication process?
>
> Thanks.
>
>
functionality to allow salesmen to come along, pick up a snapshot of the
database, go away and maybe modify/add to it and then come back and
"synchronise" their data. I'm reading up on Merge Replication for this
purpose. But anyway, I created a publisher on my server and it went away
and generated a "rowguid" column on all of my tables (my tables all have an
Identity column key field).
I would have expected SQL Server to automatically generate a guid for new
inserts (in a similar way to it's TIMESTAMP), but it appears it doesn't,
despite the fact I have "(newid())" as the default for the column. It
always
inserts the same value: {00000000-0000-0000-0000-000000000000}.
So, back to basics, now I have a guid field for each record, how do I manage
inserts, or is the rowguid field maintained by the replication process?
Thanks.
Let replication create this column. If you don't create it exactly right
replication will add a new column of its own.
Merge replication will auto supply the guid values. You should not specify
them in your insert statements. So if your table looks like this
Create Table Test
(pk int not null,
charcol char(20),
rowguid uniqueidentifier default newid())
Your insert would look like this
insert into test (pk, charcol) values (1, 'test')
Note that you do not need to supply the value of the rowguid col.
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
"Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in
message news:d7php9$ca4$1$830fa7a5@.news.demon.co.uk...
> I have a product that sits on a main server and wish to implement
> functionality to allow salesmen to come along, pick up a snapshot of the
> database, go away and maybe modify/add to it and then come back and
> "synchronise" their data. I'm reading up on Merge Replication for this
> purpose. But anyway, I created a publisher on my server and it went away
> and generated a "rowguid" column on all of my tables (my tables all have
an
> Identity column key field).
> I would have expected SQL Server to automatically generate a guid for new
> inserts (in a similar way to it's TIMESTAMP), but it appears it doesn't,
> despite the fact I have "(newid())" as the default for the column. It
> always
> inserts the same value: {00000000-0000-0000-0000-000000000000}.
> So, back to basics, now I have a guid field for each record, how do I
manage
> inserts, or is the rowguid field maintained by the replication process?
>
> Thanks.
>
>
Merge replication failure
After working for about 30 minutes (delivering the snapshot) the replication
failed
I checked the Session details of the subscription and I found Action Message
saying the 'The process could not deliver the snapshot to the Subscriber.'
In the error information under Data Source I found the following message
'General network error. Check your network documentation.'
Checking the network log I got the following message: SQL Server Scheduled
Job 'SERVER\SERVER1-MRN1-MRN1_rep-XYZSERVER.ABC.LOCAL-12'
(0x97E3EAE1F1106144B16C808A6492CB9C) - Status: Failed - Invoked on:
2006-09-28 20:23:00 - Message: The job failed. The Job was invoked by
Schedule 35 (Replication agent schedule.). The last step to run was step 3
(Detect nonlogged agent shutdown.).
Any clue?
Samuel
Samuel,
what happens if you restart the agent - normally this'll solve a network
outage.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||It happens consistantly during the initial stage when the entire database
was replicated
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uN7b$g84GHA.1188@.TK2MSFTNGP05.phx.gbl...
> Samuel,
> what happens if you restart the agent - normally this'll solve a network
> outage.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
|||Samuel,
I'd enable logging to see if this gets more info
(http://support.microsoft.com/?id=312292), especially if the failure is at
the same point each time. Otherwise I'd look at monitoring the network for
outages - network connectivity problems.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
failed
I checked the Session details of the subscription and I found Action Message
saying the 'The process could not deliver the snapshot to the Subscriber.'
In the error information under Data Source I found the following message
'General network error. Check your network documentation.'
Checking the network log I got the following message: SQL Server Scheduled
Job 'SERVER\SERVER1-MRN1-MRN1_rep-XYZSERVER.ABC.LOCAL-12'
(0x97E3EAE1F1106144B16C808A6492CB9C) - Status: Failed - Invoked on:
2006-09-28 20:23:00 - Message: The job failed. The Job was invoked by
Schedule 35 (Replication agent schedule.). The last step to run was step 3
(Detect nonlogged agent shutdown.).
Any clue?
Samuel
Samuel,
what happens if you restart the agent - normally this'll solve a network
outage.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||It happens consistantly during the initial stage when the entire database
was replicated
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uN7b$g84GHA.1188@.TK2MSFTNGP05.phx.gbl...
> Samuel,
> what happens if you restart the agent - normally this'll solve a network
> outage.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
|||Samuel,
I'd enable logging to see if this gets more info
(http://support.microsoft.com/?id=312292), especially if the failure is at
the same point each time. Otherwise I'd look at monitoring the network for
outages - network connectivity problems.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
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_'
Actually i am trying to implement replication for navision. I am using Microsoft Business Solution Navision (With sql option).
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 while applying Snapshot
Hi,
i am getting the below error while applying running the Synchronization agent for the Subscriber. I have created replication topology with one central server and one subscriber. Here central server has windows server 2003 and subscriber has windows XP. Both are having SQL server 2005. After creating the merge subscriber, i am runnnig the Synchronization agent manually for the first time. While running that i am getting below error. Anybody aware of this error.
2006-06-24 00:26:00.175 Applying the snapshot to the Subscriber
2006-06-24 00:26:02.722 The schema script 'D_NUM_7.sch' could not be propagated to the subscriber.
2006-06-24 00:26:02.784 Category:NULL
Source: Merge Replication Provider
Number: -2147201001
Message: The schema script 'D_NUM_7.sch' could not be propagated to the subscriber.
2006-06-24 00:26:02.816 Category:AGENT
Source: WMBT-07
Number: 0
Message: The process could not read file '\\WMBT-01\repldata\unc\LTR-IN001_TEST_PUB\20060624034804\D_NUM_7.sch' due to OS error 1265.
2006-06-24 00:26:02.831 Category:OS
Source:
Number: 1265
Message: The system detected a possible attempt to compromise security. Please ensure that you can contact the server that authenticated you.
Greg,
Thanks for giving me the response...
Actually i am not clear about the Account... How to see the Account under which Agent is running?
I still dont understand where we are linking the Account and Agent.
Can you help on this ?
Thanks in advance.
|||Greg,
Are you asking the about Agent in Central Server or in the Subscriber.
Thanks.
|||When you setup replication, you are asked to specify security credentials for the Snapshot agent, Log Reader Agent, Distribution Agent, Merge Agent, Queued Reader Agent. (Which agents you need to specify credentials for vary based on the method of replication.) The account that you specified for either the distribution agent (for snapshot or transactional replication) or the merge agent (for merge replication) needs to have the authority to access the snapshot folder in order for this to work successfully.|||There are two places you may need to check.
1. Since snapshot files are saved under distributor, in your case, it may be the central server, which is both publisher and distributor, so make sure your publication snapshot files are saved under an alternate folder, UNC folder, which can be accessed by merge agent running on the subscriber.
2. Check merge agent account which is used to connect to distributor, it must have read permissions on the snapshot share. You can check it through open merge agent job properties.
Hope the above will be helpful.
Thanks
Yunjing
|||Hi All,
Thanks for all you replies. I solved the problem i faced.
Normally when i create a Subscriber for Account under which Merge Agent will run i used to give as "Run Under SQL Server Agent Service Account" . It was working for me all these days. In all the machines where I created Replication was having windows XP. But when i was trying to create the Replication with systems with windows Server 2003, i have got the above said error.
To solve that error i have created one windows account in the Publisher and Subscriber with same name and same password. Then while creating the Publisher and Subscriber I was using this windows account as process Account for all the Agents. After that it was working fine. Here the windows account has to be there is both Publisher and Subscriber with same name and same Password. It was working for me. I have added that windows account as part of Administrator Group.
Thanks,
Thams.
Merge Replication error while applying Snapshot
Hi,
i am getting the below error while applying running the Synchronization agent for the Subscriber. I have created replication topology with one central server and one subscriber. Here central server has windows server 2003 and subscriber has windows XP. Both are having SQL server 2005. After creating the merge subscriber, i am runnnig the Synchronization agent manually for the first time. While running that i am getting below error. Anybody aware of this error.
2006-06-24 00:26:00.175 Applying the snapshot to the Subscriber
2006-06-24 00:26:02.722 The schema script 'D_NUM_7.sch' could not be propagated to the subscriber.
2006-06-24 00:26:02.784 Category:NULL
Source: Merge Replication Provider
Number: -2147201001
Message: The schema script 'D_NUM_7.sch' could not be propagated to the subscriber.
2006-06-24 00:26:02.816 Category:AGENT
Source: WMBT-07
Number: 0
Message: The process could not read file '\\WMBT-01\repldata\unc\LTR-IN001_TEST_PUB\20060624034804\D_NUM_7.sch' due to OS error 1265.
2006-06-24 00:26:02.831 Category:OS
Source:
Number: 1265
Message: The system detected a possible attempt to compromise security. Please ensure that you can contact the server that authenticated you.
Greg,
Thanks for giving me the response...
Actually i am not clear about the Account... How to see the Account under which Agent is running?
I still dont understand where we are linking the Account and Agent.
Can you help on this ?
Thanks in advance.
|||Greg,
Are you asking the about Agent in Central Server or in the Subscriber.
Thanks.
|||When you setup replication, you are asked to specify security credentials for the Snapshot agent, Log Reader Agent, Distribution Agent, Merge Agent, Queued Reader Agent. (Which agents you need to specify credentials for vary based on the method of replication.) The account that you specified for either the distribution agent (for snapshot or transactional replication) or the merge agent (for merge replication) needs to have the authority to access the snapshot folder in order for this to work successfully.|||There are two places you may need to check.
1. Since snapshot files are saved under distributor, in your case, it may be the central server, which is both publisher and distributor, so make sure your publication snapshot files are saved under an alternate folder, UNC folder, which can be accessed by merge agent running on the subscriber.
2. Check merge agent account which is used to connect to distributor, it must have read permissions on the snapshot share. You can check it through open merge agent job properties.
Hope the above will be helpful.
Thanks
Yunjing
|||Hi All,
Thanks for all you replies. I solved the problem i faced.
Normally when i create a Subscriber for Account under which Merge Agent will run i used to give as "Run Under SQL Server Agent Service Account" . It was working for me all these days. In all the machines where I created Replication was having windows XP. But when i was trying to create the Replication with systems with windows Server 2003, i have got the above said error.
To solve that error i have created one windows account in the Publisher and Subscriber with same name and same password. Then while creating the Publisher and Subscriber I was using this windows account as process Account for all the Agents. After that it was working fine. Here the windows account has to be there is both Publisher and Subscriber with same name and same Password. It was working for me. I have added that windows account as part of Administrator Group.
Thanks,
Thams.
Subscribe to:
Posts (Atom)