[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 508
  • Last Modified:

SQL Script to Set Triggers not for replication

I am setting up Transactional replication, I need to set all the triggers in the production database to Not for Replication. At the moment I am using the Alter Trigger command to add the not for replication option for each trigger. But there is like 250 Triggers , is there a faster way or any sp i can use to make my life easiler now and in the future ?

many Thanks

2 Solutions
select the triggers to file from query anal
like below

SELECT  '/*****   '+sysobjects.name+'   *****/'+Char(13)+
      syscomments.text +Char(13)+'Zx~@#zxZ'+char(13)
      char(13)+'GO'+CHAR(13)AS [Trigger Definition]
      sysobjects INNER JOIN syscomments ON sysobjects.id = syscomments.id
      (sysobjects.xtype = 'TR')

then in my favorite editor (textpad)
replace the "NOT FOR REPLICATION" with nothing
and then replace all of the ''Zx~@#zxZ'' with FOR REPLICATION

use something that you wont have in your triggers
i generally use some ascii codes above 128 to search and relace
but thats up to you

save the fixed script and run it (preferably on a test db first)

This works better for me than other solutions.  Additionally, with this code you're free to do other things to the trigger code if you want.

' ExportTriggers.vbs
' VBScript to export all trigger scripts from a SQL Server database
' Written By: Lynn Ransdell
' invoke like this:  cscript ExportTriggers.vbs > AllTriggers.sql

Dim server   :  server = "server\instance"
Dim dbname   :  dbname = "testdb"
Dim user     :  user   = "sa"
Dim pswd     :  pswd   = "secret"
Dim conn, rs, rs2
Dim dblcrlf
Dim Line

   dblcrlf = chr(13) & chr(10) & chr(13) & chr(10)

   ConnStr = "provider=SQLOLEDB;network=DBMSSOCN;"
   ConnStr = ConnStr & "uid=" & user & ";pwd=" & pswd & ";server="
   ConnStr = ConnStr & server & ";database=" & dbname

   Set Conn = CreateObject("ADODB.Connection")
   Conn.Open ConnStr

   Set rs = Conn.Execute("SELECT name FROM sysobjects WHERE xtype='TR'")
   Do While Not rs.eof
      Wscript.Echo "/*****  " & rs(0) & "  *****/"
      Set rs2 = Conn.Execute("EXEC sp_helptext '" & rs(0) & "'")
      Do While Not rs2.eof

         ' It seems that each line of the trigger text has double CRLF characters...
         ' These 2 statements remove both pairs and the "wscript.echo" adds 1 back on
         Line = Rtrim(rs2(0))
         Line = Left(Line,Len(Line)-2)
         Wscript.Echo Line

   rs.Close   : Set rs   = nothing
   Conn.Close : Set Conn = nothing

yipingAuthor Commented:
Thanks so much made my life much easiler

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now