Archive
Validate SQL Server Database Mail Settings
In my last post, I shared a script to automate the migration of SQL Server Database Mail settings. In this post, I show how to send test e-mails from all Database Mail profiles on an instance. The migration I was working on contained 21 Database Mail profiles. The following script will send a test e-mail from each profile to confirm successful configuration. I hope you can put this code to use in your migrations.
Migrating SQL Server Database Mail Settings
This week, I was working on a migration for a client. The migration was moving databases from a stand-alone instance to a two-node Availability Group. When it came to moving the Database Mail settings, I discovered they had 21 sets of profiles and accounts. Not wanting to manually create 42 Database Mail profiles, I set out to automate the process. A web search yielded this blog post by Iain Elder. This script does what I was looking for, but would only generate settings for a single Database Mail profile. Using Iain's code as a starting point, I modified it to create Database Mail settings for all profiles on an instance. The script is listed below. I hope this simplifies your SQL Server migrations.
Reading the xp_readerrorlog Using a WHILE Loop
Last week, I needed to search through all of the SQL Server error logs on an instance. I have used xp_readerrorlog before, using the information found here. We retain 99 log files, which is the maximum number of files that can be kept. I needed to search through all of these files, so I put together the following script. This is another example of using a WHILE loop in gathering information from SQL Server.
USE master GO -- Create temp table to hold the output of the error logs CREATE TABLE #errorlog (errdate DATETIME ,errproc SYSNAME ,errtext VARCHAR(4000)) -- Declare and initialize the @loopcount variables DECLARE @loopcount INT SET @loopcount = 1 -- Loop through the error logs -- We keep the 99 logs, which the maximum -- Change the loop limit accordingly WHILE @loopcount <= 99 BEGIN -- Insert the results of xp_readerrorlog into the temp table INSERT INTO #errorlog -- You can find the parameters for xp_readerrorlog at the following link -- http://www.mssqltips.com/sqlservertip/1476/reading-the-sql-server-log-files-using-tsql/ -- The parms I use below are as follows: -- @loopcount - file number, with 0 being the current file -- 1 - indicates the SQL error log - a 2 would query the SQL Agent log -- 'DELETE' - a search argument EXEC xp_readerrorlog @loopcount, 1, 'DELETE' -- Increment your loop counter SET @loopcount = @loopcount + 1 END -- Select the results of your loop -- Alternately, you can dump the entire log file and then use WHERE predicates to -- select what you are interested in SELECT * FROM #errorlog -- Clean up your temp table DROP TABLE #errorlog