If you’re using Database Mail in SQL Server, you’ll probably want to check your configuration settings at some stage.
You can do this by executing the sysmail_help_configure_sp stored procedure in the msdb database.
Tag: mssql
Show Advanced Server Configuration Options in SQL Server (T-SQL)
If you’re trying to check an advanced configuration option in SQL Server, but you get an error, you might find the following helpful.
Continue readingLimiting Simultaneous User Sessions for a Specific Login in SQL Server
In SQL Server, you can use a logon trigger to audit and control server sessions, such as track login activity, restrict logins to SQL Server, or limit the number of sessions for a specific login.
This article provides an example of using a logon trigger to limit the number of simultaneous sessions for a specific login.
Continue readingReturn a List of Server Trigger Events in SQL Server
In SQL Server, you can use the sys.server_trigger_events catalog view to return a list of server trigger events.
More specifically, this view contains one row for each event for which a server-level (synchronous) trigger fires.
Continue readingReturn a List of All Server Triggers in SQL Server
In SQL Server, you can use the sys.server_triggers catalog view to return a list of server triggers.
More specifically, this view contains the set of all server-level DDL triggers with object_type of TR or TA.
For CLR triggers, the assembly must be loaded into the master database.
Return a List of Triggers in SQL Server
You can use the sys.triggers catalog view to return a list of triggers in a database in SQL Server.
This view contains a row for each object that is a trigger, with a type of TR or TA.
Continue readingExamples of Formatting ‘datetimeoffset’ in SQL Server using Standard Format Strings (T-SQL)
I thought it would be interesting to run a few quick queries to see how various formatting strings affect the formatting of date/time values.
Create a “Last Modified” Column in SQL Server
Some database tables include a “last modified” column, which stores the date and time that the row was last updated. Each time the row is updated, the date is updated to reflect the date and time of that update.
In SQL Server, you can use a trigger to perform this update.
A trigger is a special type of stored procedure that automatically runs when an event occurs in the database server.
You can use the CREATE TRIGGER statement to create a trigger when using T-SQL. This statement can be used to create a DML, DDL, or logon trigger.
How to Use GOTO in SQL Server
In SQL Server, you can use GOTO to alter the flow of execution. You can use it to “jump” to another part in the T-SQL code.
The way it works is, you create a label, then you can use GOTO to jump to that label. Any code between GOTO and the label are skipped, and processing continues at the label.
GOTO statements and labels can be used anywhere within a procedure, batch, or statement block. They can also be nested.
Difference Between sys.sql_modules, sys.system_sql_modules, & sys.all_sql_modules in SQL Server
In SQL Server the sys.sql_modules, sys.system_sql_modules, and sys.all_sql_modules system catalog views return metadata about SQL language-defined modules in SQL Server.
However, there is a difference between them.