Merge Replication Error: Column names in each table must be unique.

[Noman Qureshi]

Hello All

I am new to SQL and facing problem creating merge replication, searched MS
site but found nothing on it

I have created a new merge publication with all the tables in a DB, this
error occurs wile snapshot agent starts to create the snapshot error is

Snapshot Agent Error Details

Error:
"Column names in each table must be unique. Column name 'PubID' in table
'aonflict_NomBilling_PubPage' is specified more than once."

Category = Data source
Source = sqlserver1
Number = 2705

At this point the snap shot agent aborts

Any advice/solution

Thanks & Regards

Qazi M.

[Hilary Cotter]

there are some reserved column names in merge replication. PubID is one of
them, others are: reason_code, source_object, reason_text,
conflict_type, origin_datasource, tablenick, and create_time.

If any of your tables have columns with these names in them, your snapshot
will fail with a message similar to the one you are getting.

You will have to change the column names for this to work.
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Post by Noman Qureshi
Hello All
I am new to SQL and facing problem creating merge replication, searched MS
site but found nothing on it
I have created a new merge publication with all the tables in a DB, this
error occurs wile snapshot agent starts to create the snapshot error is
Snapshot Agent Error Details
"Column names in each table must be unique. Column name 'PubID' in table
'aonflict_NomBilling_PubPage' is specified more than once."
Category = Data source
Source = sqlserver1
Number = 2705
At this point the snap shot agent aborts
Any advice/solution
Thanks & Regards
Qazi M.

[Noman Qureshi]

Thanks for you reply Hilary
The table and column is in use of five applications, is there any other
workaround for this problem

(else have to modify and recompile all the applications)

Post by Hilary Cotter
there are some reserved column names in merge replication. PubID is one of
them, others are: reason_code, source_object, reason_text,
conflict_type, origin_datasource, tablenick, and create_time.
If any of your tables have columns with these names in them, your snapshot
will fail with a message similar to the one you are getting.
You will have to change the column names for this to work.
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Post by Noman Qureshi
Hello All
I am facing problem creating merge replication, searched MS site but
found nothing on it
I have created a new merge publication with all the tables in a DB, this
error occurs wile snapshot agent starts to create the snapshot error is
Snapshot Agent Error Details
"Column names in each table must be unique. Column name 'PubID' in table
'aonflict_NomBilling_PubPage' is specified more than once."
Category = Data source
Source = sqlserver1
Number = 2705
At this point the snap shot agent aborts
Any advice/solution
Thanks & Regards
Qazi M.

[Kestutis Adomavicius]

You could create a "mirror table", with the only difference from original -
changed name of problematic column. And you could include this table in
replication. Of course you would gave to make sure that all the actions
(insert/update/delete) which are done on original table, would also be done
on "mirror table" and vice versa. That could be done by triggers, hopefully
without big problems.
--
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"

Post by Noman Qureshi
Thanks for you reply Hilary
The table and column is in use of five applications, is there any other
workaround for this problem
(else have to modify and recompile all the applications)

Post by Hilary Cotter
there are some reserved column names in merge replication. PubID is one of
them, others are: reason_code, source_object, reason_text,
conflict_type, origin_datasource, tablenick, and create_time.
If any of your tables have columns with these names in them, your snapshot
will fail with a message similar to the one you are getting.
You will have to change the column names for this to work.
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Post by Noman Qureshi
Hello All
I am facing problem creating merge replication, searched MS site but
found nothing on it
I have created a new merge publication with all the tables in a DB, this
error occurs wile snapshot agent starts to create the snapshot error is
Snapshot Agent Error Details
"Column names in each table must be unique. Column name 'PubID' in table
'aonflict_NomBilling_PubPage' is specified more than once."
Category = Data source
Source = sqlserver1
Number = 2705
At this point the snap shot agent aborts
Any advice/solution
Thanks & Regards
Qazi M.

[Hilary Cotter]

If your tables are small, you could have the application write to views
which correspond to the underlying tables, only the column names in the
underlying tables are correct, but pubid would be in the view. If the tables
are larger you would have to go with indexed views.
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Post by Noman Qureshi
Thanks for you reply Hilary
The table and column is in use of five applications, is there any other
workaround for this problem
(else have to modify and recompile all the applications)

Post by Hilary Cotter
there are some reserved column names in merge replication. PubID is one of
them, others are: reason_code, source_object, reason_text,
conflict_type, origin_datasource, tablenick, and create_time.
If any of your tables have columns with these names in them, your snapshot
will fail with a message similar to the one you are getting.
You will have to change the column names for this to work.
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Post by Noman Qureshi
Hello All
I am facing problem creating merge replication, searched MS site but
found nothing on it
I have created a new merge publication with all the tables in a DB, this
error occurs wile snapshot agent starts to create the snapshot error is
Snapshot Agent Error Details
"Column names in each table must be unique. Column name 'PubID' in table
'aonflict_NomBilling_PubPage' is specified more than once."
Category = Data source
Source = sqlserver1
Number = 2705
At this point the snap shot agent aborts
Any advice/solution
Thanks & Regards
Qazi M.

