Altering a Published Table

Hi! I've added a column to a published table in the Publisher using SP_REPLADDCOLUMN. After doing this the replication triggers for that table(i.e. del_%,upd_%,ins_%) are doubled both at the Publisher and at the Subscriber. If i update anyone of the column in the table at the Subscriber; i get the following error:

Server: Msg 208, Level 16, State 1, Procedure upd_3E6DE124B82D42A5AEB169557C0D757C, Line 60
Invalid object name 'ctsv_3E6DE124B82D42A5AEB169557C0D757C'.

Any ideas, what has gone wrong??

I have the following SQL setup.
Publisher: Enterprise Edition, SP3.
Subscriber: Standard-Edition, SP3.
The error is at the Subscriber.Did you set both @.force_invalidate_snapshot and @.force_reinit_subscription to 1. My guess is not. You have to reinitialize all subscriptions now to resolve the problem.|||Originally posted by joejcheng
Did you set both @.force_invalidate_snapshot and @.force_reinit_subscription to 1. My guess is not. You have to reinitialize all subscriptions now to resolve the problem.

No i didn't set these variables to 1, for i can't afford a full SNAPSHOT over the internet. The PUBLISHER is of 3 GB in size. What steps i can take so that it doesn't happen again.!!!?
I've temporarily fixed the problem by removing the newly created replication trigger manually at both the PUBLISHER and SUBSCRIBER.|||When you add a columns that is not PK, and you dont wish to reinitiliase during operation hours, you can choose this way, try to create a column by using Publisher properties, go to Filter columns in Publisher properties, then add the column using the Add column button, and then dont tick on that column you just added. Now you didnt ask merge agent to replicate that column for you. What you doing is to add a column for your user to make use of that column in the publisher database server. Wait till you have time to replicate the whole snapshot off the operation hours. Good Luck !

Originally posted by TALAT
No i didn't set these variables to 1, for i can't afford a full SNAPSHOT over the internet. The PUBLISHER is of 3 GB in size. What steps i can take so that it doesn't happen again.!!!?
I've temporarily fixed the problem by removing the newly created replication trigger manually at both the PUBLISHER and SUBSCRIBER.|||Thanx for your help eshl. I use to apply the SNAPSHOT only when there's a change in the PK or an ID seed column of a published table. The publication-database has 24 hours production and the subscribers have online websites running on them. I am eagerly lookin for a way to completely bypass the SNAPSHOT.|||http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20800513.html

ALTER TABLE question

Hi,
Will ALTER TABLE/ALTER COLUMN fire any triggers/fill any
defaults/observe any constraints defined on a table?
I want to change the nullability of some columns and add a
primary key constraint to a table. When I monitor what the
ALTER TABLE really does (using Profiler), I see strange
inserts and updates like "insert [dbo].[table] select *
from [dbo].[table]" or "update [dbo].[table] set [column] =
[column]". What are they for, and will these inserts and
updates activate any triggers/defaults/constraints defined
for a table?
I use SQL Server 2000 SP3a.
Many thanks,
Osk
Are you doing this in Enterprise Manager or Query Analyzer.
You really want to be using Query Analyzer to make these
kind of changes.
-Sue
On Thu, 20 Jan 2005 04:14:02 -0800, "Osk"
<anonymous@.discussions.microsoft.com> wrote:

>Hi,
>Will ALTER TABLE/ALTER COLUMN fire any triggers/fill any
>defaults/observe any constraints defined on a table?
>I want to change the nullability of some columns and add a
>primary key constraint to a table. When I monitor what the
>ALTER TABLE really does (using Profiler), I see strange
>inserts and updates like "insert [dbo].[table] select *
>from [dbo].[table]" or "update [dbo].[table] set [column] =
>[column]". What are they for, and will these inserts and
>updates activate any triggers/defaults/constraints defined
>for a table?
>I use SQL Server 2000 SP3a.
|||I'm using Query Analyzer, yes. Ent. Man. doesn't issue the
ALTER TABLE command as far as I know. But what about
triggers/constraint - will they bey fired/observed?
Thanks,
Osk

