MediaBin Database Replication

lkeller

Using Mediabin build 7.2 on SQL Server 2008 R2. I want to use transactional replication to a backup instance but Microsoft requires that all tables have a primary key. Table dbo.JobMsgs in the Mediabin Repository database does not have a primary key. Has anyone dealt with this? Thank you.

Well, no one replied to this post but I did receive an answer from Interwoven Support as follows:

That table is a helper table. Normally, it has no content. When a job is being processed, we store messages about a job in the table. When the job is completed, we concatenate all messages of the job into one string and insert it into the JobHistoryLog table. As soon as that's done, we erase all messages of the job from the JobMsgs table.
So the table is really transient and that's why it does not have a primary key.

- As you need it we would really like to help you create one.Here below is a sample to add primary key;
>ALTER TABLE dbo.JobMsgs ADD CONSTRAINT pk_JobMsgs PRIMARY KEY NONCLUSTERED(jobid, msgtime)

Hope this will be helpful.

UPDATE, lkeller
As defined, field msgtime allows nulls and cannot be used in a primary key. However, because the field has a default constraint calling the getdate() function it will never be null and can be redefinded as "not null"
ALTER TABLE [dbo].[JobMsgs] alter column msgtime datetime not null
ALTER TABLE dbo.JobMsgs ADD CONSTRAINT pk_JobMsgs PRIMARY KEY NONCLUSTERED(jobid, msgtime)