SQL custom alert

Question

So I'm trying to set up a custom alert in Microsoft SQL, 2014. I want to send my team an e-mail whenever something has been sitting in the queue for longer than 30 minutes.

I promise I've researched it quite a bit, but the issues other people posted similar to this on here didn't help.

I set up the database mail profile, and sent a test e-mail. It works.

Then I go to the SQL Alert manager to set up a new alert.

I left the namespace to the default it gave me: \.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER And my script is as follows:

if (exists (Select 1
            From ____
            where Status = 'WAITING' and
                  (GETDATE() - [ITIME]) > 0.5 * (1.0/24)
           )
   )
BEGIN

EXEC msdb.dbo.sp_send_dbmail
  @recipients='_______',
  @body='Attention: A job has been sitting in the _____ queue for longer than 30 minutes.', 
  @subject ='Queue Time Expiration',
  @profile_name ='__________',
  @query =
     'USE ___
     (select * from _____] where Status=''WAITING'' and (GETDATE() - [ITIME])>0.02)'

END

I get this error: SQLServerAgentError: WMI error: 0x80041058 The @wmi_query could not be executed in the @wmi_namespace provided. Verify that an event class selected in the query exists in the namespace and that the query has the correct syntax.

I can't tell if there is something wrong with my query itself, since I've never written this type of script before and am kinda making it up as I go, or if it's the namespace. I didn't change the namespace, it's just the default. I don't really know what to put there, this is just what it showed in the tutorial I was following.

Edit: fixed syntax in my script from suggestion below, still not working though

Answer 1

You might have other problems, but your if code doesn't make sense. You are executing a query that creates a result set and then you have an if. This should do what you want in that part of the code:

declare @WaitTime datetime;

Select @WaitTime = [ITIME]
From ______________
where Status='WAITING' ;

IF (GETDATE() - @WAITTIME) > 0.02 . . .

I presume that you have done the calculation and 0.02 days is what you really want. Otherwise you would use 0.5/24 for the constant.

You can also replace the variable, the query, and the if with:

if (exists (Select 1
            From ______________
            where Status = 'WAITING' and
                  IF (GETDATE() - ITIME) > 0.5 * (1.0/24)
           )
   )

Answer 2

You are using the wrong namespace, the CIM_DataFile WMI class is part of the \root\CIMV2 namespace and not of \root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER

The Query Used in SQL Server Alert using WMI Event ERROR to solve the error is

EXEC msdb.dbo.sp_add_alert @name=N'SimpleFolderWatcher', @message_id=0,
@severity=0,@enabled=1,@delay_between_responses=0, 
@include_event_description_in=0,@category_name=N'[Uncategorized]',  
@wmi_namespace=N'\\.\root\cimv2', 
@wmi_query=N'SELECT * FROM __InstanceCreationEvent WITHIN 1 
WHERE TargetInstance ISA ''CIM_DataFile'' 
AND TargetInstance.Drive = ''C:'' 
AND TargetInstance.Path=''\\testfolder\\'' 
AND TargetInstance.Name LIKE ''C:\\%'' ', 
@job_id=N'0-0-0' 

Answer 3

I wanted to share how I worked around this problem before I close this question. I ended up using a different method to solve this problem instead of the one I was trouble-shooting for. Gordon Linoff's critique of my script was helpful anyways, and I think Rajesh had the right idea as well but I found a way that was easier for me.

The goal of my problem was simple: I wanted my team to receive an e-mail every time a query returned some results. In my case, whenever there were any objects that had been sitting waiting in the queue for longer than 30 minutes. I apologize for the lack of details, I don't want to violate company policies. I think that this can be accomplished through the alerts system, however I never managed it. Instead I wrote a job that includes as a step a script that checks for any objects that meets my criteria, and then emails my team if there are any. This is probably obvious to DBAs, but I'm super new to this stuff so excuse my ignorance.

For anyone else who is stuck, here's what I did. This tutorial is for the 2014 edition, and I apologize but I can't include the images since I don't have enough reputation yet. I recommend running these steps on a test database first.

1.) Write your query