>--Original Message--
>Are you doing this in Enterprise Manager or Query Analyzer.
>You really want to be using Query Analyzer to make these
>kind of changes.
>-Sue
>On Thu, 20 Jan 2005 04:14:02 -0800, "Osk"
><anonymous@.discussions.microsoft.com> wrote:
>
>.
>
|||On Thu, 20 Jan 2005 22:39:58 -0800, Osk wrote:

>I'm using Query Analyzer, yes. Ent. Man. doesn't issue the
>ALTER TABLE command as far as I know. But what about
>triggers/constraint - will they bey fired/observed?
Hi Osk,
Easy to test, isn't it?
CREATE TABLE Test (Col1 int NULL,
Col2 int NULL)
go
CREATE TRIGGER TestTrig ON Test
AFTER INSERT, UPDATE, DELETE
AS
PRINT 'I''m fired!'
go
ALTER TABLE Test
ALTER COLUMN Col1 int NOT NULL
go
ALTER TABLE Test
ADD CONSTRAINT pk_Test PRIMARY KEY (Col1)
go
DROP TABLE Test
go
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

ALTER TABLE question

Hi,
Will ALTER TABLE/ALTER COLUMN fire any triggers/fill any
defaults/observe any constraints defined on a table?
I want to change the nullability of some columns and add a
primary key constraint to a table. When I monitor what the
ALTER TABLE really does (using Profiler), I see strange
inserts and updates like "insert [dbo].[table] select *
from [dbo].[table]" or "update [dbo].[table] set [column
] =
[column]". What are they for, and will these inserts and
updates activate any triggers/defaults/constraints defined
for a table?
I use SQL Server 2000 SP3a.
Many thanks,
OskAre you doing this in Enterprise Manager or Query Analyzer.
You really want to be using Query Analyzer to make these
kind of changes.
-Sue
On Thu, 20 Jan 2005 04:14:02 -0800, "Osk"
<anonymous@.discussions.microsoft.com> wrote:

>Hi,
>Will ALTER TABLE/ALTER COLUMN fire any triggers/fill any
>defaults/observe any constraints defined on a table?
>I want to change the nullability of some columns and add a
>primary key constraint to a table. When I monitor what the
>ALTER TABLE really does (using Profiler), I see strange
>inserts and updates like "insert [dbo].[table] select *
>from [dbo].[table]" or "update [dbo].[table] set [colum
n] =
>[column]". What are they for, and will these inserts and
>updates activate any triggers/defaults/constraints defined
>for a table?
>I use SQL Server 2000 SP3a.|||I'm using Query Analyzer, yes. Ent. Man. doesn't issue the
ALTER TABLE command as far as I know. But what about
triggers/constraint - will they bey fired/observed?
Thanks,
Osk

>--Original Message--
>Are you doing this in Enterprise Manager or Query Analyzer.
>You really want to be using Query Analyzer to make these
>kind of changes.
>-Sue
>On Thu, 20 Jan 2005 04:14:02 -0800, "Osk"
><anonymous@.discussions.microsoft.com> wrote:
>
>.
>|||On Thu, 20 Jan 2005 22:39:58 -0800, Osk wrote:

>I'm using Query Analyzer, yes. Ent. Man. doesn't issue the
>ALTER TABLE command as far as I know. But what about
>triggers/constraint - will they bey fired/observed?
Hi Osk,
Easy to test, isn't it?
CREATE TABLE Test (Col1 int NULL,
Col2 int NULL)
go
CREATE TRIGGER TestTrig ON Test
AFTER INSERT, UPDATE, DELETE
AS
PRINT 'I''m fired!'
go
ALTER TABLE Test
ALTER COLUMN Col1 int NOT NULL
go
ALTER TABLE Test
ADD CONSTRAINT pk_Test PRIMARY KEY (Col1)
go
DROP TABLE Test
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

ALTER TABLE question