[Noman Qureshi]

Thanks for the reply Hilary, Kestutis

We have decided that it is batter to update the applications and rename the
problematic column, that will make our DB more compliant to (MS) standards
and will be beneficial for us in the future, who knows what we will get in
the next patch/sp or future version of SQL . We have forwarded the column
names provided by Hilary to our development team so they can avoid them in
the future.

Thanks again
Regards & Best Wishes

Noman Qureshi
"Even impossible says I M Possible"

Post by Hilary Cotter
If your tables are small, you could have the application write to views
which correspond to the underlying tables, only the column names in the
underlying tables are correct, but pubid would be in the view. If the tables
are larger you would have to go with indexed views.
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Post by Noman Qureshi
Thanks for you reply Hilary
The table and column is in use of five applications, is there any other
workaround for this problem
(else have to modify and recompile all the applications)

Post by Hilary Cotter
there are some reserved column names in merge replication. PubID is one

of

Post by Noman Qureshi

Post by Hilary Cotter
them, others are: reason_code, source_object, reason_text,
conflict_type, origin_datasource, tablenick, and create_time.
If any of your tables have columns with these names in them, your

snapshot

Post by Noman Qureshi

Post by Hilary Cotter
will fail with a message similar to the one you are getting.
You will have to change the column names for this to work.
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Post by Noman Qureshi
Hello All
I am facing problem creating merge replication, searched MS site but
found nothing on it
I have created a new merge publication with all the tables in a DB,

this

Post by Noman Qureshi

Post by Hilary Cotter

Post by Noman Qureshi
error occurs wile snapshot agent starts to create the snapshot error is
Snapshot Agent Error Details
"Column names in each table must be unique. Column name 'PubID' in

table

Post by Noman Qureshi

Post by Hilary Cotter

Post by Noman Qureshi
'aonflict_NomBilling_PubPage' is specified more than once."
Category = Data source
Source = sqlserver1
Number = 2705
At this point the snap shot agent aborts
Any advice/solution
Thanks & Regards
Qazi M.

[عبدالله فيصل]

Post by Noman Qureshi
Thanks for the reply Hilary, Kestutis
We have decided that it is batter to update the applications and rename the
problematic column, that will make our DB more compliant to (MS) standards
and will be beneficial for us in the future, who knows what we will get in
the next patch/sp or future version of SQL . We have forwarded the column
names provided by Hilary to our development team so they can avoid them in
the future.
Thanks again
Regards & Best Wishes
Noman Qureshi
"Even impossible says I M Possible"

Post by Hilary Cotter
If your tables are small, you could have the application write to views
which correspond to the underlying tables, only the column names in the
underlying tables are correct, but pubid would be in the view. If the tables
are larger you would have to go with indexed views.
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Post by Noman Qureshi
Thanks for you reply Hilary
The table and column is in use of five applications, is there any other
workaround for this problem
(else have to modify and recompile all the applications)

Post by Hilary Cotter
there are some reserved column names in merge replication. PubID is one

of

Post by Noman Qureshi

Post by Hilary Cotter
them, others are: reason_code, source_object, reason_text,
conflict_type, origin_datasource, tablenick, and create_time.
If any of your tables have columns with these names in them, your

snapshot

Post by Noman Qureshi

Post by Hilary Cotter
will fail with a message similar to the one you are getting.
You will have to change the column names for this to work.
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Post by Noman Qureshi
Hello All
I am facing problem creating merge replication, searched MS site but
found nothing on it
I have created a new merge publication with all the tables in a DB,

this

Post by Noman Qureshi

Post by Hilary Cotter

Post by Noman Qureshi
error occurs wile snapshot agent starts to create the snapshot error is
Snapshot Agent Error Details
"Column names in each table must be unique. Column name 'PubID' in

table

Post by Noman Qureshi

Post by Hilary Cotter

Post by Noman Qureshi
'aonflict_NomBilling_PubPage' is specified more than once."
Category = Data source
Source = sqlserver1
Number = 2705
At this point the snap shot agent aborts
Any advice/solution
Thanks & Regards
Qazi M.

the best solution :
let column name error : dept_no
1.create temporary table as name temp_dept_no and pk of table let name dept_id
2.copy data of dept_no column into table (temp_dept_no)
3.drop column from each server (dept_no)
4.create column in table from main server name (dept_no)
5.move data from table temp_dept_no using pk of dept_id