sql server replication: Tejas,
I think this is what you need -
http://support.microsoft.com/default.aspx?scid=kb;en-us;828637&Product=sql2k
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Tejas,
if you want to avoid automatic range amangement then I'd partition the
ranges yourself. Make sure there's no constraints on the tables and make the
identity columns never overlap - either by reseeding the subscriber
appropriately or by 'algorithmic' means eg odd numbers on the publisher and
evens on the subscriber. This assumes you only have 1 subscriber. If you
have more I can help with other 'algorithms'.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

as i have mentioned in the previous posts, I've 2 publications, one trans and
one merge. If i don't have the constraints on the subscriber side...

When I change something on the subscriber side in a merge, the change will
go to the publisher. But, what does SQL do when an FK constraint is violated

Hey again. Yes, it did help a lot and that's exactly what my question was
pointing to.

Would you happen to know how to do the automatic identity range on a
subscriber, not through a publication?

I know it's in the table properties but I can't do it from there.
I already have the table structure and everything at the subscriber side. I
manually created the identity ranges at the publisher and subscriber. but the
thing is they dont have the property of 'increase the range if the capacity
is at 80%' I hope u understand what i mean.
Please let me know if it is possible to assign another range automatically
when the publisher has consumed 80% of its range. I want to do it without

Thank you very much,Paul. Finally, the client has agreed to the way I wanted
to set it up. I've migrated all the dependent tables into one merge
publication and i set up auto identity and everything and it looks fine now.

The problem had occurred with a simple 1 way transactional replication. I
created the publication with alternate snapshot location to
\\abc\sharename\foldername.

even the subscription is pointed to get its stuff from this location.

Then i started the snapshot agent. I created the snapshot fine.
But the stupid distribution agent is looking at the
\\abc\sharename\MergeFoldername. I dont know why it's doing this. I looked at
all the MS tables for replication and they show that the subscription is
pointing to
\\abc\sharename\foldername. I don't undertsnad why the distribution agent
wants to look at the wrong folder... Anyways, i've deleted the subscription
and recreated it to see what it does. will let you know.

Hey. I ran the snapshot agent again. And it runs fine. But the distribution
agent tries to look at a wrong folder to apply the snapshot which is why i'm
getting errors. I checked the MSsubscription_properties table and it shows
it's pointing to the right folder. What else is there to check to see if it's
fine or not? the only thing I have changed in the publication properties is
the CONCURRENCY checkbox under the Snapshot Tab. The one to "do not lock
tables..." I'm getting frustrated now because I have checked the publication
properties to make sure, it's pointing to the right place and I've made sure
that the subscription is pointing to the right path. Please advice what can
be done in this case. The publication in question is a transactional 1 way

Yes, I've been using the alternative snapshot folder. It grabs the MERGE
publication snapshot folder instead of the one I'm trying to point it to.
Everything tells me that I
am pointing to the folder i should. I dont know why it's doing it. Now, I've
deleted the subscription, deleted the publication. I recreated the
publication with all the tables and recreated the subscription. I'm running
the snapshot agent right now which will take about half an hour and then will
run the distribution agent. I'll update the post tomorrow but is there
anything else i should check to see where it's grabbing the wrong snapshot
folder from??????