Hi,
Will ALTER TABLE/ALTER COLUMN fire any triggers/fill any
defaults/observe any constraints defined on a table?
I use SQL Server 2000 SP3a.
--
Many thanks,
OskHi,
Will ALTER TABLE/ALTER COLUMN fire any triggers/fill any
defaults/observe any constraints defined on a table?
I want to change the nullability of some columns and add a
primary key constraint to a table. When I monitor what the
ALTER TABLE really does (using Profiler), I see strange
inserts and updates like "insert [dbo].[table] select *
from [dbo].[table]" or "update [dbo].[table] set [column] =[column]". What are they for, and will these inserts and
updates activate any triggers/defaults/constraints defined
for a table?
I use SQL Server 2000 SP3a.
--
Many thanks,
Osk|||Are you doing this in Enterprise Manager or Query Analyzer.
You really want to be using Query Analyzer to make these
kind of changes.
-Sue
On Thu, 20 Jan 2005 04:14:02 -0800, "Osk"
<anonymous@.discussions.microsoft.com> wrote:
>Hi,
>Will ALTER TABLE/ALTER COLUMN fire any triggers/fill any
>defaults/observe any constraints defined on a table?
>I want to change the nullability of some columns and add a
>primary key constraint to a table. When I monitor what the
>ALTER TABLE really does (using Profiler), I see strange
>inserts and updates like "insert [dbo].[table] select *
>from [dbo].[table]" or "update [dbo].[table] set [column] =>[column]". What are they for, and will these inserts and
>updates activate any triggers/defaults/constraints defined
>for a table?
>I use SQL Server 2000 SP3a.|||I'm using Query Analyzer, yes. Ent. Man. doesn't issue the
ALTER TABLE command as far as I know. But what about
triggers/constraint - will they bey fired/observed?
--
Thanks,
Osk
>--Original Message--
>Are you doing this in Enterprise Manager or Query Analyzer.
>You really want to be using Query Analyzer to make these
>kind of changes.
>-Sue
>On Thu, 20 Jan 2005 04:14:02 -0800, "Osk"
><anonymous@.discussions.microsoft.com> wrote:
>>Hi,
>>Will ALTER TABLE/ALTER COLUMN fire any triggers/fill any
>>defaults/observe any constraints defined on a table?
>>I want to change the nullability of some columns and add a
>>primary key constraint to a table. When I monitor what the
>>ALTER TABLE really does (using Profiler), I see strange
>>inserts and updates like "insert [dbo].[table] select *
>>from [dbo].[table]" or "update [dbo].[table] set [column] =>>[column]". What are they for, and will these inserts and
>>updates activate any triggers/defaults/constraints defined
>>for a table?
>>I use SQL Server 2000 SP3a.
>.
>|||On Thu, 20 Jan 2005 22:39:58 -0800, Osk wrote:
>I'm using Query Analyzer, yes. Ent. Man. doesn't issue the
>ALTER TABLE command as far as I know. But what about
>triggers/constraint - will they bey fired/observed?
Hi Osk,
Easy to test, isn't it?
CREATE TABLE Test (Col1 int NULL,
Col2 int NULL)
go
CREATE TRIGGER TestTrig ON Test
AFTER INSERT, UPDATE, DELETE
AS
PRINT 'I''m fired!'
go
ALTER TABLE Test
ALTER COLUMN Col1 int NOT NULL
go
ALTER TABLE Test
ADD CONSTRAINT pk_Test PRIMARY KEY (Col1)
go
DROP TABLE Test
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Alter Table

