Disconnection when 'xp_cmd' is in a comment

If I run the following in Query Analyzer against Microsoft SQL Server
2000 - 8.00.818 while logged in as 'sa':
ALTER PROCEDURE __Test AS
BEGIN
/*
-- x p_cmd
*/
RETURN 0
END
GO
then all is well, but if I remove the space between the 'x' and the
'p' in the comment line then I get the following error:
[Microsoft][ODBC SQL Server Driver][TCP/IP
Sockets]ConnectionCheckForData (CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
ODBC: Msg 0, Level 16, State 1
Communication link failure
ODBC: Msg 0, Level 16, State 1
Communication link failure
Connection Broken
I am connecting remotely. Another user who is on the same domain as
the server does not have this problem. I assume some hardware or
software on the connection between the server and myself and the
server is causing the problem.
Does anyone have any idea what this could be?
Thanks
SylvesterIf it's hardware or software connectivity between you and
the server then you'd likely find something in the windows
event logs so you may want to start checking those.
-Sue
On 10 Oct 2004 22:01:39 -0700, junk@.riddell.co.nz
(Sylvester) wrote:

>If I run the following in Query Analyzer against Microsoft SQL Server
>2000 - 8.00.818 while logged in as 'sa':
>
> ALTER PROCEDURE __Test AS
> BEGIN
> /*
> -- x p_cmd
> */
> RETURN 0
> END
> GO
>then all is well, but if I remove the space between the 'x' and the
>'p' in the comment line then I get the following error:
> [Microsoft][ODBC SQL Server Driver][TCP/IP
>Sockets]ConnectionCheckForData (CheckforData()).
> Server: Msg 11, Level 16, State 1, Line 0
> General network error. Check your network documentation.
> ODBC: Msg 0, Level 16, State 1
> Communication link failure
> ODBC: Msg 0, Level 16, State 1
> Communication link failure
> Connection Broken
>
>I am connecting remotely. Another user who is on the same domain as
>the server does not have this problem. I assume some hardware or
>software on the connection between the server and myself and the
>server is causing the problem.
>Does anyone have any idea what this could be?
>Thanks
>Sylvester

Disconnection when 'xp_cmd' is in a comment

If I run the following in Query Analyzer against Microsoft SQL Server
2000 - 8.00.818 while logged in as 'sa':
ALTER PROCEDURE __Test AS
BEGIN
/*
-- x p_cmd
*/
RETURN 0
END
GO
then all is well, but if I remove the space between the 'x' and the
'p' in the comment line then I get the following error:
[Microsoft][ODBC SQL Server Driver][TCP/IP
Sockets]ConnectionCheckForData (CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
ODBC: Msg 0, Level 16, State 1
Communication link failure
ODBC: Msg 0, Level 16, State 1
Communication link failure
Connection Broken
I am connecting remotely. Another user who is on the same domain as
the server does not have this problem. I assume some hardware or
software on the connection between the server and myself and the
server is causing the problem.
Does anyone have any idea what this could be?
Thanks
SylvesterIf it's hardware or software connectivity between you and
the server then you'd likely find something in the windows
event logs so you may want to start checking those.
-Sue
On 10 Oct 2004 22:01:39 -0700, junk@.riddell.co.nz
(Sylvester) wrote:
>If I run the following in Query Analyzer against Microsoft SQL Server
>2000 - 8.00.818 while logged in as 'sa':
>
> ALTER PROCEDURE __Test AS
> BEGIN
> /*
> -- x p_cmd
> */
> RETURN 0
> END
> GO
>then all is well, but if I remove the space between the 'x' and the
>'p' in the comment line then I get the following error:
> [Microsoft][ODBC SQL Server Driver][TCP/IP
>Sockets]ConnectionCheckForData (CheckforData()).
> Server: Msg 11, Level 16, State 1, Line 0
> General network error. Check your network documentation.
> ODBC: Msg 0, Level 16, State 1
> Communication link failure
> ODBC: Msg 0, Level 16, State 1
> Communication link failure
> Connection Broken
>
>I am connecting remotely. Another user who is on the same domain as
>the server does not have this problem. I assume some hardware or
>software on the connection between the server and myself and the
>server is causing the problem.
>Does anyone have any idea what this could be?
>Thanks
>Sylvester

Disabling triggers with alter table, execute as?

I'm disabling some triggers in SQL Server 2000 with the following command
launched from another trigger:
ALTER TABLE PM00300 DISABLE TRIGGER insPM00300
I want normal users to be able to run that command. Is there a kind of
"execute as" I can use in SQL Server 2000 to do it?
Thanks.Before I answer: are you sure you want to do this? The entire point of a
trigger is to fire regardless of user control. Be very careful here. I'm
going to lay out an option in 2000, but I don't endorse it's usage (I'm
presenting it simply because I don't know exactly what you're business goal
is - it may be perfectly valid).
Technically you could create a job that disables the trigger, then give
users access to the job and let them call it from within a stored procedure
or such. Be sure not to create a schedule for the job. With a SQL Agent job,
you can control (somewhat) the execution authority.
"b3nny80y" <b3nny80y@.discussions.microsoft.com> wrote in message
news:FF0D789D-3DE5-4354-B5A2-0AAE27CAFFB7@.microsoft.com...
> I'm disabling some triggers in SQL Server 2000 with the following command
> launched from another trigger:
> ALTER TABLE PM00300 DISABLE TRIGGER insPM00300
> I want normal users to be able to run that command. Is there a kind of
> "execute as" I can use in SQL Server 2000 to do it?
> Thanks.|||Is that wise?
For the user to execute an ALTER table, they will have to be provided table
ownership privileges. That means that the users can alter the table anyway
they desire -DROP columns, ADD columns, DROP TABLE, etc.
If you are trying to stop nested Triggers, there is another way. Use
sp_configure to turn on/off the Using Nested Triggers configuration option.
(Or use Server properties in Enterprise Manager.)
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"b3nny80y" <b3nny80y@.discussions.microsoft.com> wrote in message
news:FF0D789D-3DE5-4354-B5A2-0AAE27CAFFB7@.microsoft.com...
> I'm disabling some triggers in SQL Server 2000 with the following command
> launched from another trigger:
> ALTER TABLE PM00300 DISABLE TRIGGER insPM00300
> I want normal users to be able to run that command. Is there a kind of
> "execute as" I can use in SQL Server 2000 to do it?
> Thanks.|||b3nny80y wrote:
> I'm disabling some triggers in SQL Server 2000 with the following command
> launched from another trigger:
> ALTER TABLE PM00300 DISABLE TRIGGER insPM00300
> I want normal users to be able to run that command. Is there a kind of
> "execute as" I can use in SQL Server 2000 to do it?
> Thanks.
Why would you want to do this? Please explain further what you're
trying to accomplish. If you're trying to disable the triggers for
specific users, you can accomplish the same effect by putting IF/ELSE
code in the trigger that checks the current username, and simply doesn't
execute the code in the trigger.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Disabling triggers with alter table, execute as?

I'm disabling some triggers in SQL Server 2000 with the following command
launched from another trigger:
ALTER TABLE PM00300 DISABLE TRIGGER insPM00300
I want normal users to be able to run that command. Is there a kind of
"execute as" I can use in SQL Server 2000 to do it?
Thanks.Before I answer: are you sure you want to do this? The entire point of a
trigger is to fire regardless of user control. Be very careful here. I'm
going to lay out an option in 2000, but I don't endorse it's usage (I'm
presenting it simply because I don't know exactly what you're business goal
is - it may be perfectly valid).
Technically you could create a job that disables the trigger, then give
users access to the job and let them call it from within a stored procedure
or such. Be sure not to create a schedule for the job. With a SQL Agent job,
you can control (somewhat) the execution authority.
"b3nny80y" <b3nny80y@.discussions.microsoft.com> wrote in message
news:FF0D789D-3DE5-4354-B5A2-0AAE27CAFFB7@.microsoft.com...
> I'm disabling some triggers in SQL Server 2000 with the following command
> launched from another trigger:
> ALTER TABLE PM00300 DISABLE TRIGGER insPM00300
> I want normal users to be able to run that command. Is there a kind of
> "execute as" I can use in SQL Server 2000 to do it?
> Thanks.|||Is that wise?
For the user to execute an ALTER table, they will have to be provided table
ownership privileges. That means that the users can alter the table anyway
they desire -DROP columns, ADD columns, DROP TABLE, etc.
If you are trying to stop nested Triggers, there is another way. Use
sp_configure to turn on/off the Using Nested Triggers configuration option.
(Or use Server properties in Enterprise Manager.)
--
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"b3nny80y" <b3nny80y@.discussions.microsoft.com> wrote in message
news:FF0D789D-3DE5-4354-B5A2-0AAE27CAFFB7@.microsoft.com...
> I'm disabling some triggers in SQL Server 2000 with the following command
> launched from another trigger:
> ALTER TABLE PM00300 DISABLE TRIGGER insPM00300
> I want normal users to be able to run that command. Is there a kind of
> "execute as" I can use in SQL Server 2000 to do it?
> Thanks.|||b3nny80y wrote:
> I'm disabling some triggers in SQL Server 2000 with the following command
> launched from another trigger:
> ALTER TABLE PM00300 DISABLE TRIGGER insPM00300
> I want normal users to be able to run that command. Is there a kind of
> "execute as" I can use in SQL Server 2000 to do it?
> Thanks.
Why would you want to do this? Please explain further what you're
trying to accomplish. If you're trying to disable the triggers for
specific users, you can accomplish the same effect by putting IF/ELSE
code in the trigger that checks the current username, and simply doesn't
execute the code in the trigger.
--
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Disabling triggers

Hi,
Does ALTER TABLE ... DISABLE TRIGGER ALL guarantee that disabled triggers
won't fire under any circumstances?
MS SQL Server 2000 SP3.
-- Many thanks.Yes, did work for me all the time.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--

Disabling triggers

Hi,
Does ALTER TABLE ... DISABLE TRIGGER ALL guarantee that disabled triggers
won't fire under any circumstances?
MS SQL Server 2000 SP3.
-- Many thanks.Yes, did work for me all the time.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--

Disabling Trigger within a trigger?

Hi all,

I would like to disable a trigger within a trigger using...

ALTER TABLE RESIDENTIAL DISABLE TRIGGER UPDATE_SYSTEMS_RES

However it tells me that I cant disable a trigger within one. How can I get around this or is there another way of disabling a trigger.

Thanks
AnthonyYou will need to alter the code of your trigger to test if the trigger should do it's thing or bail. To my knowledge there is no way to disable a trigger from fireing while inside a trigger.

Disabling primary key

Hi
i have a table with primary key defined on col1 and col2. now i want to
have col3 also included in primary key. when i alter the table it gives
me error for duplicate rows. there is an option for 'with nocheck' but
it only works with check or foreign key constraint. is there any option
in sql server like in oracle 'no validate' which doesnt validate the
existing data and force the data validation from new records.
thanx
Farid

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!"Ghulam Farid" <gfaryd@.yahoo.com> wrote in message
news:40b49d58$0$209$75868355@.news.frii.net...
>
> Hi
> i have a table with primary key defined on col1 and col2. now i want to
> have col3 also included in primary key. when i alter the table it gives
> me error for duplicate rows. there is an option for 'with nocheck' but
> it only works with check or foreign key constraint. is there any option
> in sql server like in oracle 'no validate' which doesnt validate the
> existing data and force the data validation from new records.
> thanx
> Farid
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

No - CHECK/NOCHECK is for foreign keys and check constraints only. I'm not
entirely sure I understand your post - are you saying that you want to allow
duplicate values in a primary key? If so, then it's not possible, and
shouldn't be. Perhaps if you can post some more details (the table DDL and
sample data), someone may be able to suggest an alternative approach.

Simon|||"Simon Hayes" <sql@.hayes.ch> wrote in message
news:40b4d73d_1@.news.bluewin.ch...
> "Ghulam Farid" <gfaryd@.yahoo.com> wrote in message
> news:40b49d58$0$209$75868355@.news.frii.net...
> > Hi
> > i have a table with primary key defined on col1 and col2. now i want to
> > have col3 also included in primary key. when i alter the table it gives
> > me error for duplicate rows. there is an option for 'with nocheck' but
> > it only works with check or foreign key constraint. is there any option
> > in sql server like in oracle 'no validate' which doesnt validate the
> > existing data and force the data validation from new records.
> > thanx
> > Farid
> > *** Sent via Developersdex http://www.developersdex.com ***
> > Don't just participate in USENET...get rewarded for it!
> No - CHECK/NOCHECK is for foreign keys and check constraints only. I'm not
> entirely sure I understand your post - are you saying that you want to
allow
> duplicate values in a primary key? If so, then it's not possible, and
> shouldn't be. Perhaps if you can post some more details (the table DDL and
> sample data), someone may be able to suggest an alternative approach.

