disable trigger through sqlcmd ?

• SQL Galaxy

  One Orange Chip

  Points: 29472

  More actions

  January 2, 2012 at 9:39 pm

  #262340

  Hi,

  I could not able to disable or drop trigger as below command, could anyone give me correct one.

  I have login through SQLCMD, SSMS does not connect due to error trigger execution..

  1> disable trigger Tr_serverLogon

  2> go

  Msg 102, Level 15, State 1, Server WJMN0120753\SQL2K8R2, Line 1

  Incorrect syntax near 'Tr_serverLogon'.

  1> sp_msforeachtable "alter table ? disable trigger all"

  2> go

  1> sp_msforeachtable alter table ? disable trigger all

  2> go

  Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : No value given for one

  or more required parameters..

  1> alter table serverlogonhistory disable trigger Tr1_serverLogon

  2> go

  Msg 4920, Level 16, State 1, Server WJMN0120753\SQL2K8R2, Line 1

  ALTER TABLE failed because trigger 'Tr1_serverLogon' on table 'serverlogonhistor

  y' does not exist.

  1> alter table dbo.serverlogonhistory disable trigger Tr1_serverLogon

  2> go

  Msg 4920, Level 16, State 1, Server WJMN0120753\SQL2K8R2, Line 1

  ALTER TABLE failed because trigger 'Tr1_serverLogon' on table 'serverlogonhistor

  y' does not exist.

  1> disable trigger Tr_serverLogon on dbo.serverLogonHistory

  2> go

  Msg 1088, Level 16, State 119, Server WJMN0120753\SQL2K8R2, Line 1

  Cannot find the object "Tr_serverLogon" because it does not exist or you do not

  have permissions.

  1>

  thanks

  ananda

• SQL Galaxy

  One Orange Chip

  Points: 29472

  More actions

  January 2, 2012 at 10:01 pm

  #1427546

  yes, I disabled the triggers

  Disabling all triggers that were defined with the same scope

  The following example disables all DDL triggers that were created at the server scope.

  SQL

  Copy

  USE AdventureWorks2008R2;

  GO

  DISABLE Trigger ALL ON ALL SERVER;

  GO