SQL Server has a database mail option that you can use to send mail from the database server.
For example, you can get notifications when SQL Server Agent jobs finish running or fail, or when there’s a high-severity error, etc.
Continue readingCategory: SQL Server
Send Email from a Trigger in SQL Server (T-SQL)
If you find yourself needing to send an email automatically upon certain events occurring in SQL Server, you can do this via a trigger.
For example, you could automatically send an email when somebody deletes or updates a record from a table, etc.
To do this, you need to create a trigger that includes code for sending the email upon the required event.
Continue readingCreate an “Instead Of” Trigger in SQL Server
When you create a trigger in SQL Server, you have the option of firing it in conjunction with the triggering statement (i.e. the SQL statement that fired the trigger), or firing it instead of that statement.
To fire the trigger instead of the triggering statement, use INSTEAD OF argument.
This is in contrast to using the FOR or AFTER arguments. When you use those arguments, the trigger fires only when all operations specified in the triggering SQL statement have launched successfully.
Create a DML Trigger in SQL Server
In SQL Server, you can use the CREATE TRIGGER statement to create a trigger.
A trigger is a special type of stored procedure that automatically runs when an event occurs in the database server.
You can create a DML trigger, a DDL trigger, or a logon trigger.
This article provides an example of creating a DML trigger.
Continue readingCreate a Partitioned Table in SQL Server (T-SQL)
SQL Server supports partitioned tables and indexes. When a partitioned table or index is partitioned, its data is divided into units that can be spread across more than one filegroup.
Therefore, to create a partitioned table in SQL Server, you first need to create the filegroup/s that will hold each partition. You also need to create a partition function and a partition scheme.
So it goes like this:
- Create filegroup/s
- Create a partition function
- Create a partition scheme
- Create the partitioned table
Below is an example of using these steps to create a table with four partitions.
Continue readingSQL Server Error 213: Column name or number of supplied values does not match table definition.
Error message 213 is a common error that happens when you try to insert values into a table without explicitly specifying the column names.
The error looks like this:
Msg 213, Level 16, State 1, Line 1 Column name or number of supplied values does not match table definition.
It occurs when you specify the wrong number of values for that table. In other words, the number of values you provide doesn’t match the number of columns in the table.
Continue readingHow to Send an HTML Email from SQL Server (T-SQL)
SQL Server’s Database Mail has a stored procedure called sp_send_dbmail that you can use to send emails from SQL Server.
By default, emails are sent as text, but you can easily change this, so that they’re sent in HTML format.
The @body_format argument is what you use to switch over to HTML format.
How to Stop/Start the Database Mail Queue in SQL Server (T-SQL)
If you’re using Database Mail in SQL Server, you may occasionally need to stop or start the mail queue.
You can do this with T-SQL by using the sysmail_stop_sp stored procedure to stop the queue, and the sysmail_start_sp procedure to start it.
Change the Separator to a Comma when Emailing Query Results in SQL Server (T-SQL)
If you’ve ever used SQL Server’s Database Mail to email the results of a query, you might have noticed that the results are separated by a space by default.
This is fine if you want a space-separated result set, but what if you want it to be comma-separated?
Fortunately you can use the @query_result_separator argument to do just that. You can use this argument to specify any separator (as long as it’s a char(1)).
Remove Padding When Sending Query Results in an Email from SQL Server (T-SQL)
When you use the sp_send_dbmail stored procedure to send emails from SQL Server, you have the option of adding query results to the email.
When you do this, you might find that unwanted padding has been added to some columns. Fortunately, you can eliminate this padding with the @query_result_no_padding argument.