SQL Server Performance Forum – Threads Archive

Replication and Primary Key on a table?

Hi; I am working on replication, I am trying snapshot replication but I am having one error i.e. Error No. 207 "Invalid Column Name ”" , [<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />!]it is not showing any colume name but blank. Then I heared that it is compulsory for replication that each table must have Primary Key on it. I have different types of table e.g. with Primary Key, with Identity primary key and without Primary key. I know a bit about IDENTITY column which requires NOT FOR REPLICATION option set for replication. I have two question:[?] 1- Each table should have Primary Key for replication, is it true? 2- If not then how can I resolve that Error? Thanks. Essa, M. Mughal Software Engineer

It is recommended to have a primary key for snapshot replication. This is required for transactional replication. Can you tell me your sql server version with service pack.
And are you using any row filter in your replication. Rajeev Kumar Srivastava
–ALWAYS BE POSITIVE!–

NOT FOR REPLICATION is an option on identities, FKs and triggers. If you look in BOL for ‘CREATE TRIGGER’ you’ll see details about its use. Basically it is telling SQL Server not to apply the constraint, identity, trigger code etc to data originating through the replication process. There is a KBA hotfix for similar error when using merge replication, I will findout whether its available for snapshot. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Thanks Rajeev and Satya, Sorry, I did not mention SQL version, I am using SQL Server 2000 with Service Pack 3. No, I am not using any row filter in the replication. Satya, I found some article about that kind Bug i.e. Error 207 "Invalid Column Name ”’" and It is known to me from website the SQL Server Service Pack 4 will fix that bug. Is it really a bug? I would really appreciate that Hotfix for snapshot/Transactional. Thanks. Essa, M. Mughal
Software Engineer

Hope the SP4 for SQL 2K will have this bug and I’m sure it might have fix.
Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Hi All; I just want to share the interesting thing which happened to me in this context, I was getting the Error "Invalid Column name ”’ " but the column name was not mentioned which was hint to me that I might have a table which has the bad column name. To find out that column name I did query to syscolumns but I did not get that column. I found some blank columns in that table which were for UDF functions. Then, I treid ERWIN To find out that, I did Reverse Engineering and I got that Error again during reverse engineering on one table which has columns names defined with Dashes "-" and it was Raw table created by DTS. So, I found the hint and clue then I removed that table and the Snapshot Replication was done successfully. The interesting point is that the SQL Server allowed to create the Invalid Column which Dashes but the Replication was failed b/c of that column name. It might be some bug with SQL Server. I need comments on that from you. Thanks for helping me.
Essa, M. Mughal
Software Engineer

]]>