Hi,
I have set up one laptop as the Distributer/Publisher. Went through the
wizard and set up a Publication also, used Pubs. Then registered another
remote laptop that I can see via the network, and it can see me. I went
through the wizard again and set up a Push to that laptop. Said it ran good,
and I can see the tables on the remote laptop now.
I deleted the Push and keep trying to create a Pull at the other laptop,
(subscriber). The wizard sets it up, but when it Starts Syncronizing, it
immediately gets the big Red X.
The error said The schema script
'\\ACER\ReplShare\ReplData\unc\ACER_pubs_pubs_arti cles\20050928212317\stores_1.sch' could not be propagated to the subscriber.
I can see this share from both ends. I have read a ton about the accounts
the agent has to run under in the last 12 hours, but can't see what I am
doing wrong.
Is there a trick here?
Thanks.
Steve,
try logging on to the subscriber laptop using the same account that the sql
server agent uses as a service account. The see if you can browse to the
snapshot folder
\\ACER\ReplShare\ReplData\unc\ACER_pubs_pubs_artic les\20050928212317. If you
can, see if you can copy the contents of this directory locally. I'm
guessing that the first part won't be possible due to permission
restrictions, but please post back with your results.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
I had tried some other things prior to being able to read your response.
Here is what I have done that is now working.
I set up new SQL users on both Publisher/Dist. and Subscriber, giving them
the proper roles. I then set up a new Windows login on each box, "Bob", of
type admin. I then changed both the MSSQLSERVICE and SQLAGENTSERVICE on each
to run under "Bob". When it asks for logon credintials for the subscriber, I
use the sa, when it asks for the publisher, I use the new SQL user I had set
up. Probably may have been able to use that for the subscriber instead of
the sa, don't know.
I have tried so many things since yesterday afternoon, I am a little unsure
as to what actually solved it. From all I read over night, having the two
service run under "Bob" was needed.
Thank you for the response,
Steve
"Paul Ibison" wrote:
> Steve,
> try logging on to the subscriber laptop using the same account that the sql
> server agent uses as a service account. The see if you can browse to the
> snapshot folder
> \\ACER\ReplShare\ReplData\unc\ACER_pubs_pubs_artic les\20050928212317. If you
> can, see if you can copy the contents of this directory locally. I'm
> guessing that the first part won't be possible due to permission
> restrictions, but please post back with your results.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||This is OK - what you've set up is known as pass-through authentication.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Is there a better or more preferred method? We have one laptop that acts as
the publisher/distributor, and two other laptops that will be subscribers.
They run over a wireless network. The two subscribers will be able to
initial pull merge replications.
Thanks,
Steve
"Paul Ibison" wrote:
> This is OK - what you've set up is known as pass-through authentication.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Steve,
are the laptops all on the same domain? If so, you could use a domain
account, which is given rights to the distributor's working folder. If not,
it's either pass-through, FTP, backup and restore or alternative snapshot
locations.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
sql
Showing posts with label access. Show all posts
Showing posts with label access. Show all posts
Friday, March 30, 2012
Monday, March 26, 2012
Merge Replication using SQL-DMO
I am in a process of learning Replication in MSDE, especially Merge Replication
Server runs on MS-XP Professional
------------
I have a sample Access project 'ReplTest' which has only table with only 2 columns.
DatabaseName : ReplTestDB
Table Name : TestTable
MSDE Instance Name : SVR\MYINSTANCE
Now I would like to know how I can configure this database for merge replication
using SQL-DMO
Laptop runs on MS-XP Professional
------------
I have another access project which is running on computer 2 and connected to the
ReplicaTest database.
MSDE Instance Name : LPT\MYINSTANCE
My task is, when I am disconnected from server I would like to have a local copy of
the database to work with and then, when reconnected, need synchronization with the
server database and continue working from server database.
How to write this replication process from scratch using SQL-DMO objects in both Server computer
and Laptop computer.
I dont have enterprise manager in both computers since they use only MSDE
Can anyone help me?
Thanks in advance
JPCheck out "Publishers, Distributors, and Subscribers" from BOL (Ihope you have BOL).|||The problem with BOL is it is so extensive and the scenario of my situation is not described. I could do replication in the same machine as distributor, publisher and suscriber, but what if the subscriber is another machine as in my case ?|||I found this within these forums:
The sample code you were after is for creating heterogenous publication. It does not work with SQL publications. Try to model after the following sample.
Sub CreateMergePub() Dim osvr As New SQLDMO.SQLServer Dim szLastErrorText As String
Dim lLastErrorNumber As Long
Dim szServerName As String
Dim oReplicationDatabase As SQLDMO.ReplicationDatabase
Dim oDistPublisher As New DistributionPublisher
Dim oMergePublication As New MergePublication
Dim oMergeArticle1 As New SQLDMO.MergeArticle
On Error GoTo ErrorHandler
szServerName = "your servername"
' To use NT integrated security ' osvr.LoginSecure = True
osvr.Connect szServerName
' Enable pubs database for publishing ' Set oReplicationDatabase =
osvr.Replication.ReplicationDatabases("pubs")
'oReplicationDatabase.EnableTransPublishing = True
' Set MergePublication properties, name, snapshot method, trans '
oMergePublication.Name = "Pub1" oMergePublication.SnapshotMethod =
SQLDMOInitSync_BCPNative
' Set non-default properties, i.e. if you do not set the following properties, '
default value will be used ' 'oMergePublication.PublicationAttributes =
SQLDMOPubAttrib_AllowPull Or SQLDMOPubAttrib_AllowAnonymous
'oMergePublication.RetentionPeriod = 64
'oMergePublication.SnapshotSchedule.FrequencyType = SQLDMOFreq_Daily
'oMergePublication.SnapshotSchedule.FrequencyInter val = 1
'oMergePublication.SnapshotSchedule.ActiveStartTim eOfDay = 233000
'oMergePublication.SnapshotSchedule.ActiveEndTimeO fDay = 235959
'oMergePublication.SnapshotSchedule.ActiveStartDat e = 0
'oMergePublication.SnapshotSchedule.ActiveEndDate = 19981205
oReplicationDatabase.MergePublications.Add oMergePublication
' Add article ' oMergeArticle1.Name = "Article1" oMergeArticle1.SourceObjectName
= "jobs" oMergeArticle1.SourceObjectOwner = "dbo"
'Add the article to the publication ' oMergePublication.MergeArticles.Add
oMergeArticle1
' Disconnect from SQL Server szServerName ' osvr.DisConnect Set osvr =
Nothing End
ErrorHandler:
szLastErrorText = Err.Description lLastErrorNumber = Err.Number
MsgBox szLastErrorText, vbOKOnly, "Error " + Trim$(Str$(Err.Number))
End Sub
--
Pung Xu, Microsoft This posting is provided "AS IS" with no warranties, and confers
no rights. Please do not send email directly to this alias. This alias is for
newsgroup purposes only.
"matt" <matt_e_davis@.hotmail.com> wrote in message
news:d0db2ad5.0205221240.7c427ed7@.posting.google.c om...
> I am having trouble creating a merge publication with SQL-DMO. I modeled my
> solution after the following code:
> --================================================ Sub CreatePublication() '
> Connect to SQL Server Distributor Dim oSqlServer As New SQLDMO.SQLServer
> oSqlServer.Connect "", "sa", "" On Error GoTo ErrorHandler
>
> Dim oSamplePublisher As DistributionPublisher Set oSamplePublisher =
>
oSqlServer.Replication.Distributor.DistributionPub lishers("SAMPLEPUBLISHER")
>
> ' Create Sample Publication Dim oSamplePublication As New
> DistributionPublication oSamplePublication.Name = "SamplePublication"
> oSamplePublication.PublicationDB = "SampleDatabase"
> oSamplePublication.PublicationType = SQLDMOPublication_Transactional
> oSamplePublication.VendorName = "Sample Vendor"
> oSamplePublication.LogReaderAgent = "SampleLogReaderAgent"
> oSamplePublication.SnapshotAgent = "SampleSnapShotAgent"
> oSamplePublication.Description = "Sample Publication Definition"
> oSamplePublication.PublicationAttributes = SQLDMOPubAttrib_AllowPush
>
> ' Add the Publication oSamplePublisher.DistributionPublications.Add
> oSamplePublication
>
> ' Create Sample Articles Dim oSampleArticle1 As New DistributionArticle
> oSampleArticle1.Name = "SampleArticle1" oSampleArticle1.Description = "Sampe
> Article1 Definition" oSampleArticle1.SourceObjectName = "SampleTable1"
>
> Dim oSampleArticle2 As New DistributionArticle oSampleArticle2.Name =
> "SampleArticle2" oSampleArticle2.Description = "Sample Article2 Definition"
> oSampleArticle2.SourceObjectName = "SampleTable2"
>
> ' Add the Articles to the Publication
> oSamplePublication.DistributionArticles.Add oSampleArticle2
> oSamplePublication.DistributionArticles.Add oSampleArticle1
>
> oSqlServer.Close Exit Sub ErrorHandler: PrintErrors oSqlServer Exit Sub End Sub
>
> --================================================ This does create a publication
> but when you right click (via Enterprise Mgr.) Replication Monitor > PublisherName
> > SamplePublication and select Properties an error occurs. The error states that
> the publication is not in the TransPublication collection. The same error occurs
> when you create a merge publication. Except the error is that the publication does
> not exist in the MergePublications collection.
>
> How can I add the publication to the MergePublications collection?? Does anyone
> have any sample code for this?
>
> I am new at SQL-DMO and VB so I am struggling.
>
> Thanks in advance for any help you can give!!
>
> Matt|||Hi
Thanks for the reply.
I solved my problem...
I think the following posting will be helphul to someone ...
The text is too long , so posted in 3 parts
Scenario.. PART 1 SERVER COMPUTER
Desktop - Installed Access 2003 & MSDE 2000
Machine Name - MYSVR
MSDE instance Name - 'MYSVR\MYINSTANCE'
User id - 'sa' Password - 'password'
1. Create an Access Project 'TestProj.adp'
2. Create a SQL Database 'TestDB'
3. Create Table Name 'TestTable'
Columns - TestID INT IDENTITY(1,1) PRIMARY KEY
- TestName NVARCHAR(50)
4. Add a record to the table
5. Create a text TestRep01.txt and paste the following
Note that machinename should be replaced with your machine name
/*Script to be copied */
use master
GO
exec sp_adddistributor @.distributor = @.@.servername, @.password = N''
GO
-- Updating the agent profile defaults
sp_MSupdate_agenttype_default @.profile_id = 1
GO
sp_MSupdate_agenttype_default @.profile_id = 2
GO
sp_MSupdate_agenttype_default @.profile_id = 4
GO
sp_MSupdate_agenttype_default @.profile_id = 6
GO
sp_MSupdate_agenttype_default @.profile_id = 11
GO
-- Adding the distribution database
exec sp_adddistributiondb @.database = N'distribution',
@.data_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL$MYINSTANCE\Data',
@.data_file = N'distribution.MDF',
@.data_file_size = 3,
@.log_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL$MYINSTANCE\Data',
@.log_file = N'distribution.LDF',
@.log_file_size = 3,
@.min_distretention = 0,
@.max_distretention = 72,
@.history_retention = 48,
@.security_mode = 1
GO
-- Adding the distribution publisher
exec sp_adddistpublisher @.publisher = @.@.servername,
@.distribution_db = N'distribution',
@.security_mode = 1,
@.working_directory = N'\\MYSVR\C$\Program Files\Microsoft SQL Server\MSSQL$MYSINSTANCE\ReplData',
@.trusted = N'false',
@.thirdparty_flag = 0
GO
/*script ends*/
5. Create a folder 'ReplData' under 'C:\Program Files\Microsoft SQL
Server\MSSQL$MYINSTANCE' if it is not there.
6. Make sure SQLServer agent is running, if not, start that now.
7. Open Command prompt and run osql.exe with following parameters
as in scenario
prompt:> osql -S MYSVR\MYINSTANCE -U sa -P password -i TestRep01.txt
-o ResultTestRep01.txt -b
8. If so far so good , copy the follwoing script to another text file TestRep02.txt
/* Script starts*/
-- Enabling the replication database
use master
GO
exec sp_replicationdboption @.dbname = N'TestDB',
@.optname = N'merge publish',
@.value = N'true'
GO
use [TestDB]
GO
-- Adding the merge publication
exec sp_addmergepublication @.publication = N'TestPub',
@.description = N'Merge publ of TestDB.',
@.retention = 14,
@.sync_mode = N'character',
@.allow_push = N'true',
@.allow_pull = N'true',
@.allow_anonymous = N'true',
@.enabled_for_internet = N'false',
@.centralized_conflicts = N'true',
@.dynamic_filters = N'false',
@.snapshot_in_defaultfolder = N'true',
@.compress_snapshot = N'false',
@.ftp_port = 21,
@.ftp_login = N'anonymous',
@.conflict_retention = 14,
@.keep_partition_changes = N'false',
@.allow_subscription_copy = N'false',
@.allow_synctoalternate = N'false',
@.add_to_active_directory = N'false',
@.max_concurrent_merge = 0,
@.max_concurrent_dynamic_snapshots = 0
exec sp_addpublication_snapshot @.publication = 'TestPub',
@.frequency_type = 4,
@.frequency_interval = 1,
@.frequency_relative_interval = 1,
@.frequency_recurrence_factor = 0,
@.frequency_subday = 1,
@.frequency_subday_interval = 5,
@.active_start_date = 0,
@.active_end_date = 0,
@.active_start_time_of_day = 500,
@.active_end_time_of_day = 235959,
GO
exec sp_grant_publication_access @.publication = N'TestPub',
@.login = N'sa'
GO
-- Adding the merge articles
exec sp_addmergearticle @.publication = N'TestPub',
@.article = N'TestTable',
@.source_owner = N'dbo',
@.source_object = N'TestTable',
@.type = N'table',
@.description = null,
@.column_tracking = N'true',
@.pre_creation_cmd = N'drop',
@.creation_script = null,
@.schema_option = 0x000000000000FFF1,
@.article_resolver = null,
@.subset_filterclause = null,
@.vertical_partition = N'false',
@.destination_owner = N'dbo',
@.auto_identity_range = N'false',
@.verify_resolver_signature = 0,
@.allow_interactive_resolver = N'false',
@.fast_multicol_updateproc = N'true',
@.check_permissions = 0
GO
/*Script Ends */
9. Open Command prompt and run osql.exe with following parameters
as in scenario
prompt:> osql -S MYSVR\MYINSTANCE -U sa -P password -i TestRep02.txt
-o ResultTestRep02.txt -b
10. If this works fine (check the ResultTestRep02.txt for errors) the proceed
Any doubts and corrections are welcome
Cheers
Jos|||Part 2 of hte previous positing
In server computer , TestProj application
11. Create a form 'TestReplication'
13. Create reference to objects SQLMerge, SQLDSnapshot
and SQLReplError
14. Add
-- Commnad buttons 'cmdSnapShot', 'cmdMergePub'
-- Microsoft Progress Bar control 'ProgessBar'
-- Label 'ProgressLabel'
Copy the Code and paste in the VBA
Option Explicit
Private WithEvents SQLMerge As SQLMerge
Private WithEvents SQLMrgSnapshot As SQLSnapshot
Private Sub Form_Load()
Set SQLMerge = New SQLMerge
Set SQLMrgSnapshot = New SQLSnapshot
SQLMerge.Publisher = "MYSVR\MYINSTANCE"
SQLMerge.PublisherSecurityMode = NT_AUTHENTICATION
SQLMerge.PublisherDatabase = "TestDB"
SQLMerge.Publication = "TestPub"
SQLMrgSnapshot.Publisher = "MYSVR\MYINSTANCE"
SQLMrgSnapshot.PublisherDatabase = "TestDB"
SQLMrgSnapshot.Distributor = "MYSVR\MYINSTANCE"
SQLMrgSnapshot.Publication = "TestPub"
SQLMrgSnapshot.DistributorSecurityMode = NT_AUTHENTICATION
SQLMrgSnapshot.PublisherSecurityMode = NT_AUTHENTICATION
SQLMrgSnapshot.ReplicationType = MERGE
Exit Sub
End Sub
Private Sub cmdMergePub_Click()
On Error GoTo Failure
Dim replerr As SQLReplError
ProgressBar.Value = 0
ProgressLabel.Caption = "Starting Merge Replication."
DoEvents
'Initialize
SQLMerge.Initialize
'Run
SQLMerge.Run
'Terminate
SQLMerge.Terminate
'Reset objects
ProgressBar.Value = 0
Exit Sub
Failure:
For Each replerr In SQLMerge.ErrorRecords
MsgBox replerr.Description, vbCritical, "SQL Replication Sample Failure"
Next replerr
ProgressLabel.Caption = ""
ProgressBar.Value = 0
End Sub
Private Sub cmdSnapshot_Click()
On Error GoTo Failure
Dim replerr As SQLReplError
ProgressBar.Value = 0
ProgressLabel.Caption = "Starting Merge Snapshot Generation."
DoEvents
'Initialize
SQLMrgSnapshot.Initialize
'Run
SQLMrgSnapshot.Run
'Terminate
SQLMrgSnapshot.Terminate
'Reset objects
ProgressBar.Value = 0
Exit Sub
Failure:
For Each replerr In SQLMrgSnapshot.ErrorRecords
MsgBox replerr.Description, vbCritical, "SQL Replication Sample Failure"
Next replerr
ProgressLabel.Caption = ""
ProgressBar.Value = 0
End Sub
Private Sub PrintErrors(c As Object)
If Err.Number <> 0 Then
MsgBox Err.Description, vbCritical, "SQL Replication Sample Failure"
End If
End Sub
Private Function SQLMerge_Status(ByVal Message As String, ByVal Percent As Long) As STATUS_RETURN_CODE
'Update progress information
ProgressBar.Value = Percent
ProgressLabel.Caption = Message
'Allow other events
DoEvents
SQLMerge_Status = SUCCESS
End Function
Private Function SQLMrgSnapshot_Status(ByVal Message As String, ByVal Percent As Long) As STATUS_RETURN_CODE
'Update progress information
ProgressBar.Value = Percent
ProgressLabel.Caption = Message
'Allow other events
DoEvents
'Setting the return code to CANCEL will cause the control to cancel operation
SQLMrgSnapshot_Status = SUCCESS
End Function
15. Now Run only Snapshot by clicking cmdSnapshot button
Continues|||//////////////////////////////////////////////////////////////////////////////////
CLIENT COMPUTER
/////////////////////////////////////////////////////////////////////////////////
16. If success go to your next connected computer
Installed MS-Access 2003 and MSDE 2000
Computer name 'MYLAPTOP'
MSDE instance Name 'MYLAPTOP\MYINSTANCE'
user id 'sa'
passwor 'password'
17. Create an access project TestProjClient
18. Create a SQL Database 'TestDBClient'
Here you do not need to create tables
19. Add a Form TestReplicationClient
20. Create reference to objects SQLMerge and SQLReplError
21. Add
-- Commnad buttons 'cmdMergePub'
-- Microsoft Progress Bar control 'ProgessBar'
-- Label 'ProgressLabel'
22. Copy paste the following in the VBA code
'**********************************************
Option Explicit
Private WithEvents SQLMrgSnapshot As SQLSnapshot
Private Sub Form_Load()
Set SQLMerge = New SQLMerge
End Sub
Private Sub cmdMergePub_Click()
with SQLMerge
'------------------
'Set the publisher properties
.Publisher = "MYSVR\MYINSTANCE"
.PublisherSecurityMode = DB_AUTHENTICATION
.PublisherLogin = "sa"
.PublisherPassword = "password"
.PublisherDatabase = "TestDB"
.Publication = "TestPub"
'------------------
.PublisherAddress = "MYSVR"
.PublisherNetwork = DEFAULT_NETWORK
'------------------
'Set the distributor properties
' No need to set Distribution Server when both
' Publisher and subscriber are same Server
'------------------
'Set your local subscriber properties
.Subscriber = "MYLAPTOP\MYINSTANCE"
.SubscriberSecurityMode = DB_AUTHENTICATION
.SubscriberDatasourceType = SQL_SERVER
.SubscriberLogin = "sa"
.SubscriberPassword = "password"
.SubscriberDatabase = "TestDBReplica"
.SubscriptionType = ANONYMOUS
'------------------
'------------------
.Initialize
.Run
.Terminate
'------------------
End With
exit sub
ErrH:
Set SQLMerge = Nothing
MsgBox "Unexpected error occured during Merge Process" & _
vbCrLf & Err.Description, vbCritical, "Replication"
End Sub
Private Function SQLMerge_Status(ByVal Message As String, ByVal Percent As Long) As STATUS_RETURN_CODE
'Update progress information
ProgressBar.Value = Percent
ProgressLabel.Caption = Message
'Allow other events
DoEvents
SQLMerge_Status = SUCCESS
End Function
'**********************************************
23. Click cmdMerge
SUCCESS??????
if yes
24. Open the table and add one more record
25. now click cmdMerge again
26. Go to the 1st computer (server computer) and see whether newly added record in the laptop is in the TestProj
DONE
Pls reply if any doubts
Cheers
Jos|||Attached txtx file for scenario on which i solved the replication problem
All comments are corrections are appreciated
cheers
Jos
Server runs on MS-XP Professional
------------
I have a sample Access project 'ReplTest' which has only table with only 2 columns.
DatabaseName : ReplTestDB
Table Name : TestTable
MSDE Instance Name : SVR\MYINSTANCE
Now I would like to know how I can configure this database for merge replication
using SQL-DMO
Laptop runs on MS-XP Professional
------------
I have another access project which is running on computer 2 and connected to the
ReplicaTest database.
MSDE Instance Name : LPT\MYINSTANCE
My task is, when I am disconnected from server I would like to have a local copy of
the database to work with and then, when reconnected, need synchronization with the
server database and continue working from server database.
How to write this replication process from scratch using SQL-DMO objects in both Server computer
and Laptop computer.
I dont have enterprise manager in both computers since they use only MSDE
Can anyone help me?
Thanks in advance
JPCheck out "Publishers, Distributors, and Subscribers" from BOL (Ihope you have BOL).|||The problem with BOL is it is so extensive and the scenario of my situation is not described. I could do replication in the same machine as distributor, publisher and suscriber, but what if the subscriber is another machine as in my case ?|||I found this within these forums:
The sample code you were after is for creating heterogenous publication. It does not work with SQL publications. Try to model after the following sample.
Sub CreateMergePub() Dim osvr As New SQLDMO.SQLServer Dim szLastErrorText As String
Dim lLastErrorNumber As Long
Dim szServerName As String
Dim oReplicationDatabase As SQLDMO.ReplicationDatabase
Dim oDistPublisher As New DistributionPublisher
Dim oMergePublication As New MergePublication
Dim oMergeArticle1 As New SQLDMO.MergeArticle
On Error GoTo ErrorHandler
szServerName = "your servername"
' To use NT integrated security ' osvr.LoginSecure = True
osvr.Connect szServerName
' Enable pubs database for publishing ' Set oReplicationDatabase =
osvr.Replication.ReplicationDatabases("pubs")
'oReplicationDatabase.EnableTransPublishing = True
' Set MergePublication properties, name, snapshot method, trans '
oMergePublication.Name = "Pub1" oMergePublication.SnapshotMethod =
SQLDMOInitSync_BCPNative
' Set non-default properties, i.e. if you do not set the following properties, '
default value will be used ' 'oMergePublication.PublicationAttributes =
SQLDMOPubAttrib_AllowPull Or SQLDMOPubAttrib_AllowAnonymous
'oMergePublication.RetentionPeriod = 64
'oMergePublication.SnapshotSchedule.FrequencyType = SQLDMOFreq_Daily
'oMergePublication.SnapshotSchedule.FrequencyInter val = 1
'oMergePublication.SnapshotSchedule.ActiveStartTim eOfDay = 233000
'oMergePublication.SnapshotSchedule.ActiveEndTimeO fDay = 235959
'oMergePublication.SnapshotSchedule.ActiveStartDat e = 0
'oMergePublication.SnapshotSchedule.ActiveEndDate = 19981205
oReplicationDatabase.MergePublications.Add oMergePublication
' Add article ' oMergeArticle1.Name = "Article1" oMergeArticle1.SourceObjectName
= "jobs" oMergeArticle1.SourceObjectOwner = "dbo"
'Add the article to the publication ' oMergePublication.MergeArticles.Add
oMergeArticle1
' Disconnect from SQL Server szServerName ' osvr.DisConnect Set osvr =
Nothing End
ErrorHandler:
szLastErrorText = Err.Description lLastErrorNumber = Err.Number
MsgBox szLastErrorText, vbOKOnly, "Error " + Trim$(Str$(Err.Number))
End Sub
--
Pung Xu, Microsoft This posting is provided "AS IS" with no warranties, and confers
no rights. Please do not send email directly to this alias. This alias is for
newsgroup purposes only.
"matt" <matt_e_davis@.hotmail.com> wrote in message
news:d0db2ad5.0205221240.7c427ed7@.posting.google.c om...
> I am having trouble creating a merge publication with SQL-DMO. I modeled my
> solution after the following code:
> --================================================ Sub CreatePublication() '
> Connect to SQL Server Distributor Dim oSqlServer As New SQLDMO.SQLServer
> oSqlServer.Connect "", "sa", "" On Error GoTo ErrorHandler
>
> Dim oSamplePublisher As DistributionPublisher Set oSamplePublisher =
>
oSqlServer.Replication.Distributor.DistributionPub lishers("SAMPLEPUBLISHER")
>
> ' Create Sample Publication Dim oSamplePublication As New
> DistributionPublication oSamplePublication.Name = "SamplePublication"
> oSamplePublication.PublicationDB = "SampleDatabase"
> oSamplePublication.PublicationType = SQLDMOPublication_Transactional
> oSamplePublication.VendorName = "Sample Vendor"
> oSamplePublication.LogReaderAgent = "SampleLogReaderAgent"
> oSamplePublication.SnapshotAgent = "SampleSnapShotAgent"
> oSamplePublication.Description = "Sample Publication Definition"
> oSamplePublication.PublicationAttributes = SQLDMOPubAttrib_AllowPush
>
> ' Add the Publication oSamplePublisher.DistributionPublications.Add
> oSamplePublication
>
> ' Create Sample Articles Dim oSampleArticle1 As New DistributionArticle
> oSampleArticle1.Name = "SampleArticle1" oSampleArticle1.Description = "Sampe
> Article1 Definition" oSampleArticle1.SourceObjectName = "SampleTable1"
>
> Dim oSampleArticle2 As New DistributionArticle oSampleArticle2.Name =
> "SampleArticle2" oSampleArticle2.Description = "Sample Article2 Definition"
> oSampleArticle2.SourceObjectName = "SampleTable2"
>
> ' Add the Articles to the Publication
> oSamplePublication.DistributionArticles.Add oSampleArticle2
> oSamplePublication.DistributionArticles.Add oSampleArticle1
>
> oSqlServer.Close Exit Sub ErrorHandler: PrintErrors oSqlServer Exit Sub End Sub
>
> --================================================ This does create a publication
> but when you right click (via Enterprise Mgr.) Replication Monitor > PublisherName
> > SamplePublication and select Properties an error occurs. The error states that
> the publication is not in the TransPublication collection. The same error occurs
> when you create a merge publication. Except the error is that the publication does
> not exist in the MergePublications collection.
>
> How can I add the publication to the MergePublications collection?? Does anyone
> have any sample code for this?
>
> I am new at SQL-DMO and VB so I am struggling.
>
> Thanks in advance for any help you can give!!
>
> Matt|||Hi
Thanks for the reply.
I solved my problem...
I think the following posting will be helphul to someone ...
The text is too long , so posted in 3 parts
Scenario.. PART 1 SERVER COMPUTER
Desktop - Installed Access 2003 & MSDE 2000
Machine Name - MYSVR
MSDE instance Name - 'MYSVR\MYINSTANCE'
User id - 'sa' Password - 'password'
1. Create an Access Project 'TestProj.adp'
2. Create a SQL Database 'TestDB'
3. Create Table Name 'TestTable'
Columns - TestID INT IDENTITY(1,1) PRIMARY KEY
- TestName NVARCHAR(50)
4. Add a record to the table
5. Create a text TestRep01.txt and paste the following
Note that machinename should be replaced with your machine name
/*Script to be copied */
use master
GO
exec sp_adddistributor @.distributor = @.@.servername, @.password = N''
GO
-- Updating the agent profile defaults
sp_MSupdate_agenttype_default @.profile_id = 1
GO
sp_MSupdate_agenttype_default @.profile_id = 2
GO
sp_MSupdate_agenttype_default @.profile_id = 4
GO
sp_MSupdate_agenttype_default @.profile_id = 6
GO
sp_MSupdate_agenttype_default @.profile_id = 11
GO
-- Adding the distribution database
exec sp_adddistributiondb @.database = N'distribution',
@.data_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL$MYINSTANCE\Data',
@.data_file = N'distribution.MDF',
@.data_file_size = 3,
@.log_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL$MYINSTANCE\Data',
@.log_file = N'distribution.LDF',
@.log_file_size = 3,
@.min_distretention = 0,
@.max_distretention = 72,
@.history_retention = 48,
@.security_mode = 1
GO
-- Adding the distribution publisher
exec sp_adddistpublisher @.publisher = @.@.servername,
@.distribution_db = N'distribution',
@.security_mode = 1,
@.working_directory = N'\\MYSVR\C$\Program Files\Microsoft SQL Server\MSSQL$MYSINSTANCE\ReplData',
@.trusted = N'false',
@.thirdparty_flag = 0
GO
/*script ends*/
5. Create a folder 'ReplData' under 'C:\Program Files\Microsoft SQL
Server\MSSQL$MYINSTANCE' if it is not there.
6. Make sure SQLServer agent is running, if not, start that now.
7. Open Command prompt and run osql.exe with following parameters
as in scenario
prompt:> osql -S MYSVR\MYINSTANCE -U sa -P password -i TestRep01.txt
-o ResultTestRep01.txt -b
8. If so far so good , copy the follwoing script to another text file TestRep02.txt
/* Script starts*/
-- Enabling the replication database
use master
GO
exec sp_replicationdboption @.dbname = N'TestDB',
@.optname = N'merge publish',
@.value = N'true'
GO
use [TestDB]
GO
-- Adding the merge publication
exec sp_addmergepublication @.publication = N'TestPub',
@.description = N'Merge publ of TestDB.',
@.retention = 14,
@.sync_mode = N'character',
@.allow_push = N'true',
@.allow_pull = N'true',
@.allow_anonymous = N'true',
@.enabled_for_internet = N'false',
@.centralized_conflicts = N'true',
@.dynamic_filters = N'false',
@.snapshot_in_defaultfolder = N'true',
@.compress_snapshot = N'false',
@.ftp_port = 21,
@.ftp_login = N'anonymous',
@.conflict_retention = 14,
@.keep_partition_changes = N'false',
@.allow_subscription_copy = N'false',
@.allow_synctoalternate = N'false',
@.add_to_active_directory = N'false',
@.max_concurrent_merge = 0,
@.max_concurrent_dynamic_snapshots = 0
exec sp_addpublication_snapshot @.publication = 'TestPub',
@.frequency_type = 4,
@.frequency_interval = 1,
@.frequency_relative_interval = 1,
@.frequency_recurrence_factor = 0,
@.frequency_subday = 1,
@.frequency_subday_interval = 5,
@.active_start_date = 0,
@.active_end_date = 0,
@.active_start_time_of_day = 500,
@.active_end_time_of_day = 235959,
GO
exec sp_grant_publication_access @.publication = N'TestPub',
@.login = N'sa'
GO
-- Adding the merge articles
exec sp_addmergearticle @.publication = N'TestPub',
@.article = N'TestTable',
@.source_owner = N'dbo',
@.source_object = N'TestTable',
@.type = N'table',
@.description = null,
@.column_tracking = N'true',
@.pre_creation_cmd = N'drop',
@.creation_script = null,
@.schema_option = 0x000000000000FFF1,
@.article_resolver = null,
@.subset_filterclause = null,
@.vertical_partition = N'false',
@.destination_owner = N'dbo',
@.auto_identity_range = N'false',
@.verify_resolver_signature = 0,
@.allow_interactive_resolver = N'false',
@.fast_multicol_updateproc = N'true',
@.check_permissions = 0
GO
/*Script Ends */
9. Open Command prompt and run osql.exe with following parameters
as in scenario
prompt:> osql -S MYSVR\MYINSTANCE -U sa -P password -i TestRep02.txt
-o ResultTestRep02.txt -b
10. If this works fine (check the ResultTestRep02.txt for errors) the proceed
Any doubts and corrections are welcome
Cheers
Jos|||Part 2 of hte previous positing
In server computer , TestProj application
11. Create a form 'TestReplication'
13. Create reference to objects SQLMerge, SQLDSnapshot
and SQLReplError
14. Add
-- Commnad buttons 'cmdSnapShot', 'cmdMergePub'
-- Microsoft Progress Bar control 'ProgessBar'
-- Label 'ProgressLabel'
Copy the Code and paste in the VBA
Option Explicit
Private WithEvents SQLMerge As SQLMerge
Private WithEvents SQLMrgSnapshot As SQLSnapshot
Private Sub Form_Load()
Set SQLMerge = New SQLMerge
Set SQLMrgSnapshot = New SQLSnapshot
SQLMerge.Publisher = "MYSVR\MYINSTANCE"
SQLMerge.PublisherSecurityMode = NT_AUTHENTICATION
SQLMerge.PublisherDatabase = "TestDB"
SQLMerge.Publication = "TestPub"
SQLMrgSnapshot.Publisher = "MYSVR\MYINSTANCE"
SQLMrgSnapshot.PublisherDatabase = "TestDB"
SQLMrgSnapshot.Distributor = "MYSVR\MYINSTANCE"
SQLMrgSnapshot.Publication = "TestPub"
SQLMrgSnapshot.DistributorSecurityMode = NT_AUTHENTICATION
SQLMrgSnapshot.PublisherSecurityMode = NT_AUTHENTICATION
SQLMrgSnapshot.ReplicationType = MERGE
Exit Sub
End Sub
Private Sub cmdMergePub_Click()
On Error GoTo Failure
Dim replerr As SQLReplError
ProgressBar.Value = 0
ProgressLabel.Caption = "Starting Merge Replication."
DoEvents
'Initialize
SQLMerge.Initialize
'Run
SQLMerge.Run
'Terminate
SQLMerge.Terminate
'Reset objects
ProgressBar.Value = 0
Exit Sub
Failure:
For Each replerr In SQLMerge.ErrorRecords
MsgBox replerr.Description, vbCritical, "SQL Replication Sample Failure"
Next replerr
ProgressLabel.Caption = ""
ProgressBar.Value = 0
End Sub
Private Sub cmdSnapshot_Click()
On Error GoTo Failure
Dim replerr As SQLReplError
ProgressBar.Value = 0
ProgressLabel.Caption = "Starting Merge Snapshot Generation."
DoEvents
'Initialize
SQLMrgSnapshot.Initialize
'Run
SQLMrgSnapshot.Run
'Terminate
SQLMrgSnapshot.Terminate
'Reset objects
ProgressBar.Value = 0
Exit Sub
Failure:
For Each replerr In SQLMrgSnapshot.ErrorRecords
MsgBox replerr.Description, vbCritical, "SQL Replication Sample Failure"
Next replerr
ProgressLabel.Caption = ""
ProgressBar.Value = 0
End Sub
Private Sub PrintErrors(c As Object)
If Err.Number <> 0 Then
MsgBox Err.Description, vbCritical, "SQL Replication Sample Failure"
End If
End Sub
Private Function SQLMerge_Status(ByVal Message As String, ByVal Percent As Long) As STATUS_RETURN_CODE
'Update progress information
ProgressBar.Value = Percent
ProgressLabel.Caption = Message
'Allow other events
DoEvents
SQLMerge_Status = SUCCESS
End Function
Private Function SQLMrgSnapshot_Status(ByVal Message As String, ByVal Percent As Long) As STATUS_RETURN_CODE
'Update progress information
ProgressBar.Value = Percent
ProgressLabel.Caption = Message
'Allow other events
DoEvents
'Setting the return code to CANCEL will cause the control to cancel operation
SQLMrgSnapshot_Status = SUCCESS
End Function
15. Now Run only Snapshot by clicking cmdSnapshot button
Continues|||//////////////////////////////////////////////////////////////////////////////////
CLIENT COMPUTER
/////////////////////////////////////////////////////////////////////////////////
16. If success go to your next connected computer
Installed MS-Access 2003 and MSDE 2000
Computer name 'MYLAPTOP'
MSDE instance Name 'MYLAPTOP\MYINSTANCE'
user id 'sa'
passwor 'password'
17. Create an access project TestProjClient
18. Create a SQL Database 'TestDBClient'
Here you do not need to create tables
19. Add a Form TestReplicationClient
20. Create reference to objects SQLMerge and SQLReplError
21. Add
-- Commnad buttons 'cmdMergePub'
-- Microsoft Progress Bar control 'ProgessBar'
-- Label 'ProgressLabel'
22. Copy paste the following in the VBA code
'**********************************************
Option Explicit
Private WithEvents SQLMrgSnapshot As SQLSnapshot
Private Sub Form_Load()
Set SQLMerge = New SQLMerge
End Sub
Private Sub cmdMergePub_Click()
with SQLMerge
'------------------
'Set the publisher properties
.Publisher = "MYSVR\MYINSTANCE"
.PublisherSecurityMode = DB_AUTHENTICATION
.PublisherLogin = "sa"
.PublisherPassword = "password"
.PublisherDatabase = "TestDB"
.Publication = "TestPub"
'------------------
.PublisherAddress = "MYSVR"
.PublisherNetwork = DEFAULT_NETWORK
'------------------
'Set the distributor properties
' No need to set Distribution Server when both
' Publisher and subscriber are same Server
'------------------
'Set your local subscriber properties
.Subscriber = "MYLAPTOP\MYINSTANCE"
.SubscriberSecurityMode = DB_AUTHENTICATION
.SubscriberDatasourceType = SQL_SERVER
.SubscriberLogin = "sa"
.SubscriberPassword = "password"
.SubscriberDatabase = "TestDBReplica"
.SubscriptionType = ANONYMOUS
'------------------
'------------------
.Initialize
.Run
.Terminate
'------------------
End With
exit sub
ErrH:
Set SQLMerge = Nothing
MsgBox "Unexpected error occured during Merge Process" & _
vbCrLf & Err.Description, vbCritical, "Replication"
End Sub
Private Function SQLMerge_Status(ByVal Message As String, ByVal Percent As Long) As STATUS_RETURN_CODE
'Update progress information
ProgressBar.Value = Percent
ProgressLabel.Caption = Message
'Allow other events
DoEvents
SQLMerge_Status = SUCCESS
End Function
'**********************************************
23. Click cmdMerge
SUCCESS??????
if yes
24. Open the table and add one more record
25. now click cmdMerge again
26. Go to the 1st computer (server computer) and see whether newly added record in the laptop is in the TestProj
DONE
Pls reply if any doubts
Cheers
Jos|||Attached txtx file for scenario on which i solved the replication problem
All comments are corrections are appreciated
cheers
Jos
Merge replication UNIQUEIDENTIFIER Column?
Hi all,
I am using SQL 2005 sp1 to setup Merge replication for PDA access.
i created my Primary Key column a GUID using NEWID() as the default value.
When the snapshot was created it still went and added anothe GUID to all my
tables called rowguid.
i thought it was suposed to use the existing Unique GUID column before
creating a new one?
thanks for you advice. i hope i have the corect groups here?
inkquote:
If a published table does not have a uniqueidentifier column with the
ROWGUIDCOL property and a unique index, replication adds one
So, check for the rowguidcol property...
MC
"iKiLL" <iKill@.NotMyEmail.com> wrote in message
news:uS1$gVbWHHA.4796@.TK2MSFTNGP05.phx.gbl...
> Hi all,
> I am using SQL 2005 sp1 to setup Merge replication for PDA access.
> i created my Primary Key column a GUID using NEWID() as the default value.
> When the snapshot was created it still went and added anothe GUID to all
> my tables called rowguid.
> i thought it was suposed to use the existing Unique GUID column before
> creating a new one?
> thanks for you advice. i hope i have the corect groups here?
> ink
>|||Sorry my point was that i had created one and SQL2005 still created its own.
Now from what i can tell i think i have figgerd it out.
The behaviour i was expecting was how SQL 2000 handled the row GUID column
for snapshots.
i am using SQL 2005.
it seems that there is in fact a property of the column called RowGuid that
must be set to Yes before creating the first snapshot.
Then SQL2005 will use that column instead of creating it's own. Just setting
the data type and making it the primary key is not enough.
Thanks for your input Marko.
"MC" <marko.culoNOSPAM@.gmail.com> wrote in message
news:eruu6p$pcf$1@.ss408.t-com.hr...
> quote:
> If a published table does not have a uniqueidentifier column with the
> ROWGUIDCOL property and a unique index, replication adds one
>
> So, check for the rowguidcol property...
>
> MC
>
> "iKiLL" <iKill@.NotMyEmail.com> wrote in message
> news:uS1$gVbWHHA.4796@.TK2MSFTNGP05.phx.gbl...
>|||Yes, you need RowGuid property. Point is, you can have any number of
uniqueidentifiers in a table, but one of them needs to have this property
set. Since SQL Server doesnt want to guess which one would you like to have
as the 'main' GUID in a table, it adds another with rowguid property set.
Offcourse, if you allready have one it doesnt need to add it.
MC
"iKiLL" <iKill@.NotMyEmail.com> wrote in message
news:e2jo3%23bWHHA.600@.TK2MSFTNGP05.phx.gbl...
> Sorry my point was that i had created one and SQL2005 still created its
> own.
> Now from what i can tell i think i have figgerd it out.
> The behaviour i was expecting was how SQL 2000 handled the row GUID column
> for snapshots.
> i am using SQL 2005.
> it seems that there is in fact a property of the column called RowGuid
> that must be set to Yes before creating the first snapshot.
> Then SQL2005 will use that column instead of creating it's own. Just
> setting the data type and making it the primary key is not enough.
> Thanks for your input Marko.
>
>
>
> "MC" <marko.culoNOSPAM@.gmail.com> wrote in message
> news:eruu6p$pcf$1@.ss408.t-com.hr...
>
I am using SQL 2005 sp1 to setup Merge replication for PDA access.
i created my Primary Key column a GUID using NEWID() as the default value.
When the snapshot was created it still went and added anothe GUID to all my
tables called rowguid.
i thought it was suposed to use the existing Unique GUID column before
creating a new one?
thanks for you advice. i hope i have the corect groups here?
inkquote:
If a published table does not have a uniqueidentifier column with the
ROWGUIDCOL property and a unique index, replication adds one
So, check for the rowguidcol property...
MC
"iKiLL" <iKill@.NotMyEmail.com> wrote in message
news:uS1$gVbWHHA.4796@.TK2MSFTNGP05.phx.gbl...
> Hi all,
> I am using SQL 2005 sp1 to setup Merge replication for PDA access.
> i created my Primary Key column a GUID using NEWID() as the default value.
> When the snapshot was created it still went and added anothe GUID to all
> my tables called rowguid.
> i thought it was suposed to use the existing Unique GUID column before
> creating a new one?
> thanks for you advice. i hope i have the corect groups here?
> ink
>|||Sorry my point was that i had created one and SQL2005 still created its own.
Now from what i can tell i think i have figgerd it out.
The behaviour i was expecting was how SQL 2000 handled the row GUID column
for snapshots.
i am using SQL 2005.
it seems that there is in fact a property of the column called RowGuid that
must be set to Yes before creating the first snapshot.
Then SQL2005 will use that column instead of creating it's own. Just setting
the data type and making it the primary key is not enough.
Thanks for your input Marko.
"MC" <marko.culoNOSPAM@.gmail.com> wrote in message
news:eruu6p$pcf$1@.ss408.t-com.hr...
> quote:
> If a published table does not have a uniqueidentifier column with the
> ROWGUIDCOL property and a unique index, replication adds one
>
> So, check for the rowguidcol property...
>
> MC
>
> "iKiLL" <iKill@.NotMyEmail.com> wrote in message
> news:uS1$gVbWHHA.4796@.TK2MSFTNGP05.phx.gbl...
>|||Yes, you need RowGuid property. Point is, you can have any number of
uniqueidentifiers in a table, but one of them needs to have this property
set. Since SQL Server doesnt want to guess which one would you like to have
as the 'main' GUID in a table, it adds another with rowguid property set.
Offcourse, if you allready have one it doesnt need to add it.
MC
"iKiLL" <iKill@.NotMyEmail.com> wrote in message
news:e2jo3%23bWHHA.600@.TK2MSFTNGP05.phx.gbl...
> Sorry my point was that i had created one and SQL2005 still created its
> own.
> Now from what i can tell i think i have figgerd it out.
> The behaviour i was expecting was how SQL 2000 handled the row GUID column
> for snapshots.
> i am using SQL 2005.
> it seems that there is in fact a property of the column called RowGuid
> that must be set to Yes before creating the first snapshot.
> Then SQL2005 will use that column instead of creating it's own. Just
> setting the data type and making it the primary key is not enough.
> Thanks for your input Marko.
>
>
>
> "MC" <marko.culoNOSPAM@.gmail.com> wrote in message
> news:eruu6p$pcf$1@.ss408.t-com.hr...
>
Merge replication UNIQUEIDENTIFIER Column?
Hi all,
I am using SQL 2005 sp1 to setup Merge replication for PDA access.
i created my Primary Key column a GUID using NEWID() as the default value.
When the snapshot was created it still went and added anothe GUID to all my
tables called rowguid.
i thought it was suposed to use the existing Unique GUID column before
creating a new one?
thanks for you advice. i hope i have the corect groups here?
ink
Sorry my point was that i had created one and SQL2005 still created its own.
Now from what i can tell i think i have figgerd it out.
The behaviour i was expecting was how SQL 2000 handled the row GUID column
for snapshots.
i am using SQL 2005.
it seems that there is in fact a property of the column called RowGuid that
must be set to Yes before creating the first snapshot.
Then SQL2005 will use that column instead of creating it's own. Just setting
the data type and making it the primary key is not enough.
Thanks for your input Marko.
"MC" <marko.culoNOSPAM@.gmail.com> wrote in message
news:eruu6p$pcf$1@.ss408.t-com.hr...
> quote:
> If a published table does not have a uniqueidentifier column with the
> ROWGUIDCOL property and a unique index, replication adds one
>
> So, check for the rowguidcol property...
>
> MC
>
> "iKiLL" <iKill@.NotMyEmail.com> wrote in message
> news:uS1$gVbWHHA.4796@.TK2MSFTNGP05.phx.gbl...
>
I am using SQL 2005 sp1 to setup Merge replication for PDA access.
i created my Primary Key column a GUID using NEWID() as the default value.
When the snapshot was created it still went and added anothe GUID to all my
tables called rowguid.
i thought it was suposed to use the existing Unique GUID column before
creating a new one?
thanks for you advice. i hope i have the corect groups here?
ink
Sorry my point was that i had created one and SQL2005 still created its own.
Now from what i can tell i think i have figgerd it out.
The behaviour i was expecting was how SQL 2000 handled the row GUID column
for snapshots.
i am using SQL 2005.
it seems that there is in fact a property of the column called RowGuid that
must be set to Yes before creating the first snapshot.
Then SQL2005 will use that column instead of creating it's own. Just setting
the data type and making it the primary key is not enough.
Thanks for your input Marko.
"MC" <marko.culoNOSPAM@.gmail.com> wrote in message
news:eruu6p$pcf$1@.ss408.t-com.hr...
> quote:
> If a published table does not have a uniqueidentifier column with the
> ROWGUIDCOL property and a unique index, replication adds one
>
> So, check for the rowguidcol property...
>
> MC
>
> "iKiLL" <iKill@.NotMyEmail.com> wrote in message
> news:uS1$gVbWHHA.4796@.TK2MSFTNGP05.phx.gbl...
>
Monday, February 20, 2012
merge replication and identity field problems.
Hi there,
I have converted MS access database to sql 2000 database and front end
is in adp. i our db all of our table contain identity field as a
primary key and forgine key. I am using merge replication with the
publisher and distributer in the same server where original db is and
may have many subscriber (pull subscription) using msde who will
synchronize on demand. i change all the autonumber field in access as
identity field (not for replication) and relationship between table
(not for replication) is clear off. I am very much worried now if i
just publish the database and subscript is whether i am going to have
conflict with identity field which are primary key or its going to
workin fine. Actually i donot know how sql handel those identity field
with so many copy of subscriber. please give me some information how
should i proceed.
I have table call Job, jobcarrier, jobshots, joblogs, jobpersonnel,
etc where primary key is identity field and all the table contain
forgain key from job table. and our replicated database using the same
front end as we are using. please give me inf. how it work and what i
should do.
Thansk very much
Indra.
Indra,
you have a choice to either let SQL Server manage the identity ranges or do
it manually. If you select to synchronize your tables on initialization,
different seeds will be selected for each subscriber, and the size of the
allocated range is determined by yourself (on clicking the article
properties elipsis button a configuration form appears). This is probably
the easiest method. If you want to do it manually you might be interested in
Michael Hotek's algorithms to ensure no overlap
(http://www.mssqlserver.com/replicati...h_identity.asp).
HTH,
Paul Ibison
|||Hi Indra,
It would be easier for you to let SQL handle the identity values. Also I hope you are taking of specifying "Not for replication" for all your relationships.
Regards,
Karthik.
|||HI Paul,
I check all the information, BOL, artical, knowledgebase etc and try
to publishe merge replication with pull subscribtion and its not
working as the way it should work.
1. All the identity field has been assign as not for replication
2. all the relationship has been clear off the option (enforce
relationship for replication).
3. I could run the subcription and synchornzed the data.
4. i inserted in subscriber and in the publisher database it both give
the same identity field.
5. When i synchronized, it doesnot display any error message but the
data inserted at the subscriber has been deleted and data inserted at
the publisher has been trasfer to subscriber (the conflict with pk
data in subscriber has been deleted.)
6. when i check the pulication property the option for automatic
identity assign and maintain is not highlighted.
Could you please help me where am i making wrong and how i can do
this.
I will appreciate your help.
Thanks.
Indra.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message news:<##LLZhNSEHA.3988@.tk2msftngp13.phx.gbl>...
> Indra,
> you have a choice to either let SQL Server manage the identity ranges or do
> it manually. If you select to synchronize your tables on initialization,
> different seeds will be selected for each subscriber, and the size of the
> allocated range is determined by yourself (on clicking the article
> properties elipsis button a configuration form appears). This is probably
> the easiest method. If you want to do it manually you might be interested in
> Michael Hotek's algorithms to ensure no overlap
> (http://www.mssqlserver.com/replicati...h_identity.asp).
> HTH,
> Paul Ibison
|||Indra,
automatic range management is not enabled by default. Using:
exec sp_MShelp_identity_property @.tablename = N'TestIdent', @.ownername =
N'dbo'
will let you know if it is enabled. To get SQL Server to manage the range
you'll need to put a check in the box on the article properties, Identity
range tab. As far as I know, this isn't possible to do retrospectively, so
you'll need to recreate the publication.
HTH,
Paul Ibison
I have converted MS access database to sql 2000 database and front end
is in adp. i our db all of our table contain identity field as a
primary key and forgine key. I am using merge replication with the
publisher and distributer in the same server where original db is and
may have many subscriber (pull subscription) using msde who will
synchronize on demand. i change all the autonumber field in access as
identity field (not for replication) and relationship between table
(not for replication) is clear off. I am very much worried now if i
just publish the database and subscript is whether i am going to have
conflict with identity field which are primary key or its going to
workin fine. Actually i donot know how sql handel those identity field
with so many copy of subscriber. please give me some information how
should i proceed.
I have table call Job, jobcarrier, jobshots, joblogs, jobpersonnel,
etc where primary key is identity field and all the table contain
forgain key from job table. and our replicated database using the same
front end as we are using. please give me inf. how it work and what i
should do.
Thansk very much
Indra.
Indra,
you have a choice to either let SQL Server manage the identity ranges or do
it manually. If you select to synchronize your tables on initialization,
different seeds will be selected for each subscriber, and the size of the
allocated range is determined by yourself (on clicking the article
properties elipsis button a configuration form appears). This is probably
the easiest method. If you want to do it manually you might be interested in
Michael Hotek's algorithms to ensure no overlap
(http://www.mssqlserver.com/replicati...h_identity.asp).
HTH,
Paul Ibison
|||Hi Indra,
It would be easier for you to let SQL handle the identity values. Also I hope you are taking of specifying "Not for replication" for all your relationships.
Regards,
Karthik.
|||HI Paul,
I check all the information, BOL, artical, knowledgebase etc and try
to publishe merge replication with pull subscribtion and its not
working as the way it should work.
1. All the identity field has been assign as not for replication
2. all the relationship has been clear off the option (enforce
relationship for replication).
3. I could run the subcription and synchornzed the data.
4. i inserted in subscriber and in the publisher database it both give
the same identity field.
5. When i synchronized, it doesnot display any error message but the
data inserted at the subscriber has been deleted and data inserted at
the publisher has been trasfer to subscriber (the conflict with pk
data in subscriber has been deleted.)
6. when i check the pulication property the option for automatic
identity assign and maintain is not highlighted.
Could you please help me where am i making wrong and how i can do
this.
I will appreciate your help.
Thanks.
Indra.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message news:<##LLZhNSEHA.3988@.tk2msftngp13.phx.gbl>...
> Indra,
> you have a choice to either let SQL Server manage the identity ranges or do
> it manually. If you select to synchronize your tables on initialization,
> different seeds will be selected for each subscriber, and the size of the
> allocated range is determined by yourself (on clicking the article
> properties elipsis button a configuration form appears). This is probably
> the easiest method. If you want to do it manually you might be interested in
> Michael Hotek's algorithms to ensure no overlap
> (http://www.mssqlserver.com/replicati...h_identity.asp).
> HTH,
> Paul Ibison
|||Indra,
automatic range management is not enabled by default. Using:
exec sp_MShelp_identity_property @.tablename = N'TestIdent', @.ownername =
N'dbo'
will let you know if it is enabled. To get SQL Server to manage the range
you'll need to put a check in the box on the article properties, Identity
range tab. As far as I know, this isn't possible to do retrospectively, so
you'll need to recreate the publication.
HTH,
Paul Ibison
Subscribe to:
Posts (Atom)