I want to disable all the triggers of a data base (and enable all of then
after the execution of a process).
I can find out the name of the user tables looking for then in the
sysobjects table and then I can run "alter table <name> disable all" but I
have to repeat it with all the tables.
My question is: Can I write a store procedure to do this? I tried to do it
with a cursor with the name of all the tables but it doesnt't work because
the alter table expects a table name, not a variable.
Thank you.Hi,
Try out this script.
declare @.x varchar(255)
select @.x = @.x + 'alter table '+name+ 'disable trigger all'
from master.dbo.sysobjects where type='u'
exec (@.x)
go
Thanks
Hari
MCDBA
"Alberto" <alberto@.nospam.com> wrote in message
news:O9OY8kluDHA.2408@.tk2msftngp13.phx.gbl...
> I want to disable all the triggers of a data base (and enable all of then
> after the execution of a process).
> I can find out the name of the user tables looking for then in the
> sysobjects table and then I can run "alter table <name> disable all" but I
> have to repeat it with all the tables.
> My question is: Can I write a store procedure to do this? I tried to do it
> with a cursor with the name of all the tables but it doesnt't work because
> the alter table expects a table name, not a variable.
> Thank you.
>|||I thought you might need a cursor to do this. How can you tell what would
need a cursor and what would not ?
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:u8VcsuluDHA.3140@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Try out this script.
>
> declare @.x varchar(255)
> select @.x = @.x + 'alter table '+name+ 'disable trigger all'
> from master.dbo.sysobjects where type='u'
> exec (@.x)
> go
> Thanks
> Hari
> MCDBA
>
> "Alberto" <alberto@.nospam.com> wrote in message
> news:O9OY8kluDHA.2408@.tk2msftngp13.phx.gbl...
> > I want to disable all the triggers of a data base (and enable all of
then
> > after the execution of a process).
> >
> > I can find out the name of the user tables looking for then in the
> > sysobjects table and then I can run "alter table <name> disable all" but
I
> > have to repeat it with all the tables.
> >
> > My question is: Can I write a store procedure to do this? I tried to do
it
> > with a cursor with the name of all the tables but it doesnt't work
because
> > the alter table expects a table name, not a variable.
> >
> > Thank you.
> >
> >
>

Alter Stored Procedure and Trigger

Hi All,
I need to modify some Stored Procedures and Triggers in production database
(SQL 2000). Can I use Edit object which means execute "Alter procedure..."
or "Alter Trigger..." directly? or I have to drop the existing SP or TR and
recreate them? Any difference between these 2 methods?
Thank you, Julia
The net effect will be the same, the only difference will the the create
date will change for the drop/add whereas it will not change for the alter.
So dropping the sp/tr and recreating it will set the new create date for
tracking purposes, if you want to know when it was last changed. But about
the only difference.
"Julia" <Julia@.discussions.microsoft.com> wrote in message
news:F2D56079-CC8F-4F4A-A7ED-5417AF745EB7@.microsoft.com...
> Hi All,
> I need to modify some Stored Procedures and Triggers in production
database
> (SQL 2000). Can I use Edit object which means execute "Alter
procedure..."
> or "Alter Trigger..." directly? or I have to drop the existing SP or TR
and
> recreate them? Any difference between these 2 methods?
> Thank you, Julia
>
|||Sorry, forgot, along with the drop/add, object ownership could change,
depending on who you are logged in as when you recreate the sp.
"Julia" <Julia@.discussions.microsoft.com> wrote in message
news:F2D56079-CC8F-4F4A-A7ED-5417AF745EB7@.microsoft.com...
> Hi All,
> I need to modify some Stored Procedures and Triggers in production
database
> (SQL 2000). Can I use Edit object which means execute "Alter
procedure..."
> or "Alter Trigger..." directly? or I have to drop the existing SP or TR
and
> recreate them? Any difference between these 2 methods?
> Thank you, Julia
>
|||Julia wrote:
> Hi All,
> I need to modify some Stored Procedures and Triggers in production
> database (SQL 2000). Can I use Edit object which means execute
> "Alter procedure..." or "Alter Trigger..." directly? or I have to
> drop the existing SP or TR and recreate them? Any difference between
> these 2 methods?
> Thank you, Julia
Object IDs change on a drop/create. Use alter whenever possible.
David Gugick
Imceda Software
www.imceda.com

Alter Stored Procedure and Trigger

