Re: Newbie Question on Jobs & Stored Procedures.

From: Rob Boger (rboger_at_usa.com)
Date: 09/25/04 

Date: Sat, 25 Sep 2004 15:47:48 -0500

Hey John,

Sorry, I thought it was more of a procedural issue versus data-based, as
mentioned, it works when one record is returned but doesn't send the emails
out when multiple records are returned.

Thanks also for the tip on asking questions, again I'm new to this stuff,
I've done a little VbScript, Asp, but not really working directly within Sql
Server. Here is the Create Table statment: I'll look into the GetDate(),
I'm used to using Now() but apparently that's not available directly within
Sql Server.

Thanks!
Rob

The Table:

CREATE TABLE [SiteTasks] (
 [TaskId] [int] IDENTITY (1, 1) NOT NULL ,
 [Title] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [Creation] [datetime] NULL ,
 [TargetCompletion] [datetime] NULL ,
 [Contact] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
 [AlternateContact] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
 [Notification] [bit] NULL ,
 [TimesNotified] [int] NULL ,
 [LastNofication] [datetime] NULL ,
 CONSTRAINT [PK_SiteTasks] PRIMARY KEY CLUSTERED
 (
  [TaskId]
 ) ON [PRIMARY]
) ON [PRIMARY]
GO

The Job:

DECLARE @TITLE VARCHAR(100), @CONTACT VARCHAR(200)

DECLARE TASK_LIST CURSOR FOR

SELECT TITLE, CONTACT FROM PLATFORMMGMT.DBO.SITETASKS
    WHERE NOTIFICATION = 1 AND TARGETCOMPLETION > CURRENT_TIMESTAMP
OPEN TASK_LIST
 FETCH NEXT FROM TASK_LIST
 INTO @TITLE, @CONTACT
  WHILE @@FETCH_STATUS = 0
  BEGIN
   PRINT @CONTACT + ' ' + @TITLE

DECLARE @BODY VARCHAR(4000), @SUBJECT VARCHAR(100),
        @TO VARCHAR(100), @FROM VARCHAR(100)
   SELECT @BODY = 'THIS ITEM IS PAST DUE.'
   SELECT @SUBJECT = @TITLE
   SELECT @TO = @CONTACT
   SELECT @FROM = 'ME@MYDOMAIN.COM'
EXEC SEND_MAIL @FROM, @TO, @SUBJECT, @BODY

    FETCH NEXT FROM TASK_LIST
    INTO @TITLE, @CONTACT

  END

CLOSE TASK_LIST
DEALLOCATE TASK_LIST

The Stored Procedure:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE [DBO].[SEND_MAIL]
   @FROM VARCHAR(100) ,
   @TO VARCHAR(100) ,
   @SUBJECT VARCHAR(100)=" ",
   @BODY VARCHAR(4000) =" "

   AS
   DECLARE @MSG INT
   DECLARE @HR INT
   DECLARE @SOURCE VARCHAR(255)
   DECLARE @DESCRIPTION VARCHAR(500)
   DECLARE @OUTPUT VARCHAR(1000)

   EXEC @HR = SP_OACREATE 'CDO.MESSAGE', @MSG OUT

   EXEC @HR = SP_OASETPROPERTY @MSG,
'CONFIGURATION.FIELDS("http://schemas.microsoft.com/cdo/configuration/sendus
ing").VALUE','2'
   EXEC @HR = SP_OASETPROPERTY @MSG,
'CONFIGURATION.FIELDS("http://schemas.microsoft.com/cdo/configuration/smtpse
rver").VALUE', 'LOCALHOST'

   EXEC @HR = SP_OAMETHOD @MSG, 'CONFIGURATION.FIELDS.UPDATE', NULL

   EXEC @HR = SP_OASETPROPERTY @MSG, 'TO', @TO
   EXEC @HR = SP_OASETPROPERTY @MSG, 'FROM', @FROM
   EXEC @HR = SP_OASETPROPERTY @MSG, 'SUBJECT', @SUBJECT

   EXEC @HR = SP_OASETPROPERTY @MSG, 'TEXTBODY', @BODY
   EXEC @HR = SP_OAMETHOD @MSG, 'SEND', NULL

   IF @HR <> 0
     SELECT @HR
     BEGIN
       EXEC @HR = SP_OAGETERRORINFO NULL, @SOURCE OUT, @DESCRIPTION OUT
       IF @HR = 0
         BEGIN
           SELECT @OUTPUT = ' SOURCE: ' + @SOURCE
           PRINT @OUTPUT
           SELECT @OUTPUT = ' DESCRIPTION: ' + @DESCRIPTION
           PRINT @OUTPUT
         END
       ELSE
         BEGIN
           PRINT ' SP_OAGETERRORINFO FAILED.'
           RETURN
         END
     END

   EXEC @HR = SP_OADESTROY @MSG
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Relevant Pages

  • Re: try...catch execution issue
    ... declare @ii_LinkedServerID int = 1 ... declare @ii_LinkedServerID int = null ... CAST(0x00009C5D00904037 AS DateTime), N'dcaz\couchj', CAST(0x00009C6100ADA6DB ... This procedure is used to insert/update a ServerOption to ...
    (microsoft.public.sqlserver.programming)
  • Re: 17805 Starting up Java App server, with SQL Server 2000 Backend
    ... network issue although you would expect a network issue to be more random. ... declare @P1 int ... On another machine running SQL Server 2000 Standard on Win2K advanced, ...
    (microsoft.public.sqlserver.clients)
  • Re: DBCOLUMNFLAGSENUM
    ... >BOL claimed that the flags property of the column object could be broken ... The problem I have, is that when I generate import code using SQL Server, ... DECLARE @DBCOLUMNFLAGS_MAYDEFER int -- 0x2, ...
    (microsoft.public.sqlserver.dts)
  • Re: Front end website full-text search
    ... -- a @boolean type of null means a phrase based search ... DECLARE @whitespace INT ... Looking for a SQL Server replication book? ...
    (microsoft.public.sqlserver.fulltext)
  • Re: between dates with no time consideration
    ... > Columnist, SQL Server Professional ... > declare @start as datetime ... >> only passing the date value back to the stored procedure, ...
    (microsoft.public.sqlserver.programming)
Loading