Create a "New Query", and start by writing your own query that checks the database for the condition you want your automated e-mails to notify based on. Just a simple select statement. We're going to modify our query slightly to record the number of records that meet the criteria in the "where" clause in the variable @recordCount. We aren't doing anything with recordCount yet, but we do execute the script to see that the syntax is correct. Go ahead and make your own recordCount variable and assign it to the count of your query results as well and execute it to check the syntax. We're going to have to set our query aside for now so we can set up e-mail in the database, but we will come back to it in Step 4, so be sure to save it or leave it open.

Example:

declare @recordCount int
Select @recordCount = isnull(count(*), 0)
From (table)
where (conditions)

Simple enough.

Step 2: Create an e-mail profile

We need to create a profile for MSSQL to send e-mails from. This is not the e-mail address you want the notifications sent to, however they can be the same e-mail address. In the object explorer, expand the server node, then expand the "Management" node. You'll see something called "Database mail." Double click it to launch the Database Mail Configuration Wizard. Press "next" at the intro page. On the second page of the Database Wizard, you'll want to choose the first option (it should say something about setting up a new profile).

•Click "Next."

•Give your profile a name. Remember, this is the name of the e-mail sender, not the recipient.

•Fill in an appropriate description.

•Click "Add."

•In the pop-up window that appears, choose "New Account."

•In the "New Database Mail Account" window that appears, give your account a name and description.

•In the "Outgoing Mail Server" section, specify the e-mail address of the sender and give it a display name.

•You'll need to know the SMTP server of the e-mail account you're using. If it is an @intel.com account, write smtp.intel.com as shown below. Google Mail uses smtp.google.com, and yahoo uses smtp.mail.yahoo.com.

•For a list of smtp server names for the most popular e-mail providers, follow this link: http://www.serversmtp.com/en/what-is-my-smtp

•You'll also need to know the port number.

•Under the "Authentication" section, you'll most likely want the first option, though if you know the e-mail and password logon for this account, you can choose the second option and provide them.

•Click OK.

•On the next screen, choose to make the profile public if this is reasonable, and in the far right column choose for this profile to be the default.

•Click Next.

•On the next page, leave the default options and click "Next."

•On the final page, review your changes and click "Finish."

Step 3: Enable e-mail on the server agent

•If you expand the server and scroll down, you'll see the "SQL Server Agent." Right-click this and choose "properties."

•In the sidebar on the left, choose "Alert System."

•Under "Mail session," make sure "Enable mail profile" is checked. "Mail system:" should be set to "Database Mail," and you'll want to choose the profile you created in step 2 for the mail profile. •At the bottom of the "Pager e-mails" section, check "include body of e-mail in notification message."

•Choose OK.

•You'll want to restart the agent. Back in the object explorer, right-click "SQL Server Agent" again and choose "start" or "restart."

•Even though e-mail has been enabled on the agent, you might still have database mail disabled. Create a "New Query" and copy-paste the following short script. This will set the "Database Mail XPs" variable to 1, meaning it is enabled: sp_configure 'show advanced options', 1;

GO
RECONFIGURE;
GO
sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE
GO

•Scroll back up to the "Database Mail" option in the object explorer under Server Name > Management. Right click and choose "send test e-mail." In the window that pops up, make sure your new profile account is chosen as the profile, and in the "To:" line specify your own e-mail address. •You should receive the test e-mail within a few minutes.

Step 4: Writing the script

•We are going to expand upon our query from step 1.

•Presumably, if the query returns some records, we want to be notified. So we extend our query into the following script:

declare @recordCount int
 Select @recordCount = isnull(count(*), 0)
From (your table)
 where (your criteria)

IF (@recordCount > 0)
 Begin
 EXEC msdb.dbo.sp_send_dbmail
 @profile_name = 'the name of the profile you created in step 2',
 @recipients = 'your email address',
 @query = 'select * from (your table - same as above) where (your criteria - same as above)' ,
 @subject = 'your email subject',
 @Body = 'your e-mail message'
 End

•At the top of this new script, we see our exact query from before. Below it, there is a conditional statement. It says, if there are any records which meet the criteria of our query, then execute the send_dbmail command. Go ahead and copy-paste this script in a new query, filling in the necessary information. Since this is a test, you'll probably want to give it your own e-mail address for recipients. Also, make sure you give it the exact name you gave the profile you created.

•Note: if you've already forgotten the name of the profile you created, or if you're not sure on the exact spelling or casing, right-click SQL Server Agent in the object explorer and choose "Properties." In the left sidebar choose "Alert System." Towards the top of the page, the name of the profile will be specified. Once you have it, cancel out of this window.

•Once you've replaced the parts in bold with your own material, highlight the entire script and execute it. If there weren't any records found that meet your criteria, you'll get the message "Command(s) completed successfully." If at least one record was found, you'll get the message "Mail Queued."

•If you got the message "Command(s) completed successfully," go ahead and create a dummy record that will meet your criteria and trigger the e-mail, then run the script again.

•You should receive the e-mail in a few minutes. Notice that the e-mail is ugly and not properly formatted. We'll fix this now.

•To format our email, we'll need to tweak our script again. In the example below, I've formatted the e-mail using HTML so that the query results show up in a simple table. The things that need to be changed are in bold.

declare @recordCount int
Select @recordCount = isnull(count(*), 0)
From **(table)**
where **(conditions)**



IF (@recordCount > 0)
Begin
DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)

SET @xml = CAST(( SELECT **[(column)]** AS 'td','',**[(column)]** AS 'td','',
**[(column)]** AS 'td','', **(column)** AS 'td'
FROM **(table)**
where **(conditions)**
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))

SET @body ='**(Message at beginning of email)**' <br> <br>      <br>
 <html><body><H3 style="color:#3333FF">**(Label for your table)**</H3>
 <table border = 1> 
 <tr>
 <th> **(Column header text)**</th> <th> **(Column header text)** </th> <th> **(Column header text)** </th>     <th> **(Column header text)** </th></tr>'

SET @body = @body + @xml +'</table></body></html>'

EXEC msdb.dbo.sp_send_dbmail
 @profile_name = '**(profile name from last step)'**,
 @recipients = '**(recipient e-mail)**',
 @subject = '**(Email subject)**',
@Body = @body,
 @Body_format='HTML'
 End

•The most important thing is that you add the variable @Body_format and set it to HTML. Then you can add a variable @Body to your script, and set it to the HTML content you want in the e-mail. Don't forget to set @Body equal to @body inside the EXEC block at the bottom. Everything else is just simple mark-up. The result is a simple table, but you can change the markup to anything you like.

•Go ahead and save your script. We'll be using it in the final step.

Step 5: Setting up a job to run our script

•We're almost done! Now that our script is working, all we need to do is set up a job to run this script on a schedule. In the object explorer under the server, expand the SQL Server Agent. Right click "Jobs" and select "New Job."

•Give your job a name and a description, leave the Owner to the default login, and "Category" can be left set to "[Uncategorized: (local)]." Make sure "Enabled" is checked.

•In the left sidebar, choose "Steps."

•At the bottom, select "New."

•In the window that pops up, give the step a name. Set "Type" to "Transact-SQL script (T-SQL)," and choose the appropriate database. "Run as" can be left blank.

•Click on the "Open" button, and choose the script you saved in step 4. The tool will automatically populate with your script. You can click "Parse" to check the syntax of your script one last time.

•Click OK.

•In the left sidebar of the New Job window, choose "Schedules." At the bottom, press "New."

•Make sure "Enabled" is checked and that you've given your schedule a name.

•How you set your schedule is up to you. You can schedule the job to run once, or set it to recurring and allow it to execute every month, week, day, minute, or second, either indefinitely or until a particular end date, during only the workday or around the clock.

•Review the description at the bottom under "Summary" to make sure this is what you wanted. Click OK.

•Back in the "New Job" window, click "OK."

•Make sure the SQL Server Agent is running, and expand jobs. You should see your job there now in the list, if not you can right-click "Jobs" and choose "Refresh."

•Find your job in the list, right-click it, and choose "Start Job at Step..."

•Your job is now running and will continue to run however you have scheduled it to! If you ever need to edit it, you can simply double-click it.

Still have questions? •This tutorial was created from the following two resources:

1.) Microsoft's database mail documentation: http://msdn.microsoft.com/en-us/library/ms187605.aspx

2.) Sorry, couldn't post second resource link because I don't have the reputation yet. Just Google Microsoft SQL Server troubleshooting.

Thank you, everyone.