Hi All,
I need to modify some Stored Procedures and Triggers in production database
(SQL 2000). Can I use Edit object which means execute "Alter procedure..."
or "Alter Trigger..." directly? or I have to drop the existing SP or TR and
recreate them? Any difference between these 2 methods?
Thank you, JuliaThe net effect will be the same, the only difference will the the create
date will change for the drop/add whereas it will not change for the alter.
So dropping the sp/tr and recreating it will set the new create date for
tracking purposes, if you want to know when it was last changed. But about
the only difference.
"Julia" <Julia@.discussions.microsoft.com> wrote in message
news:F2D56079-CC8F-4F4A-A7ED-5417AF745EB7@.microsoft.com...
> Hi All,
> I need to modify some Stored Procedures and Triggers in production
database
> (SQL 2000). Can I use Edit object which means execute "Alter
procedure..."
> or "Alter Trigger..." directly? or I have to drop the existing SP or TR
and
> recreate them? Any difference between these 2 methods?
> Thank you, Julia
>|||Sorry, forgot, along with the drop/add, object ownership could change,
depending on who you are logged in as when you recreate the sp.
"Julia" <Julia@.discussions.microsoft.com> wrote in message
news:F2D56079-CC8F-4F4A-A7ED-5417AF745EB7@.microsoft.com...
> Hi All,
> I need to modify some Stored Procedures and Triggers in production
database
> (SQL 2000). Can I use Edit object which means execute "Alter
procedure..."
> or "Alter Trigger..." directly? or I have to drop the existing SP or TR
and
> recreate them? Any difference between these 2 methods?
> Thank you, Julia
>|||Julia wrote:
> Hi All,
> I need to modify some Stored Procedures and Triggers in production
> database (SQL 2000). Can I use Edit object which means execute
> "Alter procedure..." or "Alter Trigger..." directly? or I have to
> drop the existing SP or TR and recreate them? Any difference between
> these 2 methods?
> Thank you, Julia
Object IDs change on a drop/create. Use alter whenever possible.
David Gugick
Imceda Software
www.imceda.com

Alter Stored Procedure and Trigger

Hi All,
I need to modify some Stored Procedures and Triggers in production database
(SQL 2000). Can I use Edit object which means execute "Alter procedure..."
or "Alter Trigger..." directly? or I have to drop the existing SP or TR and
recreate them? Any difference between these 2 methods?
Thank you, JuliaThe net effect will be the same, the only difference will the the create
date will change for the drop/add whereas it will not change for the alter.
So dropping the sp/tr and recreating it will set the new create date for
tracking purposes, if you want to know when it was last changed. But about
the only difference.
"Julia" <Julia@.discussions.microsoft.com> wrote in message
news:F2D56079-CC8F-4F4A-A7ED-5417AF745EB7@.microsoft.com...
> Hi All,
> I need to modify some Stored Procedures and Triggers in production
database
> (SQL 2000). Can I use Edit object which means execute "Alter
procedure..."
> or "Alter Trigger..." directly? or I have to drop the existing SP or TR
and
> recreate them? Any difference between these 2 methods?
> Thank you, Julia
>|||Sorry, forgot, along with the drop/add, object ownership could change,
depending on who you are logged in as when you recreate the sp.
"Julia" <Julia@.discussions.microsoft.com> wrote in message
news:F2D56079-CC8F-4F4A-A7ED-5417AF745EB7@.microsoft.com...
> Hi All,
> I need to modify some Stored Procedures and Triggers in production
database
> (SQL 2000). Can I use Edit object which means execute "Alter
procedure..."
> or "Alter Trigger..." directly? or I have to drop the existing SP or TR
and
> recreate them? Any difference between these 2 methods?
> Thank you, Julia
>|||Julia wrote:
> Hi All,
> I need to modify some Stored Procedures and Triggers in production
> database (SQL 2000). Can I use Edit object which means execute
> "Alter procedure..." or "Alter Trigger..." directly? or I have to
> drop the existing SP or TR and recreate them? Any difference between
> these 2 methods?
> Thank you, Julia
Object IDs change on a drop/create. Use alter whenever possible.
--
David Gugick
Imceda Software
www.imceda.com

alter replication triggers

I have a customer application where I utilize merge replication between
sqlserver2000 and pocketpc's that are running sqlserverce2.0 (vb.net 2003).
It appears that the customer has some tables that they use to keep track of
who modifies the data in certain tables. they want the same functionality
from me.
Since I'm utilizing replication I'm not 100% sure how to accomplish this.
I looked at the trigger that replication creates for on insert . I was
thinking I could add the code necesary to populate the log table there, but
am afraid of screwing up the replication.
I'm not very strong on triggers, anybody have any ideas/suggestions?
Thanks
In SQL Server 2000 you can have multiple triggers on a table, so I'd just
create your own separate ones which can be added using sp_addscriptexec or
as part of the article's properties.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

