SQL Server Performance Forum – Threads Archive

peoplesoft replication on sql server

Hi, i’m trying to setup transactional replication fro a peoplesoft database running on sql server 2000. I would like to know if some of you already done it without difficulties because : 1) The sql replication works only if your table has a primary key: Altough almost all of peoplesoft tables have a clustered unique index, none of them has a primary key but it seems that for sql server it’s not the same…(i really wonder why as result is the same and both are unique). 2) to workaround the 1st point, i attempted to create a primary key on some tables using same fields and order as the clustered unique index. Works fine except… that some columns in peoplesoft clustered indexes allow NULL values… and when you try to create the primary key, you receive the nice message below : Cannot define PRIMARY KEY constraint on nullable column in table ‘PS_TRANSACTION’ So if somebody knows how to make an easy peoplesoft replication (to feed a reporting db), i would really appreciate the help ! tks in advance.

---

Transactional resplication requires PK on every table to participate… Clustered-unique index is not a PK because unique index will allow one NULL where PK is not… I have not worked on People Soft… If you can add new column to the table… you can add PK
alter table…. Add NewColumn uniqueidentifier not null default(newid()) CONSTRAINT … PRIMARY KEY Mohammed U.

---

tks for your answer. If there are some users with experience on peoplesoft, i will appreciate. Another small question. Doing transactionnal replication, the rows on replicated table can be updated/deleted/inserted. Is there a parameterer in the replication somewhere to say you want read only so nobody can change data on the replicated table. tks

---

I don’t think there any flag to control the user access…
You have control with permission…
Deny insert,update, delete on tablename to username
Mohammed U.

---

]]>