I think he wants: SET IDENTITY_INSERT.

> Simon|||>> I have a table with primary key defined on col1 and col2. Now I
want to
have col3 also included in primary key. When I alter the table it
gives me error for duplicate rows. <<

That does not make sense to me. Given this

CREATE TABLE Foobar
(col1 INTEGER NOT NULL,
col2 INTEGER NOT NULL,
col3 INTEGER NOT NULL,
PRIMARY KEY (col1, col2),
..);

Then a superkey is still unique:

CREATE TABLE Foobar
(col1 INTEGER NOT NULL,
col2 INTEGER NOT NULL,
col3 INTEGER NOT NULL,
PRIMARY KEY (col1, col2, col3),
..);|||i think u people didn't understand the problem.
i have created table
create table test(col1 int, col2 int, col3 int,col4...)
Primary key(col1,col2)
as there is primary key on col1 and col2 no duolicate data can exist in
them now the scenario changed i have to change the primary key on the
table. now when i alter the table
alter table test primary key (col1, col3, col4) it gives me error
duplicate rows exist.
but in my scenario i want the existing duplication to remain in the
table. and the primary key enforcement starts from new data.
in oracle there is an option of 'no validate' which doesnt check the
existing data in the table but enforce the uniqueness of data from new
records. i want to know is there any option available in sql server
which doesnt check the existing data but enforces the uniqueness of
records from new records.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Ghulam Farid <gfaryd@.yahoo.com> wrote in message news:<40b57e55$0$207$75868355@.news.frii.net>...
> i think u people didn't understand the problem.
> i have created table
> create table test(col1 int, col2 int, col3 int,col4...)
> Primary key(col1,col2)
> as there is primary key on col1 and col2 no duolicate data can exist in
> them now the scenario changed i have to change the primary key on the
> table. now when i alter the table
> alter table test primary key (col1, col3, col4) it gives me error
> duplicate rows exist.
> but in my scenario i want the existing duplication to remain in the
> table. and the primary key enforcement starts from new data.
> in oracle there is an option of 'no validate' which doesnt check the
> existing data in the table but enforce the uniqueness of data from new
> records. i want to know is there any option available in sql server
> which doesnt check the existing data but enforces the uniqueness of
> records from new records.
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

