locked
Disable Trigger in Remote Server RRS feed

  • Question

  • Hi all,

    I have 2 sql server Database(Server1 and Server 2) and Server1 Static IP

    Normally we are disabling Trigger using below Query,

    ALTER TABLE InvTransaction DISABLE TRIGGER Tgr_UpdateTransactions

    But I wanto to Disable a Trigger in the Server1 by executing the Query in Server2 through Server1 Static IP.

    How can I do it? Pls help me

    Thanks

    Sanjaya

    Friday, May 4, 2012 1:18 PM

Answers

  • EXEC ('ALTER TABLE db.dbo.tbl DISABLE TRIGGER tri') AT SERVER

    Then again, disabling triggers is nothing you should do lightly. The trigger sits there for a reason.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, May 5, 2012 5:24 PM
  • EXEC sp_addlinkedserver@server='198.168.0.1',@provider='sqlncli'

    etc


    (Twitter | Blog)

    Friday, May 4, 2012 1:53 PM

All replies

  • Never tried it, but I think you can use EXECUTE 'my sql script' AT linked_server_name. To do this, you'd need to create a linked server first.

    (Twitter | Blog)

    Friday, May 4, 2012 1:33 PM
  • thanks for ur reply.

    How to create linked server using Static IP?

    Friday, May 4, 2012 1:41 PM
  • EXEC sp_addlinkedserver@server='198.168.0.1',@provider='sqlncli'

    etc


    (Twitter | Blog)

    Friday, May 4, 2012 1:53 PM
  • I connected to the Linked server using its static IP.

    Before Inserting data in the linked server i want to disable its Trigger and Constrains.

    So how can I disable from the current server?

    Thanks in advance.

    Saturday, May 5, 2012 12:51 PM
  • Hi Sanjaya,

    To disable trigger, use TSQL command like ;

    DISABLE TRIGGER Person.uAddress ON Person.Address;

    Please note tha To disable a DML trigger, at a minimum, a user must have ALTER permission on the table or view on which the trigger was created.

    http://msdn.microsoft.com/en-us/library/ms189748.aspx

    To disable the constraints , You will need to know what those contraints are before hand and write the static SQL in the same batch. If you don't know what the constraints are, or if they can change - use dynamic SQL t construct the commands and then use sp_executesql to run it.

    The following example shows how to disable all constraints on a table.

    --disable all constraints for the Sales.SalesOrderHeader table 
    ALTER
    TABLE Sales.SalesOrderHeader NOCHECK CONSTRAINT ALL

    --do something --enable all constraints for the Sales.SalesOrderHeader table
    ALTER TABLE Sales.SalesOrderHeader CHECK CONSTRAINT ALL

    http://weblogs.sqlteam.com/joew/archive/2008/10/01/60719.aspx

    Hppe this helps !


    Sanil Mhatre | Database Developer | MCTS | If you find my reply useful in any way, please vote it as helpful. If it has helped answer your question, please mark it as Answer. http://sqlwithsanil.com


    • Edited by Sanil Mhatre Saturday, May 5, 2012 2:19 PM added links
    Saturday, May 5, 2012 2:18 PM
  • EXEC ('ALTER TABLE db.dbo.tbl DISABLE TRIGGER tri') AT SERVER

    Then again, disabling triggers is nothing you should do lightly. The trigger sits there for a reason.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, May 5, 2012 5:24 PM