Transactional Replication without a GUI
As I'd previously posted I had to be able to set up replication through T-SQL scripts only, my database to be replicated has identity columns and time stamps. The subscribing database has to be fully operational seperate to the publishing database, the plan is to use the subscribing database for support.
Changes to the subscriber do not get pushed back to the publisher. After much discussion we've decided to go with data only replication, no snapshot. This has it's own problems as you then have to generate the procedures yourself and then for tables with identity columns you have to edit the procs - fun huh?
So first change is to make sure all the identity columns on those tables with identity columns have the NOT FOR REPLICATION set, this script shows the status of sql2k and sql2005 ( I did the 2k script earlier )
There's a script available to update the system tables, I don't support this and apart from that the database schema is in version control, so it's off to modify each table in turn!
--
-- Identify the setting of the identity status
-- we need this to be NOT FOR REPLICATION
--
-- Run in database to be published
--
-- SQL 2000 Version
--
select obj.name,col.name,
case columnproperty(obj.id,col.name,'isidnotforrepl')
when 0 then 'Not Set'
when 1 then 'Set'
else 'unknown'
end as 'Not for Repl'
from dbo.sysobjects obj join dbo.syscolumns col on obj.id=col.id
where col.autoval is not null
and objectproperty(obj.id,'tablehasidentity')=1
and obj.xtype='U' and obj.name not like 'dt%'
--
-- SQL 2005 Version
--
select obj.name,col.name,
case columnproperty(obj.object_id,col.name,'isidnotforrepl')
when 0 then 'Not Set'
when 1 then 'Set'
else 'unknown'
end as 'Not for Repl'
from sys.objects obj join sys.columns col on obj.object_id=col.object_id
where col.is_identity=1
and objectproperty(obj.object_id,'tablehasidentity')=1
and obj.type='U' and obj.name not like 'dt%'