For SQL Server 2000, I quite honestly wouldn't bother trying to get operators to work. SQL Mail is a royal PITA and requires Outlook or a similar mail client to be installed on the server. I would rather just setup each job to have a step called "mail on failure", which uses a token to identify the job, and then calls a stored procedure. You would only reach that step in the workflow if you set a step to go to that step on failure. Otherwise the step before it simply quits the job with success.
A couple of alternatives:
(1) I used xp_smtp_sendmail with great success when I was still straddled with SQL Server 2000. The challenge is it used to be available on sqldev.net, but Gert seems to have abandoned that site. So you may have to search around to find a copy of the DLL. It was written by a Microsoft employee but the standard "download from a trusted source only" disclaimer applies.
(2) Use CDO. The same ick factor as SQL Mail but no dependency on Outlook / MAPI. See this KB article for some info, but here is an example that sets up a stored procedure to send job mail failures and then how to include a job token in a job step to call that stored procedure.
First, a generic procedure to send the e-mail (you may want to re-use this for more than just job failures):
CREATE PROCEDURE [dbo].[sp_send_cdosysmail]
@From VARCHAR(320) = 'default_admin@yourcompany.com',
@To VARCHAR(320) = 'default_admin@yourcompany.com',
@Subject NVARCHAR(4000) = N'',
@Body NVARCHAR(4000) = N'',
@SMTPServer VARCHAR(2000) = '127.0.0.1'
AS
BEGIN
SET NOCOUNT ON;
DECLARE @obj INT;
EXEC sp_OACreate 'CDO.Message', @obj OUTPUT;
EXEC sp_OASetProperty @obj,
'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value',
'2';
EXEC sp_OASetProperty @obj,
'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value',
@SMTPServer;
EXEC sp_OAMethod @obj,
'Configuration.Fields.Update',
NULL;
EXEC sp_OASetProperty @obj, 'To', @To;
EXEC sp_OASetProperty @obj, 'From', @From;
EXEC sp_OASetProperty @obj, 'Subject', @Subject;
EXEC sp_OASetProperty @obj, 'TextBody', @Body;
EXEC sp_OAMethod @obj, 'Send', NULL;
EXEC sp_OADestroy @obj;
END
GO
Next, a procedure to handle the e-mail specifically for job failures:
CREATE PROCEDURE dbo.JobAlertSender
@To VARCHAR(320) = 'default_admin@yourcompany.com',
@job_id VARCHAR(36)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Subject NVARCHAR(4000);
SELECT @Subject = N'Job failed: ' + name
FROM msdb.dbo.sysjobs
WHERE job_id = @job_id;
DECLARE @Body NVARCHAR(4000);
SET @Body = CONVERT(CHAR(10), GETDATE(), 120)
+ ' ' + CONVERT(CHAR(5), GETDATE(), 108);
EXEC dbo.sp_send_cdosysmail
@From = 'default_admin@yourcompany.com',
@To = @To,
@Subject = @Subject,
@Body = @Body;
END
GO
Now you can add a step to your job that calls this procedure. You would only hit this step if a previous step failed. The step would have code like this (note that you may want to override the default "To" person depending on the job):
EXEC master.dbo.JobAlertSender
-- @To = 'override@yourcompany.com',
@job_id = '(JOBID)';
Here is how the job step would look:
And here is how you would set up previous steps, either using "go to the next step" or picking the failure step explicitly from the drop-down:
If you find xp_smtp_sendmail, you'd just call a slightly different procedure from dbo.JobAlertSender
.
If you don't have a valid SMTP server, you'll need to install an SMTP server locally. For help with that you'll want to hit up SuperUser or ServerFault. If you need something to test locally in the meantime, nothing could be simpler than smtp4dev. You won't get the e-mails in your inbox, but you can verify the contents, headers, etc until you do have a proper SMTP server in place.
Google also told me I wrote this article in 2002 which may contain some useful information.
Best Answer
A
datetime
column always includes a time part. If you set adatetime
column to '2017-05-10', it'll show up as2017-05-10 00:00:00
.When you want to compare "dates only" with
datetime
columns, you set the time part to midnight on both dates. This is generally referred to as "removing" the time part.If you want to eliminate the time part from your output, there are various options. You can build a date in whatever format you wish by pulling the various fields out with
DATEPART
. Most commonly, however, I've seen people convert the date to a string and drop the time portion:Note that
CONVERT
is being used here for formatting the date value for output, not for changing it for comparison purposes. I'd still do that as you did in your code.