All about Triggers...

Created my first trigger a day or two ago that inserts the current date
into a smalldatetime field WHEN another field is updated. My question:
why is it that one cannot update that field a second time using my
website front end? It's as if you enter in that field's info once and
then you're stuck with it... :/ All the other fields may be modified
at will except that one.
My trigger:
CREATE TRIGGER set_date_trig
ON lla
FOR UPDATE
AS
SET NOCOUNT ON
BEGIN
IF UPDATE(test)
UPDATE lla
SET test_date=GETDATE()
WHERE doc+poe IN (SELECT doc+poe FROM inserted)
END
Any ideas?Try,
...
UPDATE
a
SET
a.test_date=GETDATE()
from
lla as a
inner join
inserted as i
on a.doc = i.doc and a.poe = i.poe
AMB
"roy.anderson@.gmail.com" wrote:

> Created my first trigger a day or two ago that inserts the current date
> into a smalldatetime field WHEN another field is updated. My question:
> why is it that one cannot update that field a second time using my
> website front end? It's as if you enter in that field's info once and
> then you're stuck with it... :/ All the other fields may be modified
> at will except that one.
> My trigger:
> CREATE TRIGGER set_date_trig
> ON lla
> FOR UPDATE
> AS
> SET NOCOUNT ON
> BEGIN
> IF UPDATE(test)
> UPDATE lla
> SET test_date=GETDATE()
> WHERE doc+poe IN (SELECT doc+poe FROM inserted)
> END
>
> Any ideas?
>|||Since this is an UPDATE trigger any change you make to the column Test will
also overwrite Test_date. Maybe you just wanted a DEFAULT value for the
column? Example:
ALTER TABLE lla
ADD CONSTRAINT df_lla_test_date
DEFAULT CURRENT_TIMESTAMP FOR test_date
In your UPDATE:
...
WHERE doc+poe IN
This seems unlikely to be the correct or best way to correlate with the
Inserted table. Is (Doc,Poe) the key? If so:
UPDATE lla SET test_date = CURRENT_TIMESTAMP
WHERE EXISTS
(SELECT *
FROM Inserted AS I
WHERE I.doc = lla.doc
AND I.poe = lla.poe)
Concatenating (or adding!?) the two columns would otherwise not give your
query the full benefit of an index on these columns. Concatenating VARCHAR
columns in this way could also give you incorrect results.
David Portas
SQL Server MVP
--|||The UPDATE() function checks to see if the column is in the update
statement. It does NOT check to see if the value is changing from the
original value. I assume when you try your update statement from the
front-end to set the Test_Date to something expilicit it still sets it to
GetDate(). You may need something like this for your trigger. I'm assuming
your primary key is a combination of Doc and Poe...
CREATE TRIGGER set_date_trig
ON lla
FOR UPDATE
AS
SET NOCOUNT ON
BEGIN
UPDATE lla SET
lla.test_date = GETDATE()
FROM lla
INNER JOIN Inserted I ON lla.Doc = I.Doc AND lla.Poe = I.Poe
INNER JOIN Deleted D ON I.Doc = D.Doc AND I.Poe = D.Poe
WHERE I.Test <> D.Test
END
Paul
"roy.anderson@.gmail.com" wrote:

> Created my first trigger a day or two ago that inserts the current date
> into a smalldatetime field WHEN another field is updated. My question:
> why is it that one cannot update that field a second time using my
> website front end? It's as if you enter in that field's info once and
> then you're stuck with it... :/ All the other fields may be modified
> at will except that one.
> My trigger:
> CREATE TRIGGER set_date_trig
> ON lla
> FOR UPDATE
> AS
> SET NOCOUNT ON
> BEGIN
> IF UPDATE(test)
> UPDATE lla
> SET test_date=GETDATE()
> WHERE doc+poe IN (SELECT doc+poe FROM inserted)
> END
>
> Any ideas?
>