Conditionally stop a trigger from firing

There are instances when inserting records into a table that you might not want one or more triggers to fire.  There is an easy way to control this using Context_Info.

The trick is to add code to the trigger(s) that you want to control.

Context_Info() can be set to a hex value in your procedure and then read from the trigger.  It is a sneaky way to pass a parameter to a trigger.

At the beginning of your trigger logic, add the following three lines

IF Context_Info() = 0x20220121 RETURN  


Then in your procedure, just before you do your insert, update, or delete, add the following line

SET Context_Info 0x20220121 

Note that the value 0x20220121 can be any hex value you choose.  However, since it is global, I prefer to use a date for the numbers to avoid stepping on it with a later addition.

Also, initially, Context_Info() will return null until it has been set.

Remember to reset it after the insert, update, or delete

SET Context_Info 0x00000000 

The report server cannot open a connection to the report server database.

I ran into something interesting today with our VM template. 

When I change the machine name, I can run the SQL script on the desktop to correct the SQL server. After reboot, all seems fine. ReportServer services even start up with no error.

However, if I try to run an SSRS report, or even connect to http://localhost/reports, I get an error:

"The report server cannot open a connection to the report server database."

This is because the report server configuration records the SQL server that the ReportServer database resides on (and even the name of the ReportServer database, in case you want to really confuse people by changing it). 

If this happens to you, this is how you fix it:

1. Launch Reporting Services Configuration Manager
2. Click database from the menu at the left.
3. On the right, you will see you old SQL Server name.  Click change database.
4. Select Choose an existing report server database, then next
5. Enter your new SQL Server name and test connection, then click next
6. For Report Server Database, select ReportServer, then Next x3
7. Then finish

If you have WennSoft reports setup, you can run this to correct those paths:
WS_SetReplacementReportsForSRS