Not seen this issue beforte. sp_browsesnapshotfolder and sp_helpdistributor
can be used to see what is set on the publisher/distributor. Are you using
an alternative snapshot location for the subscription or are you using the
default?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Tejas,
your script seems ok. I have added a similar one I just set up which runs ok
albeit using anonymous subscribers, but that shouldn't make any difference.
I don't see any big differences in our scripts but if you do please let me
know. Anyway, please can you try running the distribution agent and have it
log to a text file so we can be sure this error is generated by the
distribution agent and not another subscription
(http://support.microsoft.com/?id=312292). Also, preferably run the agent
from the commandline to avoid any other issues.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

-- Adding the distribution database

exec sp_adddistributiondb @database = N'distribution', @data_folder =
N'C:\Program Files\Microsoft SQL Server\MSSQL\Data', @data_file =
N'distribution.MDF', @data_file_size = 3, @log_folder = N'C:\Program
Files\Microsoft SQL Server\MSSQL\Data', @log_file = N'distribution.LDF',
@log_file_size = 0, @min_distretention = 0, @max_distretention = 72,
@history_retention = 48, @security_mode = 1

GO



-- Adding the distribution publisher

exec sp_adddistpublisher @publisher = N'LON36', @distribution_db =
N'distribution', @security_mode = 1, @working_directory =
N'\\LON36\C$\Program Files\Microsoft SQL Server\MSSQL\ReplData', @trusted =
N'false', @thirdparty_flag = 0

GO



-- Adding the transactional publication

exec sp_addpublication @publication = N'NorthwindRegions', @restricted =
N'false', @sync_method = N'native', @repl_freq = N'continuous', @description
= N'Transactional publication of Northwind database from Publisher LON36.',
@status = N'active', @allow_push = N'true', @allow_pull = N'true',
@allow_anonymous = N'true', @enabled_for_internet = N'false',
@independent_agent = N'true', @immediate_sync = N'true', @allow_sync_tran =
N'false', @autogen_sync_procs = N'false', @retention = 336,
@allow_queued_tran = N'false', @snapshot_in_defaultfolder = N'true',
@compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anonymous',
@allow_dts = N'false', @allow_subscription_copy = N'false',
@add_to_active_directory = N'false', @logreader_job_name =
N'LON36-Northwind-7'

exec sp_addpublication_snapshot @publication =
N'NorthwindRegions',@frequency_type = 4, @frequency_interval = 1,
@frequency_relative_interval = 1, @frequency_recurrence_factor = 0,
@frequency_subday = 8, @frequency_subday_interval = 1, @active_start_date =
0, @active_end_date = 0, @active_start_time_of_day = 0,
@active_end_time_of_day = 235959, @snapshot_job_name =
N'LON36-Northwind-NorthwindRegions-12'

GO



-- Adding the transactional articles

exec sp_addarticle @publication = N'NorthwindRegions', @article = N'Region',
@source_owner = N'dbo', @source_object = N'Region', @destination_table =
N'Region', @type = N'logbased', @creation_script = null, @description =
null, @pre_creation_cmd = N'drop', @schema_option = 0x00000000000000F3,
@status = 16, @vertical_partition = N'false', @ins_cmd = N'CALL
sp_MSins_Region', @del_cmd = N'CALL sp_MSdel_Region', @upd_cmd = N'MCALL
sp_MSupd_Region', @filter = null, @sync_object = null, @auto_identity_range
= N'false'

GO



-- Enabling the replication database

use master

GO



exec sp_replicationdboption @dbname = N'Northwind', @optname = N'merge
publish', @value = N'true'

GO



use [Sub2]

GO

exec sp_addpullsubscription @publisher = N'lon36', @publisher_db =
N'northwind', @publication = N'northwindregions', @independent_agent =
N'true', @subscription_type = N'anonymous', @update_mode = N'read only',
@immediate_sync = 1

exec sp_addpullsubscription_agent @publisher = N'lon36', @publisher_db =
N'northwind', @publication = N'northwindregions', @distributor = N'lon36',
@subscriber_security_mode = 1, @distributor_security_mode = 1,
@frequency_type = 64, @frequency_interval = 1, @frequency_relative_interval
= 1, @frequency_recurrence_factor = 0, @frequency_subday = 4,
@frequency_subday_interval = 5, @active_start_date = 0, @active_end_date =
0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959,
@enabled_for_syncmgr = N'true', @alt_snapshot_folder =
N'C:\LON36_Northwind_NorthwindRegions\20051114172547', @use_ftp = N'false',
@publication_type = 0, @dts_package_location = N'subscriber', @offloadagent
= N'false'

GO

---------------------------------------PUBLICATION------------------------------------------
-- Enabling the replication database
use master
GO

exec sp_replicationdboption @dbname = N'CriticalDevices', @optname =
N'publish', @value = N'true'
GO

use [CriticalDevices]
GO

-- Adding the transactional publication
exec sp_addpublication @publication = N'CDITrans', @restricted = N'false',
@sync_method = N'native', @repl_freq = N'continuous', @description =
N'Transactional publication of CriticalDevices database from Publisher
XIAN\XIAN1.', @status = N'active', @allow_push = N'true', @allow_pull =
N'true', @allow_anonymous = N'false', @enabled_for_internet = N'false',
@independent_agent = N'false', @immediate_sync = N'false', @allow_sync_tran =
N'false', @autogen_sync_procs = N'false', @retention = 336,
@allow_queued_tran = N'false', @snapshot_in_defaultfolder = N'false',
@alt_snapshot_folder = N'\\xian\replCDI\CDITrans', @compress_snapshot =
N'false', @ftp_port = 21, @ftp_login = N'anonymous', @allow_dts = N'false',
@allow_subscription_copy = N'false', @add_to_active_directory = N'false',
@logreader_job_name = N'XIAN\XIAN1-CriticalDevices-9'
exec sp_addpublication_snapshot @publication = N'CDITrans',@frequency_type =
4, @frequency_interval = 1, @frequency_relative_interval = 0,
@frequency_recurrence_factor = 1, @frequency_subday = 1,
@frequency_subday_interval = 0, @active_start_date = 0, @active_end_date = 0,
@active_start_time_of_day = 232600, @active_end_time_of_day = 0,
@snapshot_job_name = N'XIAN\XIAN1-CriticalDevices-CDITrans-23'
GO

exec sp_grant_publication_access @publication = N'CDITrans', @login =
N'BUILTIN\Administrators'
GO
exec sp_grant_publication_access @publication = N'CDITrans', @login =
N'distributor_admin'
GO
exec sp_grant_publication_access @publication = N'CDITrans', @login = N'sa'
GO

-- Adding the transactional articles
exec sp_addarticle @publication = N'CDITrans', @article = N'ctlControl',
@source_owner = N'dbo', @source_object = N'ctlControl', @destination_table =
N'ctlControl', @type = N'logbased', @creation_script = null, @description =
null, @pre_creation_cmd = N'drop', @schema_option = 0x00000000000000F3,
@status = 16, @vertical_partition = N'false', @ins_cmd = N'CALL
sp_MSins_ctlControl', @del_cmd = N'CALL sp_MSdel_ctlControl', @upd_cmd =
N'MCALL sp_MSupd_ctlControl', @filter = null, @sync_object = null,
@auto_identity_range = N'false'
GO
exec sp_addarticle @publication = N'CDITrans', @article =
N'mapSubType2WebNavigation', @source_owner = N'dbo', @source_object =
N'mapSubType2WebNavigation', @destination_table =
N'mapSubType2WebNavigation', @type = N'logbased', @creation_script = null,
@description = null, @pre_creation_cmd = N'drop', @schema_option =
0x000000000000CEB3, @status = 16, @vertical_partition = N'false', @ins_cmd =
N'CALL sp_MSins_mapSubType2WebNavigation', @del_cmd = N'CALL
sp_MSdel_mapSubType2WebNavigation', @upd_cmd = N'MCALL
sp_MSupd_mapSubType2WebNavigation', @filter = null, @sync_object = null,
@auto_identity_range = N'false'
GO
exec sp_addarticle @publication = N'CDITrans', @article =
N'mapWebPage2Strings2Variables', @source_owner = N'dbo', @source_object =
N'mapWebPage2Strings2Variables', @destination_table =
N'mapWebPage2Strings2Variables', @type = N'logbased', @creation_script =
null, @description = null, @pre_creation_cmd = N'drop', @schema_option =
0x000000000000CEB3, @status = 16, @vertical_partition = N'false', @ins_cmd =
N'CALL sp_MSins_mapWebPage2Strings2Variables', @del_cmd = N'CALL
sp_MSdel_mapWebPage2Strings2Variables', @upd_cmd = N'MCALL
sp_MSupd_mapWebPage2Strings2Variables', @filter = null, @sync_object = null,
@auto_identity_range = N'false'
GO


----------------------------------PULL
SUBSCRIPTION-------------------------------------

-- Adding the transactional pull subscription:
XIAN\XIAN1:CriticalDevices:CDITrans

/****** Begin: Script to be run at Subscriber: XIAN\XIAN2 ******/
use [eSmart]
GO
exec sp_addpullsubscription @publisher = N'XIAN\XIAN1', @publisher_db =
N'CriticalDevices', @publication = N'CDITrans', @independent_agent =
N'false', @subscription_type = N'pull', @description = N'Transactional
publication of CriticalDevices database from Publisher XIAN\XIAN1.',
@update_mode = N'read only', @immediate_sync = 0
exec sp_addpullsubscription_agent @publisher = N'XIAN\XIAN1', @publisher_db
= N'CriticalDevices', @publication = N'CDITrans', @distributor =
N'XIAN\XIAN1', @subscriber_security_mode = 1, @distributor_security_mode = 1,
@frequency_type = 64, @frequency_interval = 1, @frequency_relative_interval =
1, @frequency_recurrence_factor = 0, @frequency_subday = 4,
@frequency_subday_interval = 5, @active_start_date = 0, @active_end_date = 0,
@active_start_time_of_day = 0, @active_end_time_of_day = 235959,
@enabled_for_syncmgr = N'false', @alt_snapshot_folder =
N'\\xian\replcdi\cditrans', @use_ftp = N'false', @publication_type = 0,
@dts_package_location = N'subscriber', @offloadagent = N'false'
GO

/****** End: Script to be run at Subscriber: XIAN\XIAN2 ******/

/****** Begin: Script to be run at Publisher: XIAN\XIAN1 ******/
use [CriticalDevices]
GO
-- Parameter @sync_type is scripted as 'automatic', please adjust when
appropriate.
exec sp_addsubscription @publication = N'CDITrans', @subscriber =
N'XIAN\XIAN2', @destination_db = N'eSmart', @sync_type = N'automatic',
@subscription_type = N'pull', @update_mode = N'read only'
GO

/****** End: Script to be run at Publisher: XIAN\XIAN1 ******/

I've deleted all the other articles from the publication because it took a
lot of space. There r about 70 articles. See the path that says
\\xian\replcdi\cditrans? That's where I want it to go to. The snapshot runs
fine and creates under \\xian\replcdi\cditrans. but when trying to apply the
snapshot, it points to some other folder for some reason. FYI, this folder is
where the merge publication is pointing to (\\xian\replcdi\cdimerge).

when i look up the error in msdistribution_history table, I get

The process could not read file
'\\xian\replcdi\cdimerge\unc\XIAN$XIAN1_CriticalDevices_CDITrans\20051113203736\defAlertTypes_1.sch' due to OS error 3.

Under MSRepl_errors,
it tells me the same error and says "The system cannot find the path
specified"

I don't understand y the distribution agent was looking for the snapshot in

Tejas - can you script out your publication and subscription and post it up.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Hey. I've seen a few differences in our scripts.
@independent_agent = N'true',
@immediate_sync = N'true',
@snapshot_in_defaultfolder = N'true',

I dont think any of these make any major differences.
I'll try that logging now. I have used the cheap way of cutting and pasting
the snapshot into the folder where the dist agent is looking at for now.
Thank you for your help, Paul

A simple question regarding your script. I dont know how I could get the
first two scripts which are before '-- Adding the transactional publication'
I'm talking about the ones to
'Adding the distribution publisher' and
'Adding the distribution database'

I use this to script out my system, change the servernames and job owners if
they haven't been set to 'sa' then it is ready to go. I don't usually script
out the jobs as I don't usually change the standard replication jobs (these
aren't the agent jobs).
I also use an activeX task in DTS to schedule scripting out the system each
evening, just in case (script below).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)


