sql server replication: I have a few tables that I need to replicate that don't have a PK. And
since these tables are a part of a 3rd party app, I can't add one. While
I'm confident I could add an identity col and they'd never know, my concern
is that if they upgrade the app, I might lose the PK.

I have 2 questions:
1 - are there any 3rd party products out there that will allow me to
replicate w/o a PK?
2 - does anyone have any ideas how I could replicate a table w/o a PK?
A couple things I'm tossing around, but don't like, would be to schedule a
backup/restore of the db, or maybe add a trigger for insert/update/delete
that copies the data to another table with a PK - and then replicate that
table. I don't really like either option because I only need 6 tables out
of the 100+, so a restore would be inefficient. And if the company won't
let me add an identity column, I'm pretty sure they won't let me add a
trigger.

Any suggestions are appreciated.

Thanks, Andre

Andre,
a PK is mandatory for transactional replication only, so you could use
snapshot or merge replication. Merge will add an extra column, assuming the
tables don't already have a guid column with the rowguid property and this
may break your application - it depends how it has been coded. EG if all
TSQL uses named columns and doesn't have this sort of syntax:
insert into table1
select * from table 2
where you decide to replicate table2, then everything will be ok.
HTH,
Paul Ibison

In some cases you can convert your tables to views.

Create a new dbo account in your database
sp_addlogin @loginame = 'dbo_test' , @passwd = 'non_dbo','Your_db',
exec sp_grantdbaccess N'non_dbo', N'non_dbo'
exec sp_addrolemember N'db_owner', N'non_dbo'

then change the object owner for all of your tables

sp_msforeachtable 'sp_changeobjectowner ''?'',''non_dbo'''

Then issue the below query and in the results pane copy the statements back
into your query pane to run them.

select 'create view '+name+' as select * from [non_dbo].'+name from
sysobjects where type='u'

Then add some pk's to your tables, run this query and paste the results back
in your query pane and run them.

select 'alter table [non_dbo].'+name+' add PrimaryKey int not null primary
key identity(1,1)' from sysobjects where type='u'

Now you have tables with PKs. Your app will still talk to views with the
same name as the original tables, you won't have to qualify the pk in your
insert statements and everything should work as before.

You should be able to replicate these tables now.


--
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html


[quoted text, click to view]