In SQL Server, you can use the sysmail_help_profile_sp stored procedure on the msdb database to retrieve a list of all Database Mail profiles.
You can also return profile info based on the profile name or ID.
Continue readingCategory: SQL Server
Find the Associations Between Database Mail Accounts and Database Principals in SQL Server (T-SQL)
In SQL Server, you can use the sysmail_help_principalprofile_sp stored procedure on the msdb database to retrieve a list of all associations between Database Mail accounts and database principals.
You can also return account info based on the principal name/ID or the profile name/ID.
Continue readingGet a List of Database Mail Accounts in SQL Server (T-SQL)
In SQL Server, you can use the sysmail_help_account_sp stored procedure on the msdb database to retrieve a list of all Database Mail accounts.
You can also return account info based on its account name or ID.
Continue reading3 Ways to use ALL in SQL Server
You might be familiar with the ALL option in SQL Server. Perhaps you’ve used it along with the UNION operator to include any duplicates that might be returned in the result set.
But did you know that ALL can also be used in two other contexts?
ALL can be used in the following three contexts:
- As an argument to the
SELECTclause. - As an argument to the
UNIONclause. - As a logical operator when comparing a scalar value with a single-column set of values.
Examples of each of these contexts follows.
Continue readingSQL Server ALL Operator Explained
In SQL Server, the ALL operator can be used with a subquery to compare a scalar value with a single-column set of values returned by the subquery.
It’s also true that the SELECT clause and UNION operator both accept an ALL argument, although this usage has a different purpose (allows duplicates in the result set).
Below are examples of using the ALL operator with a subquery.
SQL Server ANSI_NULLS Explained
In SQL Server, the ANSI_NULLS setting allows you to specify how NULL values are treated in queries.
More specifically, it allows you to specify ISO compliant behaviour of the Equal To (=) and Not Equal To (<>) comparison operators when they are used with NULL values.
How to Find a Database’s ANSI_NULLS Setting in SQL Server (T-SQL)
SQL Server has an ANSI_NULLS setting that determines how NULL values are evaluated when compared to another value with the Equals (=) and Not Equal To (<>) comparison operators.
While it’s true that you can change the ANSI_NULLS setting at the session level (using SET ANSI_NULLS), each database also has its own ANSI_NULLS setting.
You can check your database to see whether its ANSI_NULLS setting is ON or OFF.
To do this with T-SQL, you can either use the sys.databases catalog view or the DATABASEPROPERTYEX() function.
Check the Status of the Database Mail Queues in SQL Server (T-SQL)
In SQL Server, you can use the sysmail_help_status_sp stored procedure on the msdb database to check the status of the Database Mail queues.
Return a List of Emails Sent from SQL Server Database Mail (T-SQL)
If you use Database Mail in SQL Server, you can use the sysmail_sentitems view to return a list of all emails that Database Mail has sent.
Check the Status of All Database Mail Messages in SQL Server (T-SQL)
If you use Database Mail in SQL Server, you can use the sysmail_allitems view to check the status of all emails that Database Mail has processed.