In part 1 of this series, I outlined the design of a solution that I have used to record errors in a manner that allows a unique ID to be returned to the user which in turn can be used to trace the error. In part 2 I described the database objects that are required Read More
Month: December 2010
Stored Procedure Error Handling Best Practices, Part 2
In the part 1 of this series, I outlined the design of a solution that I have used to record errors in a manner that allows a unique ID to be returned to the user which in turn can be used to trace the error. In this part of the series, I explain the database Read More
Stored Procedure Error Handling Best Practices, Part 1
Over the years I have worked with SQL Server a regular requirement was the ability to log errors in the database in a way that lets them be retrieved by support teams and allows end users to have a reference number. This first part of this series will describe the design of the solution, the Read More
System Stored Procedures
Using the “sp_” prefix for a stored procedure has some interesting effects on SQL Server. Firstly it looks for the stored procedure in the Master database (dbo schema) and then it looks in the current database (default schema for the user, then dbo schema). The reason it is looking in Master first is that it Read More
Best Practices for the Management of SQL Scripts
I have been a long time fan of writing scripts for every action that is taken to make any changes in a database. This means that version control tools can be used to track the changes and if necessary repeat them. In previous roles I have worked in environments where it has been essential to Read More
Database Mail, Part 5-Monitoring
Now that Database Mail is set up and can be used it is important to monitor the process. In particular the following issues should be identified as they may have an impact on the effectiveness of any solution that depends on Database Mail: The service broker must be running The e-mails should not be failing Read More
Database Mail, Part 4–Sending an e-mail
In the previous parts of this series, I have enabled Database Mail, created an account and created a profile. With everything now set up, it is important to test the ability to send an e-mail. At this point, I am assuming that the SMTP server has been set up and is working properly. SQL Server Read More
Database Mail, Part 3–Creating a Profile
In Part 1 of this series I produced a stored procedure to enable Database Mail and in Part 2 I produced a stored procedure to create an account. Now, to be able to use an account, and to send an e-mail you need to have a profile. A profile is related to one or more Read More
Database Mail, Part 2–Creating an Account
In Database Mail, Part 1 I produced a stored procedure that enables Database Mail. The second stage is to create an account which determines the e-mail address that messages will be sent from and the SMTP server that will be used to send the e-mails. It is worth noting that if your organisation has primary Read More
Database Mail, Part 1
Over the years I have come across many reasons for Database Mail to be activated, and a couple of reasons not to use it. The main reasons for activating it are the added flexibility that can be achieved in reporting the success or failure of maintenance jobs or any unattended processes and for monitoring and Read More