It sounds like you want to place a primary key on columns which
contain duplicates, but you want to ignore those duplicates and only
enforce the primary key for new values? If so, then it's not possible
- all values in a primary key must always be unique, otherwise it
couldn't be a primary key.

Simon

create table dbo.Test (
col1 int not null,
col2 int not null,
col3 int not null,
col4 int not null,
constraint PK_Test primary key (col1, col2)
)
go

insert into dbo.Test
select 1,1,1,1
union all
select 1,2,1,1
union all
select 3,1,1,1
go

alter table dbo.Test
drop constraint PK_Test

-- This will always fail because of duplicate data
alter table dbo.Test
add constraint PK_Test primary key (col1, col3, col4)
go

drop table dbo.Test
go

Disabling constraints

Hi,
I need to disable all the foreign key and primary key constraints in a table
and reenable them at the end of my commands. I can use ALTER TABLE to do
this but I was wondering if it is a good practise. It is not maintenance
code but production code. Everything is done in a transaction but can I be
sure there is no incidence with concurent accesses ? What about the rights
for the db user ?
Thanks.
FredFrdric Mayot wrote:
> Hi,
> I need to disable all the foreign key and primary key constraints in
> a table and reenable them at the end of my commands. I can use ALTER
> TABLE to do this but I was wondering if it is a good practise. It is
> not maintenance code but production code. Everything is done in a
> transaction but can I be sure there is no incidence with concurent
> accesses ? What about the rights for the db user ?
> Thanks.
> Fred
Why would you need to disable constraints in production code?
David Gugick
Quest Software
www.imceda.com
www.quest.com|||The reason is quite simple.
Assume we have the tables
A(pkA <PK> )
B(pkfkA <PK,FK>, pkfkC <PK,FK> )
C(pkC <PK> )
with the data
A = {1}
C = {11, 12}
B = {(1, 11), (1, 12)}
Now, I want to update the two rows in B in the following manner :
(1, 11) -> (1, 12) and (1, 12) -> (1, 11).
This operation is supposed to be atomic (that's to say in a transaction)
"David Gugick" <david.gugick-nospam@.quest.com> a crit dans le message de
news: %23$J$Y0pbFHA.3040@.TK2MSFTNGP14.phx.gbl...
> Frdric Mayot wrote:
> Why would you need to disable constraints in production code?
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com|||Have you tried this?
UPDATE B
SET pkfkC = CASE pkfkC
WHEN 11 THEN 12
WHEN 12 THEN 11
END
WHERE pkfkA = 1 AND pkfkC IN (11, 12)
This update is atomic and as such doesn't cause a constraint violation error
.
"Frédéric Mayot" wrote:

> The reason is quite simple.
> Assume we have the tables
> A(pkA <PK> )
> B(pkfkA <PK,FK>, pkfkC <PK,FK> )
> C(pkC <PK> )
> with the data
> A = {1}
> C = {11, 12}
> B = {(1, 11), (1, 12)}
> Now, I want to update the two rows in B in the following manner :
> (1, 11) -> (1, 12) and (1, 12) -> (1, 11).
> This operation is supposed to be atomic (that's to say in a transaction)
>
> "David Gugick" <david.gugick-nospam@.quest.com> a écrit dans le message de
> news: %23$J$Y0pbFHA.3040@.TK2MSFTNGP14.phx.gbl...
>
>

Disable Triggers (using alter with public)

Hey folks,

Here's the skinny
I'm trying to run a stored procedure that requires triggers to be disabled in a table.

When I run the stored procedure (from the app or from the query analyzer) it works fine cause I'm setup as the owner/admin.
However, when users call the stored procedure from the application it gets executed as public which does not have alter persmissions.
I do not want to give alter permissions to public (for obvious reasons).

I was thinking of using SETUSER and SETOWNER but I don't know if it will work (I think because I cannot upgrade permissions using these just change to user of equal or lesser permissions).

Someone had mentioned to me to create a sysadmin user and just SETUSER to that then set it back to public, but again not sure if this will work because public has less permissions than the sysadmin.

I would like to have more info before I try this out and give it back to the client for testing.
Not sure if any of this will work for me, please help.
Thank you for your time.

By the way, I'm using SQL2000My suggestion would be to code an exemption for that stored procedure into your trigger, something like:IF 'myProc' = Object_Name(@.@.procid)
RETURN
-PatP|||Perfect, Exactly what I was looking for.
Thanks a million.

Disable Trigger Question

Here is a trigger in a table that I am working on. Call it tbl1. AT the
bottom of the trigger is an Alter Table statement which looks like it
disables the trigger. My question is for the purpose of this Alter Table
statement at the end of the trigger. I am not familiar with the logic. Doe
s
this run the trigger and then disable it? Or does this statement disable th
e
trigger altogether? If the trigger runs and then gets disabled, what would
be the purpose of doing that?
---
CREATE TRIGGER t_UpdateTbl1 ON dbo.tbl1
For INSERT, UPDATE
AS
update tbl1
set CoID = c.CoID
from tbl1 s inner join Inserted i on (i.RecordID = s.RecordID)
where i.CoID <> c.CoID
alter table [dbo].[tbl1] disable trigger [t_UpdateTbl1]
----
Thanks,
RichWell, I just tried it out on a test trigger and I was able to disable the
trigger this way.
"Rich" wrote:

> Here is a trigger in a table that I am working on. Call it tbl1. AT the
> bottom of the trigger is an Alter Table statement which looks like it
> disables the trigger. My question is for the purpose of this Alter Table
> statement at the end of the trigger. I am not familiar with the logic. D
oes
> this run the trigger and then disable it? Or does this statement disable
the
> trigger altogether? If the trigger runs and then gets disabled, what woul
d
> be the purpose of doing that?
> ---
> CREATE TRIGGER t_UpdateTbl1 ON dbo.tbl1
> For INSERT, UPDATE
> AS
> update tbl1
> set CoID = c.CoID
> from tbl1 s inner join Inserted i on (i.RecordID = s.RecordID)
> where i.CoID <> c.CoID
> alter table [dbo].[tbl1] disable trigger [t_UpdateTbl1]
> ----
> Thanks,
> Rich|||It looks as if somebody was trying to "manually" avoid what is known as
trigger recursion: a trigger repeatedly calling itself. The trigger modifies
the table that it is defined on, so you would think that it would keep
calling itself in an infinite loop.
SQL server has options for trigger recursion (triggers making changes that
cause themselves to fire again), and nesting (triggers making changes that
fire other triggers). In SQL 2000, trigger recursion is disabled by default.
Even with it enabled, a trigger would call itself a maximum of 32 times.
Recursive triggers are enabled or disabled with ALTER DATABASE .
In SQL 2000, that trigger wouldn't have worked anyway, because DDL
statements were not permitted in triggers. In SQL 2005, they are.
"Rich" wrote:

> Here is a trigger in a table that I am working on. Call it tbl1. AT the
> bottom of the trigger is an Alter Table statement which looks like it
> disables the trigger. My question is for the purpose of this Alter Table
> statement at the end of the trigger. I am not familiar with the logic. D
oes
> this run the trigger and then disable it? Or does this statement disable
the
> trigger altogether? If the trigger runs and then gets disabled, what woul
d
> be the purpose of doing that?
> ---
> CREATE TRIGGER t_UpdateTbl1 ON dbo.tbl1
> For INSERT, UPDATE
> AS
> update tbl1
> set CoID = c.CoID
> from tbl1 s inner join Inserted i on (i.RecordID = s.RecordID)
> where i.CoID <> c.CoID
> alter table [dbo].[tbl1] disable trigger [t_UpdateTbl1]
> ----
> Thanks,
> Rich|||> In SQL 2000, that trigger wouldn't have worked anyway, because DDL
> statements were not permitted in triggers. In SQL 2005, they are.
Incorrect - this works on sql server 2000.|||Scott Morris" wrote:

> Incorrect - this works on sql server 2000.
>
I stand corrected; however, what's the point of a one-time trigger? If
trigger recursion was even enabled in the first place, wouldn't it be much
better to accomplish the desired action by checking @.@.NESTLEVEL?|||> I stand corrected; however, what's the point of a one-time trigger? If
> trigger recursion was even enabled in the first place, wouldn't it be much
> better to accomplish the desired action by checking @.@.NESTLEVEL?
Indeed - what is the point of such a trigger! That would be a question for
the original programmer. I would fire someone in my employ that did such a
thing - especially since there is no comment in the code to indicate the
reason.|||Create the table and the trigger, look at the trigger code, then
disable the trigger and look at the trigger code again
surprise!!!! last line in the trigger code is "alter table
[dbo].[TestTrigger] disable trigger [trTest]"
So this is not a one time thing, this happens when you disable a
trigger, run code provided to test it out
CREATE TABLE TestTrigger (TestID INT identity,
name VARCHAR(20),
value DECIMAL(12,2) ,
CONSTRAINT chkPositiveValue CHECK (value > 0.00) )
INSERT INTO TestTrigger
SELECT 'SQL',500.23
GO
CREATE TRIGGER trTest
ON TestTrigger
FOR UPDATE
AS
IF @.@.ROWCOUNT =0
RETURN
IF UPDATE(value)
BEGIN
SELECT '1', * FROM deleted d JOIN inserted i ON d.testid =i.testid
SELECT '2',* FROM deleted d JOIN inserted i ON d.testid =i.testid
AND i.value <> d.value
SELECT '3',* FROM deleted d JOIN inserted i ON d.testid =i.testid
AND COALESCE(i.value,-1) <> COALESCE(d.value,-1)
END
GO
alter table TestTrigger
disable trigger trTest
Look at the trigger code again
BTW
sp_helptext trTest won't show this, you have to do it from manage
triggers after you right click on the table
http://sqlservercode.blogspot.com/|||On Thu, 2 Feb 2006 11:04:58 -0800, Rich wrote:

>Here is a trigger in a table that I am working on. Call it tbl1. AT the
>bottom of the trigger is an Alter Table statement which looks like it
>disables the trigger. My question is for the purpose of this Alter Table
>statement at the end of the trigger.
Hi Rich,
None - having a trigger that runs only once, then disables itself has no
purpose at all.
The script posted by "SQL" reveals the bug that caused this alter table
statement to be in the trigger.
If you use Enterprise Manager to manage a trigger (right-click a table;
select All Tasks / Manage Triggers), you'll get a window in which is
some generated code that will recreate a trigger in it's current state.
For a normal trigger, that would be:
CREATE TRIGGER name
ON table
FOR UPDATE (or whatever)
AS
body of the trigger
And if the trigger is currently disabled, that should be extended to
CREATE TRIGGER name
ON table
FOR UPDATE (or whatever)
AS
body of the trigger
GO
ALTER TABLE table DISABLE TRIGGER name
Unfortunately, there seems to be a bug in Enterpris Manager - it omits
the batch seperator ("GO") in the generated code. That makes a huge
difference. With the GO, the trigger gets recreated in it's original
form, then (in a seperate batch) disabled. Without the GO, the trigger
gets recreated with an extra ALTER TABLE statement tacked on at the end
of the code, and is kept in an enabled state. Of course, after being
fired once, the trigger will be disabled again, due to the execution of
the extra ALTER TABLE statement.
It really gets funny if you repeat the exercise a few times. Starting
from the code posted by "SQL", I have now a trigger with this code:
CREATE TRIGGER trTest
ON TestTrigger
FOR UPDATE
AS
(snip)
alter table [dbo].[TestTrigger] disable trigger [trTest]
alter table [dbo].[TestTrigger] disable trigger [trTest]
alter table [dbo].[TestTrigger] disable trigger [trTest]
(I omitted the blank lines to keep the post readable).
To fix this, take the following steps:
1. Find out if the trigger needs to be enabled or disabled in your
applications. If disabled, you might want to check if it will ever be
needed again.
2. Copy the current code of the trigger into Query Analyzer, remove the
ALTER TABLE statement and recreate the trigger. Then, if necessary,
disable it. (Doon't forget to add a GO statement if you intend to do
this all in one script <g> ).
3. Make a mental note to never ever use Enterprise Manager again for
creating or changing triggers. (Same goes for lots of other tasks too;
EM is really only a DBA tool; development work should be done in Query
Analyzer).
Hugo Kornelis, SQL Server MVP

Disable logging

is there a option to disable logging during alter column
scripts...?
what are the events can be disabled from logging...?
What exactly is your ALTER table command like? Can you post it here?
Generally, when you run an ALTER TABLE...ALTER COLUMN on a column, SQL
Server updates that column, to make sure the new column datatype is
compatible with the current data, and this results in logging. You cannot
disable transaction logging.
Your other alternative, could be adding a new column and updating the new
column with the old columns values, in smaller batches. Once the new column
is populated, drop the old column and rename the new.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
<anonymous@.discussions.microsoft.com> wrote in message
news:6cf901c4837c$609aaf30$a601280a@.phx.gbl...
is there a option to disable logging during alter column
scripts...?
what are the events can be disabled from logging...?
|||> what are the events can be disabled from logging...?
SELECT INTO and other bulk operations are minimally logged in the SIMPLE or
BULK LOGGED recovery model. To provide ALTER TABLE functionality, you can
create and load a new table using one of these techniques and then drop the
old table and recreate constraints and indexes.
Hope this helps.
Dan Guzman
SQL Server MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:6cf901c4837c$609aaf30$a601280a@.phx.gbl...
> is there a option to disable logging during alter column
> scripts...?
> what are the events can be disabled from logging...?

Disable logging

is there a option to disable logging during alter column
scripts...?
what are the events can be disabled from logging...?What exactly is your ALTER table command like? Can you post it here?
Generally, when you run an ALTER TABLE...ALTER COLUMN on a column, SQL
Server updates that column, to make sure the new column datatype is
compatible with the current data, and this results in logging. You cannot
disable transaction logging.
Your other alternative, could be adding a new column and updating the new
column with the old columns values, in smaller batches. Once the new column
is populated, drop the old column and rename the new.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
<anonymous@.discussions.microsoft.com> wrote in message
news:6cf901c4837c$609aaf30$a601280a@.phx.gbl...
is there a option to disable logging during alter column
scripts...?
what are the events can be disabled from logging...?|||> what are the events can be disabled from logging...?
SELECT INTO and other bulk operations are minimally logged in the SIMPLE or
BULK LOGGED recovery model. To provide ALTER TABLE functionality, you can
create and load a new table using one of these techniques and then drop the
old table and recreate constraints and indexes.
--
Hope this helps.
Dan Guzman
SQL Server MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:6cf901c4837c$609aaf30$a601280a@.phx.gbl...
> is there a option to disable logging during alter column
> scripts...?
> what are the events can be disabled from logging...?

Disable logging

is there a option to disable logging during alter column
scripts...?
what are the events can be disabled from logging...?What exactly is your ALTER table command like? Can you post it here?
Generally, when you run an ALTER TABLE...ALTER COLUMN on a column, SQL
Server updates that column, to make sure the new column datatype is
compatible with the current data, and this results in logging. You cannot
disable transaction logging.
Your other alternative, could be adding a new column and updating the new
column with the old columns values, in smaller batches. Once the new column
is populated, drop the old column and rename the new.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
<anonymous@.discussions.microsoft.com> wrote in message
news:6cf901c4837c$609aaf30$a601280a@.phx.gbl...
is there a option to disable logging during alter column
scripts...?
what are the events can be disabled from logging...?|||> what are the events can be disabled from logging...?
SELECT INTO and other bulk operations are minimally logged in the SIMPLE or
BULK LOGGED recovery model. To provide ALTER TABLE functionality, you can
create and load a new table using one of these techniques and then drop the
old table and recreate constraints and indexes.
Hope this helps.
Dan Guzman
SQL Server MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:6cf901c4837c$609aaf30$a601280a@.phx.gbl...
> is there a option to disable logging during alter column
> scripts...?
> what are the events can be disabled from logging...?

disable constraints

Hi,
I disabled all constraints on a database before using DTS by running the
following command:
exec sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
When comparing the source tables and the destination tables after the DTS I
found that some DEFAULT constraints were not disabled. The result was that
source columns populated with NULLs got populated on the destinatin columns
with the dafult values as defined in the constraints.
Thanks,
YanivYou cannot disable DEFAULT, UNIQUE or PRIMARY KEY constraints.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Yaniv" <yanive@.rediffmail.com> wrote in message news:ePf1C8o7FHA.4076@.tk2msftngp13.phx.gbl
..
> Hi,
> I disabled all constraints on a database before using DTS by running the f
ollowing command:
> exec sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
> When comparing the source tables and the destination tables after the DTS
I found that some
> DEFAULT constraints were not disabled. The result was that source columns
populated with NULLs got
> populated on the destinatin columns with the dafult values as defined in t
he constraints.
>
> Thanks,
> Yaniv
>|||Hi, Yaniv
I think you should have first to drop contstraint (DEFAULT in your case) ,
insert the data and then re-create contraints
"Yaniv" <yanive@.rediffmail.com> wrote in message
news:ePf1C8o7FHA.4076@.tk2msftngp13.phx.gbl...
> Hi,
> I disabled all constraints on a database before using DTS by running the
> following command:
> exec sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
> When comparing the source tables and the destination tables after the DTS
> I found that some DEFAULT constraints were not disabled. The result was
> that source columns populated with NULLs got populated on the destinatin
> columns with the dafult values as defined in the constraints.
>
> Thanks,
> Yaniv
>|||Thank you all,
This is what I did; I droped and recreated the defaults but I wanted to
find out why a default constraint was not diabled.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OjQBmIp7FHA.3752@.tk2msftngp13.phx.gbl...
> Hi, Yaniv
> I think you should have first to drop contstraint (DEFAULT in your case)
> , insert the data and then re-create contraints
>
>
>
>
> "Yaniv" <yanive@.rediffmail.com> wrote in message
> news:ePf1C8o7FHA.4076@.tk2msftngp13.phx.gbl...
>|||See my earlier reply. You cannot disable default constraints. I.e., default,
pk and uq constraints
are not disabled when you disable "ALL" constraints for a table.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Yaniv" <yanive@.rediffmail.com> wrote in message news:OPF1Tfp7FHA.3388@.TK2MSFTNGP11.phx.gbl
..
> Thank you all,
> This is what I did; I droped and recreated the defaults but I wanted to f
ind out why a default
> constraint was not diabled.
>
> --
> "Uri Dimant" <urid@.iscar.co.il> wrote in message news:OjQBmIp7FHA.3752@.tk2
msftngp13.phx.gbl...
>|||hi guys try the attached file to dynamically drop a default constraint on a
column in a table.