August 28, 2010 at 2:05 pm
I'm a noob... so I have a dumb question. Say I have a table of "Studies" and a table of "Staff". Staff--(1,M)---Studies.
Say for the sake of argument that I want the database to mail each staff member a list of his active Studies. (Hey, I'm learning, so it's a somewhat contrived example.)
Can I do this without a cursor?
Maybe a function that returns the given @StaffID's assigned studies... but then I still have to process the list of staff one at a time, right?
August 28, 2010 at 2:20 pm
pietlinden (8/28/2010)
I'm a noob... so I have a dumb question. Say I have a table of "Studies" and a table of "Staff". Staff--(1,M)---Studies.Say for the sake of argument that I want the database to mail each staff member a list of his active Studies. (Hey, I'm learning, so it's a somewhat contrived example.)
Can I do this without a cursor?
Maybe a function that returns the given @StaffID's assigned studies... but then I still have to process the list of staff one at a time, right?
You can certainly format all the emails without a cursor and you can email all the emails without a cursor but it brings NOTHING to the table because... the cursor isn't the slow thing on this problem.
I can help you format each individual email without a cursor because THAT is a performance problem. Have you any test data to play with? (See first link in my signature line below for how to format it to make it readily usable).
--Jeff Moden
Change is inevitable... Change for the better is not.
August 28, 2010 at 3:05 pm
Staff--(1,M)---Protocol
USE [SCRIDB]
GO
/****** Object: Table [dbo].[Staff] Script Date: 08/28/2010 15:54:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Staff](
[StaffID] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[LastName] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Department] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Role] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EMail] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_Staff] PRIMARY KEY CLUSTERED
(
[StaffID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
USE [SCRIDB]
GO
/****** Object: Table [dbo].[Protocol] Script Date: 08/28/2010 15:45:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Protocol](
[ProtocolNo] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[PrincipalInvestigatorID] [int] NULL,
[StudyManagerID] [int] NULL,
[OpenToEnrollDate] [datetime] NULL,
[CloseToEnrollDate] [datetime] NULL,
[EnrollmentGoal] [int] NULL,
[IsOpen] [bit] NULL CONSTRAINT [DF_Protocol_IsOpen] DEFAULT ((0)),
[NotifyOfOpenDate] [smalldatetime] NULL,
CONSTRAINT [PK_Protocol] PRIMARY KEY CLUSTERED
(
[ProtocolNo] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
/ * INSERTs for Staff */
INSERT INTO [SCRIDB].[dbo].[Staff]
([FirstName]
,[LastName]
,[Department]
,[Role]
,[EMail])
VALUES
('Donald'
,'Duck'
,'Toons'
,'Study Manager'
,'dduck@toons.com');
INSERT INTO [SCRIDB].[dbo].[Staff]
([FirstName]
,[LastName]
,[Department]
,[Role]
,[EMail])
VALUES
('Mickey'
,'Mouse'
,'Toons'
,'Study Manager'
,'mmouse@toons.com');
/* INSERTs for Protocol */
INSERT INTO Protocol(ProtocolNo, StudyManagerID,OpenToEnrollDate, CloseToEnrollDate, EnrollmentGoal, IsOpen)
VALUES ('BRE150', 1, '2010-08-26 22:01:56.857', NULL, 250,1);
INSERT INTO Protocol(ProtocolNo, StudyManagerID,OpenToEnrollDate, CloseToEnrollDate, EnrollmentGoal, IsOpen)
VALUES ('LUN120', 1, '2010-08-26 22:01:56.857', NULL, 50,1);
INSERT INTO Protocol(ProtocolNo, StudyManagerID,OpenToEnrollDate, CloseToEnrollDate, EnrollmentGoal, IsOpen)
VALUES ('LUN189', 2, '2010-08-26 22:01:56.857', NULL, 50,1);
INSERT INTO Protocol(ProtocolNo, StudyManagerID,OpenToEnrollDate, CloseToEnrollDate, EnrollmentGoal, IsOpen)
VALUES ('LUN90', 2, '2010-08-26 22:01:56.857', NULL, 20,1);
-- End of "code"
Where I was going with this was creating a stored procedure to format/send the e-mails and then running that on a schedule. (Basically, so if the boss assigns me a new Protocol, I get an automatic e-mail notifying me of it). The reason I was thinking I needed a cursor was to select the open protocols for each Study Manager and e-mail a "report" that's just text -
list of open protocols, with Enrollment goal, open date, etc.
Hopefully I didn't leave anything out... if I did, let me know.
Thanks!
Pieter
August 30, 2010 at 9:07 pm
The only thing I'm missing is how YOU call the EXEC that sends an email for you. (Trying to give you a complete solution).
--Jeff Moden
Change is inevitable... Change for the better is not.
August 30, 2010 at 10:08 pm
Jeff Moden (8/30/2010)
The only thing I'm missing is how YOU call the EXEC that sends an email for you. (Trying to give you a complete solution).
I would call the send e-mail code in one of the following ways:
1. create a "digest" of each study manager's open protocols and e-mail it, so by using a cursor). This kind of thing would probably be run as a job at night. (use the StudyManagerID to retrieve the related email address from the Staff table.)
2. create a trigger in the AFTER INSERT/UPDATE event of the Protocol table (use the inserted/deleted virtual tables to send "assign"/"deassign" e-mails).
3. create stored procedures that do the INSERT/UPDATEs and just call the sendmail code from there.
Does this answer your question? Thanks for the help!
Pieter
August 30, 2010 at 10:28 pm
pietlinden (8/30/2010)
Jeff Moden (8/30/2010)
The only thing I'm missing is how YOU call the EXEC that sends an email for you. (Trying to give you a complete solution).I would call the send e-mail code in one of the following ways:
1. create a "digest" of each study manager's open protocols and e-mail it, so by using a cursor). This kind of thing would probably be run as a job at night. (use the StudyManagerID to retrieve the related email address from the Staff table.)
2. create a trigger in the AFTER INSERT/UPDATE event of the Protocol table (use the inserted/deleted virtual tables to send "assign"/"deassign" e-mails).
3. create stored procedures that do the INSERT/UPDATEs and just call the sendmail code from there.
Does this answer your question? Thanks for the help!
Pieter
Sorry, no. You must call a stored procedure to send the email, right? sp_send_dbmail is what most folks use now. What is the callout YOU use for that? Be sure to include the variables you use for the "To, From, Body", etc of the email.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 31, 2010 at 11:55 pm
Jeff Moden (8/30/2010)
pietlinden (8/30/2010)
Jeff Moden (8/30/2010)
The only thing I'm missing is how YOU call the EXEC that sends an email for you. (Trying to give you a complete solution).
I think I'm going backwards...
I rewrote some of the code, and when it gets to
exec dbo.sp_send_dbmail
@profile_name = 'Notifier_Profile',
@recipients = @EMail,
@subject = @MsgSubject,
@body = @MsgBody
I get the following message:
Cannot add rows to sysdepends for the current object because it depends on the missing object 'dbo.sp_send_dbmail'. The object will still be created.
Not sure I even understand what the error means...
Here's the entirety of the code, in case it helps...
USE [SCRIDB]
GO
/****** Object: Trigger [Enroll_INSERT] Script Date: 09/01/2010 00:27:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [Enroll_INSERT]
ON [dbo].[Enroll]
AFTER INSERT
AS
-- Send a notification to the Study Manager when the
--enrollment record is inserted
DECLARE @StaffName VARCHAR(50),
@EMail VARCHAR(50),
@PatientID int,
@TimeNow datetime,
@ProtocolNo VARCHAR(20),
@MsgSubject VARCHAR(100),
@MsgBody VARCHAR(500)
SELECT @StaffName = Staff.FirstName + ' ' + Staff.LastName,
@EMail = Staff.EMail,
@PatientID = i.e_PatientID,
@TimeNow = getdate(),
@ProtocolNo = i.e_ProtocolNo,
@MsgSubject = 'New Patient enrolled in ' + CAST(i.e_ProtocolNo AS VARCHAR),
@MsgBody = 'You got a new patient!'
FROMinserted as i
INNER JOINProtocol ON i.e_ProtocolNo = Protocol.ProtocolNo
INNER JOIN Staff ON Protocol.StudyManagerID = Staff.StaffID
Print 'Enroll_INSERT_TRIGGER'
Print @StaffName
Print @EMail
Print @PatientID
Print @TimeNow
exec dbo.sp_send_dbmail
@profile_name = 'Notifier_Profile',
@recipients = @EMail,
@subject = @MsgSubject,
@body = @MsgBody
print 'Message hopefully sent!'
September 1, 2010 at 6:13 am
Perfect. I'll be back. Sorry this is taking so long. Work is a bit hectic and the delays between our posts make things a bit slow. I should be able to help you finish this when I get home tonight... I believe you're gonna like it a lot.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 1, 2010 at 10:00 am
Cool, thanks! I can wait...
Pieter
September 2, 2010 at 4:07 am
One thing, the sp_send_dbmail proc is in the msdb database your code should look like this:
EXEC msdb.dbo.sp_send_dbmail ...
September 2, 2010 at 10:21 am
Thanks for the catch Nigel... coming from baby database land (Access) where everything is pretty much in one database, this takes some getting used to!
September 4, 2010 at 4:18 pm
Sorry this took so long... I got pretty far behind and I thought I was going to have to be twins to catch up. I hope you'll think it was worth the wait.
Here's some test data creation code, 2 procs, and a couple of EXEC statments (to demonstrate) for emailing Protocols without cursors. As usual, you'll find the details for how it works in the comments. All that's left for you to do is to replace "#" with "dbo." in the stored procedures and you should be able to use them as is. Let me know how this all works out for you. Thanks.
/**********************************************************************************************************************
Create the test tables. THIS SECTION IS NOT A PART OF THE SOLUTION!!! It just creates some test data.
**********************************************************************************************************************/
--===== Conditionally drop the test tables to make reruns easier
IF OBJECT_ID('TempDB..#Staff') IS NOT NULL DROP TABLE #Staff;
IF OBJECT_ID('TempDB..#Protocol') IS NOT NULL DROP TABLE #Protocol;
--===== Create the test tables
CREATE TABLE #Staff
(
StaffID int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
FirstName varchar(15) NOT NULL,
LastName varchar(20) NOT NULL,
Department varchar(50) NULL,
Role varchar(20) NULL,
EMail varchar(50) NULL,
)
;
CREATE TABLE #Protocol
(
ProtocolNo varchar(30) NOT NULL PRIMARY KEY CLUSTERED,
PrincipalInvestigatorID int NULL,
StudyManagerID int NULL,
OpenToEnrollDate datetime NULL,
CloseToEnrollDate datetime NULL,
EnrollmentGoal int NULL,
IsOpen bit NULL DEFAULT ((0)),
NotifyOfOpenDate smalldatetime NULL,
)
;
--===== Populate the test tables with data
INSERT INTO #Staff
(FirstName,LastName,Department,Role,EMail)
SELECT 'Donald','Duck','Toons','Study Manager','dduck@toons.com' UNION ALL
SELECT 'Mickey','Mouse','Toons','Study Manager','mmouse@toons.com'
;
INSERT INTO #Protocol
(ProtocolNo, StudyManagerID,OpenToEnrollDate, CloseToEnrollDate, EnrollmentGoal, IsOpen)
SELECT 'BRE150', 1, '2010-08-26 22:01:56.857', NULL, 250,1 UNION ALL
SELECT 'LUN120', 1, '2010-08-26 22:01:56.857', NULL, 50,1 UNION ALL
SELECT 'LUN189', 2, '2010-08-26 22:01:56.857', NULL, 50,1 UNION ALL
SELECT 'LUN90', 2, '2010-08-26 22:01:56.857', NULL, 20,1
;
GO
CREATE PROCEDURE dbo.SendProtocol
/**********************************************************************************************************************
Purpose:
Send an email for all open Protocols for a given Study Manager ID.
(This IS a part of the solution)
**********************************************************************************************************************/
--===== Declare I/O Parameters
@pStudyManagerID INT
AS
--===== Environmental Presets
SET NOCOUNT ON;
--===== Declare local variables
DECLARE @Body NVARCHAR(MAX),
@Email NVARCHAR(50),
@FirstName NVARCHAR(15)
;
--===== Get the email address and name for the given study manager id
SELECT @FirstName = FirstName,
@EMail = Email
FROM #Staff
WHERE StaffID = @pStudyManagerID
;
--===== Create the body of the message including a nicely formatted table of the protocols
SELECT @Body =
------- Create the table and the table header. (Easily readable HTML)
'
<html>
<body>
Dear ' + @FirstName + ',
The following table contains a list of your open Protocols.
<table border="1" cellspacing="0" style="text-align:center">
<caption>Protocols</caption>
<tr style="background-color:AliceBlue"><th>Protocol No</th><th>Open to Enrollment Date</th><th>Enrollment Goal</th></tr>
'
------- Create the rest of the table. Filled in from data in the table.
+ SPACE(8)
+ REPLACE( --This just indents each row to make rows in the HTML apparent and easy to read
CAST((SELECT td = ProtocolNo, N'', --<td></td> = "data" element in a row
td = CONVERT(NCHAR(11), OpenToEnrollDate, 106), N'',
td = CAST(EnrollmentGoal AS NVARCHAR(10)), N''
FROM #Protocol
WHERE IsOpen = 1
AND StudyManagerID = @pStudyManagerID
FOR XML PATH('tr'),TYPE --<tr></tr> = row encapsulation (The XML does a bunch of concatenation)
)AS NVARCHAR(MAX))
,'</tr>','</tr>'+CHAR(10)+SPACE(8))
------- Finalize the HTML
+ '
</table>
</body>
</html>'
;
--===== All set. Send the email.
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Notifier_Profile',
@recipients = @EMail,
@subject = 'Your open Protocols',
@body = @Body
;
GO
CREATE PROCEDURE dbo.SendAllProtocols
/**********************************************************************************************************************
Purpose:
Send an email for all open Protocols for each Study Manager ID that has open Protocols.
(This IS a part of the solution)
**********************************************************************************************************************/
--===== Declare I/O Parameters
-- No parameters required for this proc
AS
--===== Environmental Presets
SET NOCOUNT ON;
--===== Declare local variables
DECLARE @SendAll VARCHAR(MAX);
WITH cteStaffProtocol AS
( --=== This creates a list of all Staff IDs with at least 1 open protocol
SELECT DISTINCT s.StaffID
FROM #Staff s
INNER JOIN #Protocol p
ON s.StaffID = p.StudyManagerID
AND p.IsOpen = 1
) --=== This creates a list of commands to send protocol emails from the list of Staff IDs
SELECT @SendAll = (SELECT 'EXEC dbo.SendProtocol ' + CAST(StaffID AS VARCHAR(10)) + CHAR(10)
FROM cteStaffProtocol sp
FOR XML PATH('')
)
;
--===== This executes ALL those commands to send ALL the emails
EXEC (@SendAll);
GO
--===== We're ready to rock... this will send ALL the emails...
EXEC dbo.SendAllProtocols
--===== ... or, you can send just one staff member his/her Protocol list
EXEC dbo.SendProtocol 2
--Jeff Moden
Change is inevitable... Change for the better is not.
September 4, 2010 at 4:25 pm
Ah... almost forgot...
There is no way that I would execute sp_send_dbmail in a trigger. If the email service goes down, new entries to the tables will either fail or wait for a long time. Whatever... they won't work.
My recommendation is to change the trigger to populate a table with the new information and run a job once every minute or so (if it's THAT important) to send the emails for the "You have new patients" thing. That way, you can still collect new patient notifications even if the email server is down. When it comes back up, everything will run like nothing ever happened.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply