I would like to use perfmon counter alerts to trigger an email to let developement DBAs know when their machine is running out of memory.
I got an idea from this article: http://www.db-staff.com/index.php/microsoft-sql-server/89-sql-server-monitoring-tool
I have set up an alert and created a small batch file that generates an email:
sqlcmd -S MyMachine -E -Q "declare @subject sysname; set @subject = 'Performance alert on ' + @@SERVERNAME; EXEC msdb.dbo.sp_send_dbmail @recipients='MyEmail',@subject = @subject , @body = '%mailbody%', @body_format = 'TEXT' ;"
This sends an email using sqlcmd to run a SQL stored procedure. It works when I run it.
When Perfmon tries to run the batch file I see the following error in the event log:
Unable to execute command '' for the MyFirstAlert alert. The alert will continue as scheduled. The error code returned is in the data.
I think I must be doing something wrong with the command line arguments (How exactly do I pass them into the batch file?) or perhaps a permissions issue (I've allowed read and excute to everyone on the .bat. Also allowed cmd.exe to [BATCH].
Thanks in advance.