Search code examples
t-sqllinked-server

Disabling trigger in a linked server

I am trying to do a schema migration using PowerShell / TSQL but am having a few issues with using linked servers. Basically, the source DB backup is a higher version than that of the target SQL server so i am having to use linked servers to pull the data from the source database into a linked server database.

This is my first time using linked servers and one of the parts of the script i have is disabling certain triggers on the destination database to stop inserts from failing.

I figured out how to add columns using EXEC :

EXEC('ALTER TABLE [TRS].dbo.AM ADD testColum INT') AT S1

But the same process doesnt work for disabling triggers

EXEC('DISABLE TRIGGER tr_SetFinalVersion ON [TRS].dbo.Contract;') AT S1

Any idea how i would go about disabling these?

Solution

  • Tables from your examples are different. Is it possible that you don't have permissions on the other table?

    Also, you can try using ALTER TABLE to disable trigger. 

    EXEC('ALTER TABLE [TRS].[dbo].[Contract] DISABLE TRIGGER tr_SetFinalVersion') AT S1

    alter table trigger example