'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************

Function Main()

Dim objDMO
Dim objReplication
Dim fso
Dim tf

Set objDMO = CreateObject("SQLDMO.SQLServer")
Set fso = CreateObject("Scripting.FileSystemObject")

objDMO.LoginSecure = True
objDMO.Connect "crazyfrog"
Set objReplication = objDMO.Replication

Set fso = CreateObject("Scripting.FileSystemObject")
Set tf = fso.CreateTextFile("c:\testfile.txt", True)

tf.Write objReplication.Script(1048576)
tf.Close

Main = DTSTaskExecResult_Success

End Function

Tejas,
The DTS task I was talking about is my DTS package that runs an ActiveX
script. This script scripts out the replication setup for safekeeping.
The scripting of jobs doesn't refer to the agent jobs we normally use in
replication, it refers to the background jobs instead that are rarely
altered.
If your jobs run as a domain account, then that account is looked up in AD
for Existence, but the job runs as the agent account anyway, so most people
change the owner to 'sa' which avoids the potential problem of accounts
being deleted.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Hey again. I never thought of doing that. If I wanted to start replication
from scratch on another server, is that the only script I would
need(obviously after replacing the server names). What all script would I
need other than that? Also, I would have to incule 'script creation of
replication jobs', am i correct? I want to take this into QA but run
everything via a script...

Thank you very much

Ok, so that script from the publication folder should be good enough for me.
I don't have any DTS transformations at all. I'm confused about the job part
that you said.
If I don't script out the jobs, and i run the rest of the script from the
right click of publication folder, will SQL create those jobs for me by
itself??
Also, my jobs will not be run as a 'sa' but will be run as a domain account

Tejas,
if you right-click the publications folder under the published database the
create sql script is slightly different to the others and allows you to pick
up all script options.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)