Can someone help me in altering the procedure below.
The current procedure emails one named person whom will get all the notifications from searching through all the users. What I'm looking for is that this procedure is to email each user with only their updates that they have to complete.
Example of what user 1 and user 2 would receive if the sample table/data used below is ran with the procedure that I'm after.
User 1 (test1@abc.com) would get an email:
-----------
From: wiupdate@server.com
Subject: WI update notification
<body of msg begin>
There has been some WI updates. Please read WI's:
WI46
WI47
WI48
WI52
Upon compleation, please update your status.
<body of msg end>
-----------
User 2 (test2@abc.com) would get an email:
-----------
From: wiupdate@server.com
Subject: WI update notification
<body of msg begin>
There has been some WI updates. Please read WI's:
WI48
WI57
Upon compleation, please update your status.
<body of msg end>
-----------
Example table/data:
----------------------------------
CREATE TABLE [dbo].[iso_employwi] (
[empWiID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[pin] [int] NOT NULL ,
[wi] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[rev] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[wi_level] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[level_trained] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[date_added] [smalldatetime] NULL ,
[date_trained] [smalldatetime] NULL ,
[wi_connection] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
INSERT INTO iso_employwi (pin,wi,rev,level_trained)
VALUES ('001','WI46','C','B')
INSERT INTO iso_employwi (pin,wi,rev)
VALUES ('001','WI47','A')
INSERT INTO iso_employwi (pin,wi,rev,level_trained)
VALUES ('001','WI48','G','1')
INSERT INTO iso_employwi (pin,wi,rev,level_trained)
VALUES ('001','WI49','A','No')
INSERT INTO iso_employwi (pin,wi,rev,level_trained)
VALUES ('001','WI50','F','F')
INSERT INTO iso_employwi (pin,wi,rev,level_trained)
VALUES ('001','WI51','E','E')
INSERT INTO iso_employwi (pin,wi,rev)
VALUES ('001','WI52','H')
INSERT INTO iso_employwi (pin,wi,rev,level_trained)
VALUES ('002','WI46','E','E')
INSERT INTO iso_employwi (pin,wi,rev)
VALUES ('002','WI57','H')
INSERT INTO iso_employwi (pin,wi,rev,level_trained)
VALUES ('002','WI48','G','1')
GO
CREATE TABLE [dbo].[iso_employees] (
[pin] [int] NOT NULL ,
[nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
INSERT INTO iso_employees (pin,email)
VALUES ('001','test1@abc.com')
INSERT INTO iso_employees (pin,email)
VALUES ('002','test2@abc.com')
GO
SELECT iso_employwi.pin, iso_employwi.wi, iso_employwi.rev, iso_employwi.level_trained,
iso_employees.email
FROM dbo.iso_employwi INNER JOIN dbo.iso_employees ON dbo.iso_employwi.pin = dbo.iso_employees.pin
WHERE (iso_employwi.level_trained <> iso_employwi.rev) AND
(iso_employwi.level_trained <> N'No') OR (iso_employwi.level_trained is null)
GO
DROP TABLE dbo.iso_employwi
DROP TABLE dbo.iso_employees
----------------------------------
[B]Here is the current procedure (WI_updated). I also included the email sending procedure if it's needed to ref.[/B]
----------------------------------
CREATE PROCEDURE [dbo].[sp_send_cdontsmail]
@From varchar(100),
@To varchar(100),
@Subject varchar(100),
@Body varchar(4000),
@CC varchar(100) = null,
@BCC varchar(100) = null
AS
Declare @MailID int
Declare @hr int
EXEC @hr = sp_OACreate 'CDONTS.NewMail', @MailID OUT
EXEC @hr = sp_OASetProperty @MailID, 'From',@From
EXEC @hr = sp_OASetProperty @MailID, 'Body', @Body
EXEC @hr = sp_OASetProperty @MailID, 'BCC',@BCC
EXEC @hr = sp_OASetProperty @MailID, 'CC', @CC
EXEC @hr = sp_OASetProperty @MailID, 'Subject', @Subject
EXEC @hr = sp_OASetProperty @MailID, 'To', @To
EXEC @hr = sp_OAMethod @MailID, 'Send', NULL
EXEC @hr = sp_OADestroy @MailID
GO
CREATE PROCEDURE [dbo].[WI_updated]
as
declare @message varchar(4000)
declare @wi varchar(20)
set @message = ''
declare wi_cursor cursor for
SELECT iso_employwi.wi
FROM dbo.iso_employwi INNER JOIN dbo.iso_employees ON dbo.iso_employwi.pin = dbo.iso_employees.pin
WHERE (iso_employwi.level_trained <> iso_employwi.rev) AND
(iso_employwi.level_trained <> N'No') OR (iso_employwi.level_trained is null)
open wi_cursor
fetch next from wi_cursor into @wi
while @@fetch_status = 0
begin
set @message = @message + @wi + char(10)
fetch next from wi_cursor into @wi
end
set @message = 'There has been some WI updates. Please read WI''s' + char(10) + char(10) + @message + char(10) + char(10) + 'Upon compleation, please update your status.'
close wi_cursor
deallocate wi_cursor
exec [dbo].[sp_send_cdontsmail] 'wiupdate@Server.com', 'user@server.com', 'Update Notification', @message
GO
----------------------------------
I'm guessing another variable needs to be set for the user's email and have the procedure do some kind of loop until all the user's have been emailed of their notification?
" exec [dbo].[sp_send_cdontsmail] 'wiupdate@Server.com', [B]@email[/B], 'Update Notification', @message "?
If anyone can help me out here, I would be very grateful! Thanks in adv!
JLM
The current procedure emails one named person whom will get all the notifications from searching through all the users. What I'm looking for is that this procedure is to email each user with only their updates that they have to complete.
Example of what user 1 and user 2 would receive if the sample table/data used below is ran with the procedure that I'm after.
User 1 (test1@abc.com) would get an email:
-----------
From: wiupdate@server.com
Subject: WI update notification
<body of msg begin>
There has been some WI updates. Please read WI's:
WI46
WI47
WI48
WI52
Upon compleation, please update your status.
<body of msg end>
-----------
User 2 (test2@abc.com) would get an email:
-----------
From: wiupdate@server.com
Subject: WI update notification
<body of msg begin>
There has been some WI updates. Please read WI's:
WI48
WI57
Upon compleation, please update your status.
<body of msg end>
-----------
Example table/data:
----------------------------------
CREATE TABLE [dbo].[iso_employwi] (
[empWiID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[pin] [int] NOT NULL ,
[wi] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[rev] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[wi_level] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[level_trained] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[date_added] [smalldatetime] NULL ,
[date_trained] [smalldatetime] NULL ,
[wi_connection] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
INSERT INTO iso_employwi (pin,wi,rev,level_trained)
VALUES ('001','WI46','C','B')
INSERT INTO iso_employwi (pin,wi,rev)
VALUES ('001','WI47','A')
INSERT INTO iso_employwi (pin,wi,rev,level_trained)
VALUES ('001','WI48','G','1')
INSERT INTO iso_employwi (pin,wi,rev,level_trained)
VALUES ('001','WI49','A','No')
INSERT INTO iso_employwi (pin,wi,rev,level_trained)
VALUES ('001','WI50','F','F')
INSERT INTO iso_employwi (pin,wi,rev,level_trained)
VALUES ('001','WI51','E','E')
INSERT INTO iso_employwi (pin,wi,rev)
VALUES ('001','WI52','H')
INSERT INTO iso_employwi (pin,wi,rev,level_trained)
VALUES ('002','WI46','E','E')
INSERT INTO iso_employwi (pin,wi,rev)
VALUES ('002','WI57','H')
INSERT INTO iso_employwi (pin,wi,rev,level_trained)
VALUES ('002','WI48','G','1')
GO
CREATE TABLE [dbo].[iso_employees] (
[pin] [int] NOT NULL ,
[nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
INSERT INTO iso_employees (pin,email)
VALUES ('001','test1@abc.com')
INSERT INTO iso_employees (pin,email)
VALUES ('002','test2@abc.com')
GO
SELECT iso_employwi.pin, iso_employwi.wi, iso_employwi.rev, iso_employwi.level_trained,
iso_employees.email
FROM dbo.iso_employwi INNER JOIN dbo.iso_employees ON dbo.iso_employwi.pin = dbo.iso_employees.pin
WHERE (iso_employwi.level_trained <> iso_employwi.rev) AND
(iso_employwi.level_trained <> N'No') OR (iso_employwi.level_trained is null)
GO
DROP TABLE dbo.iso_employwi
DROP TABLE dbo.iso_employees
----------------------------------
[B]Here is the current procedure (WI_updated). I also included the email sending procedure if it's needed to ref.[/B]
----------------------------------
CREATE PROCEDURE [dbo].[sp_send_cdontsmail]
@From varchar(100),
@To varchar(100),
@Subject varchar(100),
@Body varchar(4000),
@CC varchar(100) = null,
@BCC varchar(100) = null
AS
Declare @MailID int
Declare @hr int
EXEC @hr = sp_OACreate 'CDONTS.NewMail', @MailID OUT
EXEC @hr = sp_OASetProperty @MailID, 'From',@From
EXEC @hr = sp_OASetProperty @MailID, 'Body', @Body
EXEC @hr = sp_OASetProperty @MailID, 'BCC',@BCC
EXEC @hr = sp_OASetProperty @MailID, 'CC', @CC
EXEC @hr = sp_OASetProperty @MailID, 'Subject', @Subject
EXEC @hr = sp_OASetProperty @MailID, 'To', @To
EXEC @hr = sp_OAMethod @MailID, 'Send', NULL
EXEC @hr = sp_OADestroy @MailID
GO
CREATE PROCEDURE [dbo].[WI_updated]
as
declare @message varchar(4000)
declare @wi varchar(20)
set @message = ''
declare wi_cursor cursor for
SELECT iso_employwi.wi
FROM dbo.iso_employwi INNER JOIN dbo.iso_employees ON dbo.iso_employwi.pin = dbo.iso_employees.pin
WHERE (iso_employwi.level_trained <> iso_employwi.rev) AND
(iso_employwi.level_trained <> N'No') OR (iso_employwi.level_trained is null)
open wi_cursor
fetch next from wi_cursor into @wi
while @@fetch_status = 0
begin
set @message = @message + @wi + char(10)
fetch next from wi_cursor into @wi
end
set @message = 'There has been some WI updates. Please read WI''s' + char(10) + char(10) + @message + char(10) + char(10) + 'Upon compleation, please update your status.'
close wi_cursor
deallocate wi_cursor
exec [dbo].[sp_send_cdontsmail] 'wiupdate@Server.com', 'user@server.com', 'Update Notification', @message
GO
----------------------------------
I'm guessing another variable needs to be set for the user's email and have the procedure do some kind of loop until all the user's have been emailed of their notification?
" exec [dbo].[sp_send_cdontsmail] 'wiupdate@Server.com', [B]@email[/B], 'Update Notification', @message "?
If anyone can help me out here, I would be very grateful! Thanks in adv!
JLM