Happy New Year and Christmas everybody! Another question:

Can i alter a table included in a Publication?

I am having problems with that.

Yes, if you are using SQL Server 2005. Additionally, if you are using Merge replication your publication compatibility should be set to 90RTM.

With this, you can alter a table and add/drop/alter a column and this DDL action will be propagated to the subscribers.

|||

For DDL opertion on replicated object tables , in SQL 2000 you can use sp_repladdcolumn/sp_repldropcolumn to add/remove the table columns

|||

Mahesh Dudgikar wrote:

Yes, if you are using SQL Server 2005. Additionally, if you are using Merge replication your publication compatibility should be set to 90RTM.

But if my compatibility level is set to 80 - can I change it to 90, call ALTER TABLE, and then change it back to 80? Will it cause any problems?

|||

You can change the compat from 80 to 90 and then use alter table.

However once the publication compatibility is set to 90, you cannot go back to 80.

|||If your compatibility is set to 80 then you are running in SQL 2000 mode. So you have to use the sp_repladdcolumn/sp_repldropcolumn to add/drop columns.|||

Mahesh Dudgikar wrote:

You can change the compat from 80 to 90 and then use alter table.

However once the publication compatibility is set to 90, you cannot go back to 80.

1. Is compatibility level defined separately for DATABASE and for PUBLICATION?

2. If not: I have tried to change the compatibility level for unpublished database (both upgrading and downgrading), and it succeeded. Does publication lock downgrading of compatibility level of the database?

Thanks in advance!

|||

Sorry for not being clear.

Above, I meant @.publication_compatibility_level of the publication setting, not the database compat level.

They are separate settings. The database compat level has nothing to do with the DDL method (sp_repladdcolumn and alter table)

Handle Error

Hi everybody, i do
insert into client
select * from client
client table has pk when execute the query the follow error appear
Servidor: mensaje 2627, nivel 14, estado 1, lnea 2
Infraccin de la restriccin PRIMARY KEY 'PK__EMPLEADO__4924D839'. No se
puede insertar una clave duplicada en el objeto 'EMPLEADO'.
Se termin la instruccin.
what can i do to obtain the complete message in bold to insert into a table
Any idea?
P.D. sorry my english is not good
Regards,
JorgeAre you using SQL Server 2005? If so, look up TRY..CATCH in Books Online,
and check out the ERROR_MESSAGE function. If you're in SQL Server 2000, you
might have to handle this on the client.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Jorge Aguilar" <j.aguilar@.lpcsite.com> wrote in message
news:uM%239FoIcGHA.1276@.TK2MSFTNGP03.phx.gbl...
> Hi everybody, i do
> insert into client
> select * from client
> client table has pk when execute the query the follow error appear
> Servidor: mensaje 2627, nivel 14, estado 1, lnea 2
> Infraccin de la restriccin PRIMARY KEY 'PK__EMPLEADO__4924D839'. No se
> puede insertar una clave duplicada en el objeto 'EMPLEADO'.
> Se termin la instruccin.
> what can i do to obtain the complete message in bold to insert into a
> table
> Any idea?
> P.D. sorry my english is not good
> Regards,
> Jorge
>|||Jorge Aguilar (j.aguilar@.lpcsite.com) writes:
> Hi everybody, i do
> insert into client
> select * from client
> client table has pk when execute the query the follow error appear
> Servidor: mensaje 2627, nivel 14, estado 1, lnea 2
> Infraccin de la restriccin PRIMARY KEY 'PK__EMPLEADO__4924D839'. No se
> puede insertar una clave duplicada en el objeto 'EMPLEADO'.
> Se termin la instruccin.
> what can i do to obtain the complete message in bold to insert into a
> table
I'm not really sure what you are asking for, but if the question is
how to retrieve the text of the error message from SQL, the answer
depends on the version of SQL Server you are using:
SQL 2000 - you can't.
SQL 2005 you can use TRY CATCH:
BEGIN TRY
INSERT into CLIENT SELECT * FROM client
END TRY
BEGIN CATCH
INSERT errortable (msg, text, proc, line)
SELECT error_number(), error_message(), error_procedure(),
error_line()
END CATCH
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Do you have a trigger on the table CLIENT that updates or inserts to the
table EMPLEADO?
Why are you inserting existing rows into client? It appears that you are
creating duplicates of every row, which is almost always a mistake. It will
cause many problems later.
Please explain what you are trying to do (and why) and provide the scripts
to create your tables, keys, and triggers. Specifically, we need the
scripts for CLIENTS and EMPLEADO.
Also, you might try posting to:
microsoft.public.espanol.sqlserver.administracion
The focus of the group is different, but language may not be as much of a
problem there, and this error should be known to them.
"Jorge Aguilar" <j.aguilar@.lpcsite.com> wrote in message
news:uM%239FoIcGHA.1276@.TK2MSFTNGP03.phx.gbl...
> Hi everybody, i do
> insert into client
> select * from client
> client table has pk when execute the query the follow error appear
> Servidor: mensaje 2627, nivel 14, estado 1, lnea 2
> Infraccin de la restriccin PRIMARY KEY 'PK__EMPLEADO__4924D839'. No se
> puede insertar una clave duplicada en el objeto 'EMPLEADO'.
> Se termin la instruccin.
> what can i do to obtain the complete message in bold to insert into a
table
> Any idea?
> P.D. sorry my english is not good
> Regards,
> Jorge
>