Newbie help emailing a blob field

From: Dave the Project Portfolio Manager (dave_at_att.com)
Date: 11/06/04 

Date: 6 Nov 2004 15:09:15 -0800

I'm trying to write a simple program for a project managemnet
application that emails a message to the assigned people when the
status of an issue changes. I'm ok with generating the message nad
sendign it out, my problem is how to send the updated 'Comments'
field. The trigger will not let me pass the field and the stored
procedure uses the 'embeded information'. What do I need to do to get
the latest update/inserted info? I am learning and am sure that its
simple, I've tried an instead of on the trigger with no luck. In my
stored procedure I'd like to use an updated view as it gets data from
multiple tables.

The online form includes fields in various tables so theire not all
inserted in the same table (thus one of the reasons I like the view)

I'm trying to learn this myself with this group, the SQL Guru book,
and examples. I know this is not pretty code, but perhaps you can
help. Using SQL 2000 version 7

tblissues layout )where the trigger is)
CREATE TABLE [dbo].[tblIssue] (
        [ID] [int] IDENTITY (1, 1) NOT NULL ,
        [WorkgroupID] [int] NULL ,
        [ProjectID] [int] NOT NULL ,
        [IssueNo] [int] NOT NULL ,
        [Title] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
        [Description] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
        [SubmittedByID] [int] NULL ,
        [ResponsibleID] [int] NULL ,
        [DateOpened] [datetime] NULL ,
        [DateDue] [datetime] NULL ,
        [DateCompleted] [datetime] NULL ,
        [Impact] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
        [Resolution] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
        [StatusFlowID] [int] NULL ,
        [StatusID] [int] NULL ,
        [dtStatusModified] [datetime] NULL ,
        [StatusModifiedByID] [int] NULL ,
        [PriorityID] [int] NULL ,
        [SeverityID] [int] NULL ,
        [TypeID] [int] NULL ,
        [TaskID] [int] NULL ,
        [Comments] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
        [OldID] [int] NULL ,
        [dtEntered] [datetime] NULL ,
        [EnteredByID] [int] NULL ,
        [dtModified] [datetime] NULL ,
        [ModifiedByID] [int] NULL ,
        [ExternalID] [int] NULL ,
        [ts] [timestamp] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

The view that has the information I desire: b2bi_issues

drop view b2bi_issues
go

CREATE view b2bi_issues
as
 
SELECT I.*, getdate() as 'todays_date', i.uniqueid as 'issueid',
case when datediff ("d", i.IssueDtDue, getdate())> 1 then 'pastdue'
else 'future_date' end as iss_due_past_future,
Res.ResName,
proj_names.ManagerID, proj_names.PM_last_Name,
proj_names.SponsorID, proj_names.CBPA_last_Name,
proj_names.OriginatorID, proj_names.EAAM_last_name,

dbo.fnb2b_user_def_text_get(I.uniqueid, 569, 2) as 'ACTT Ticket',
dbo.fnb2b_user_def_text_get(I.uniqueid, 530, 2) as 'MatrixRFR',
issue_rafr.resname as 'MatrixAFR' ,
dbo.fnb2b_user_def_text_get(I.uniqueid, 570, 2) as 'MR number',

U4.Value as 'Orig Due Date',
dbo.fnb2b_user_def_date_get( I.uniqueid, 534, 2 ) as 'Orig_Due_date',

U5.Value as 'MR Date',
dbo.fnb2b_user_def_date_get( I.uniqueid, 546, 2 ) as 'MR_date',

dbo.fnb2b_user_def_text_get(I.uniqueid, 547, 2) as 'MR Against',

dbo.fnb2b_long_only_get(I.uniqueid, 545, 2) as 'MR Yes_No',

U8.value as 'PDcode',

u8.userdefid as 'issue org ID', u8.recordid as 'org recordid',

U9.value as 'Retired CS',
U10.value as 'Org Owner',
U11.value as 'Status_report',
isnull (dbo.fnb2b_user_def_text_get(I.uniqueid, 606, 2), 'No impact
entered ') as 'iss_impact',

isnull(convert (varchar(500) , i.IssueComments), 'No comments') as
'xx_comments',
isnull(convert (varchar(500) , i.Issuefulldescription), 'No comments')
as 'xx_description',
issue_res.reslastname as 'responsible_last_name',
isnull (dbo.fnFirstResEmail(ResponsibleID), ' ') as 'res_email',
dbo.fnb2b_user_def_text_get(I.uniqueid, 684, 2) as 'ss_proj_schema',
dbo.fnb2b_user_def_text_get(I.uniqueid, 685, 2) as 'ss_proj_mapping',
u31b.value as 'ss_Proj_tab',
u32b.value as 'ss_template_category',
v1.value as 'forecast_volume',
v2.value as 'forecast_actual',
dbo.fnb2b_project_info (i.projid, 'projtype') as 'interface_media',
ISNULL (dbo.fnb2b_user_def_text_get(I.uniqueid, 741, 2),
'dave@att.com') as 'notification_email',

/* SET @Value = IsNull(@LastName,'') + IsNull(', ' + @FirstName,'') +
IsNull(' ' + @MI,'') */

isnull (dbo.fnFirstResEmail(ResponsibleID), ' ') as
'responsible_email',
isnull (dbo.fnFirstResEmail(proj_names.ManagerID), ' ') as 'pm_email',
isnull (dbo.fnFirstResEmail(proj_names.SponsorID), ' ') as 'ba_email',
isnull (dbo.fnFirstResEmail(proj_names.originatorID), ' ') as
'eaam_email',

isnull (dbo.fnFirstResEmail(ResponsibleID), ' ') + ';' +
isnull(dbo.fnFirstResEmail(proj_names.ManagerID), ' ') + ';'
+ isnull (dbo.fnFirstResEmail(proj_names.SponsorID), ' ') + ';' +
isnull (dbo.fnFirstResEmail(proj_names.originatorID), ' ') as
'first_email',

case i.priorityid when 66
then
        case i.severityid
        when 219 then 'Very High'
        when 204 then 'Very High'
        else i.issuepriority
        end
else i.issuepriority
end as 'veryhigh_status'

FROM InVision_dta.dbo.Issues I
left outer join InVision_dta.dbo.tblUserDefDate tblUserDefDate
on I.UniqueID = tblUserDefDate.RecordID and tbluserdefdate.userdefid =
536
left outer join InVision_dta.dbo.tblUserDefLong tblUserDefLong
on I.UniqueID = tblUserDefLong.RecordID and
tbluserdeflong.userdefid=536

left outer join InVision_dta.dbo.tblUserDefdate U4 on
I.UniqueID = U4.RecordID and U4.userdefid = 534
left outer join InVision_dta.dbo.tblUserDefdate
U5 on I.UniqueID = U5.RecordID and U5.userdefid = 546

left outer join InVision_dta.dbo.tblUserDeflong U8
on I.UniqueID = U8.RecordID and U8.userdefid = 541

left outer join InVision_dta.dbo.tblUserDeflong U9
on I.UniqueID = U9.RecordID and U9.userdefid = 527

left outer join InVision_dta.dbo.tblUserDeflong U11
on I.UniqueID = U11.RecordID and U11.userdefid = 578

left outer join InVision_dta.dbo.tblUserDeflong U2aa
on I.UniqueID = U2aa.RecordID and U2aa.userdefid = 680

left outer join InVision_dta.dbo.tblUserDefTextValidation U10
on U8.userdefid = U10.userdefid and U8.value = U10.id

left outer join inVision_dta.dbo.resources issue_rafr
on u2aa.value = issue_rafr.uniqueid

/* Resource info */
left outer join InVision_dta.dbo.Resources Res
on tblUserDefLong.Value = Res.UniqueID and
tbluserdeflong.userdefid=536

left outer join inVision_dta.dbo.resources issue_res
on issue_res.uniqueid=i.ResponsibleID
left outer join inVision_dta.dbo.b2bi_proj_lastnames proj_names
on i.projid=proj_names.projid

/* Pull Downs */
full outer join InVision_dta.dbo.tblUserDeflong u31a
on i.uniqueiD = u31a.RecordID and u31a.userdefid = 686
full outer join InVision_dta.dbo.tblUserDefTextValidation u31b
on u31a.value = u31b.id

full outer join InVision_dta.dbo.tblUserDeflong u32a
on i.uniqueiD = u32a.RecordID and u32a.userdefid = 687
full outer join InVision_dta.dbo.tblUserDefTextValidation u32b
on u32a.value = u32b.id

/* Volume Stuff */

full outer join InVision_dta.dbo.tblUserDeflong v1
on i.uniqueiD = v1.RecordID and v1.userdefid = 688
full outer join InVision_dta.dbo.tblUserDeflong v2
on i.uniqueiD = v2.RecordID and v2.userdefid = 689

where i.workgroupid = 17 and i.projid <> 530 /* doris mae */

My trigger (again not pretty)

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[b2b_tblissue_StatusUpdate]') and OBJECTPROPERTY(id,
N'IsTrigger') = 1)
drop trigger [dbo].[b2b_tblissue_StatusUpdate]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER b2b_tblissue_StatusUpdate ON tblissue FOR UPDATE AS
/*
 * Trigger Issue Status Change Alert
 */
IF UPDATE(StatusID)
begin
        DECLARE @WorkgroupID int
        SELECT @WorkgroupID=(SELECT WorkgroupID FROM inserted)
        If @WorkGroupID = 17
        begin
             DECLARE @ID int , @StatusID int , @StatusModifiedByID int ,
@dtStatusModified DateTime
            SELECT @ID=(SELECT ID FROM inserted)
               SELECT @StatusID=(SELECT StatusID FROM inserted)
            SELECT @StatusModifiedByID=(SELECT StatusModifiedByID FROM
inserted)
            SELECT @dtStatusModified=(SELECT dtStatusModified FROM inserted)
             EXEC spb2bissueAlert
@ID,@StatusID,@StatusModifiedByID,@dtStatusModified

        END
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

And my real ugly store procedure:

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[spb2bissueAlert]') and OBJECTPROPERTY(id,
N'IsProcedure') = 1)
drop procedure [dbo].[spb2bissueAlert]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE spb2bissueAlert @issueID int, @StatusID Int,
@StatusModBy int, @StatusModDate DateTime AS
Set NOCOUNT ON
Declare @Flags int
Declare @Header varChar(255)
Declare @Subject varchar(100)
Declare @projectid int

--- Use temporary table to send only one alert per resource ---
CREATE TABLE #MsgList (Message varchar(3000), ResourceID int, ListID
int, RecordID int,MyFlags int,LastName varchar(50))
INSERT INTO #MsgList

SELECT DISTINCT 'Project: ' + isnull(i.ProjNO , ' ')
+ ' - '
+ convert (varchar(35), i.ProjTitle)
+ char(10)
+ 'Issue status change. for issue # '
+ convert (varchar(5), i.IssueNo)
+ ' - ' + isnull(i.issuetitle, 'No Title ')
+ char(10) + char(10)
+ '"' + isnull(i.issuestatus, 'No Status ')
+ '" is the new status and was changed on '
+ Convert(varChar(10), i.IssueStatusModDt,101) + ' at ' +
Convert(Varchar(8),i. IssueStatusModDt,108)
+ ' by ' + isnull(Convert(Varchar(45),
(dbo.fnb2bresourceName(i.StatusModifiedByID,4))), ' ')
+ char(10) + char(10) + isnull (i.responsible_last_name, 'Nobody')
+ ' is responsible for resolution and it is due by: '
+ isnull ( Convert(varChar(10),IssueDtDue,101), ' ') + char(10)

+ char(10) + char (10) + 'The issue type is: ' + isnull ( i.IssueType
, ' ')
+ char(10) + 'The issue priority on reports is: ' + isnull
(i.veryhigh_status, ' ')
+ char(10) + 'The project manager is: ' + isnull ( i.PM_last_Name, '
')
+ char(10) + char(10)
 
Message,
i.managerID, 2 ListId,i.uniqueid RecordID,@Flags
MyFlags,i.pm_Last_Name
from b2bi_issues i

WHERE i.uniqueid = @issueid

Set @Flags=1
Set @Header = ''
Set @Subject='Project InVision Issue Status Change for: ' + ( SELECT
DISTINCT 'Project: ' + Px.ProjNO + ' - ' + px.projtitle from
b2bi_issues Px WHERE Px.uniqueid = @issueID )

--- Add to message queue ---
IF @Flags & 2 = 2
begin
    INSERT tblResourceMessageQueue (ToResourceID, Message, dtCreated,
ListID, RecordID)
    SELECT ResourceID, Message, GetDate(), ListID, RecordID FROM
#MsgList
END --IF

--- Send email ---
IF @Flags & 1 = 1
begin
    DECLARE @Message varchar(3257), @ResourceID int, @EmailAddress
varchar(150),@LastName varchar(50)
    DECLARE @Message1 varchar(3257) , @closing varchar(150),
@issuedesc varchar (500), @issueimpact varchar(254)
    Declare @currentres varchar (500), @issuecommentsx varchar (500) ,
@pm_email varchar(50)
    --- Create Cursor ---
    DECLARE curMsgList cursor FAST_FORWARD LOCAL FOR
    SELECT Message, ResourceID,LastName
    FROM #MsgList
    WHERE Len(Message)>0 --AND Exists(SELECT * FROM
tblResourceEMail WHERE ResourceID = #MsgList.ResourceID)

    --- Loop through cursor ---
    OPEN curMsgList
    FETCH NEXT FROM curMsgList into @Message, @ResourceID,@LastName
Set @issueimpact=char(10) + char(10) + 'Issue Impact: ' + ( SELECT
DISTINCT
CONVERT(varchar(254),px.iss_impact) from b2bi_issues Px WHERE
Px.uniqueid = @issueID )

              Select @pm_email=isnull(i.pm_email, 'dave@att.com')
        from b2bi_issues i WHERE i.uniqueid = @issueid

Set @issuedesc='Issue Description: ' + ( SELECT Convert
(varchar(254), Px.issuedescription)
from b2bi_issues Px WHERE Px.uniqueid = @issueID )

Set @currentres=char(10) + char(10) + 'Issue Resolution: ' + ( SELECT
 DISTINCT
isnull (CONVERT(varchar(500),px.IssueFullRes), 'No resolution
defined')
+ CASE WHEN DATALENGTH(Px.IssueFullRes)> 500 THEN '...' + char(10) +
char(10) + 'More in InVision' ELSE ' ' END
from b2bi_issues Px WHERE Px.uniqueid = @issueID )

Set @issuecommentsx=char(10) + char(10) + 'Issue Comments: ' + (
SELECT DISTINCT Px.xx_comments
from b2bi_issues Px WHERE Px.uniqueid = @issueID )

set @closing=char(10) + char(10) + 'Message sent by Project InVision
trigger version 1.2' + char(10) + 'for questions, email the project
manager : ' + @pm_email

Set @Message=@Message + @issuedesc + @issueimpact + @currentres +
@issuecommentsx + @closing

WHILE @@FETCH_STATUS = 0
    begin

              Select @EmailAddress=isnull(i.first_email, 'dave@att.com') + '
; ' + isnull(i.pm_email, 'dave@att.com') + ' ; ' +
isnull(i.notification_email, 'dave@att.com' ) + ';'+
        from b2bi_issues i WHERE i.uniqueid = @issueid

      /* print @EmailAddress */
        
 SET @Message1=@Message + @issuedesc + @issueimpact + @currentres +
@issuecommentsx + @closing

        EXEC master.dbo.xp_sendmail @recipients=@EmailAddress,
@query=@Message1, @subject=@Subject, @no_output='True',
@no_header='True', @width=3257
        FETCH NEXT FROM curMsgList into @Message, @ResourceID, @LastName
    END --While

 -- Send copy to B2Bi Group Box
  /* Select @EmailAddress = 'dave@ems.att.com' + ';' +
'dave.davis@sev.org' */
     Select @EmailAddress=first_email + ' ; ' + notification_email
from b2bi_issues i WHERE i.uniqueid = @issueid
 
      Select @EmailAddress=isnull(i.first_email, 'dave@att.com') + ' ;
' + isnull(i.pm_email, 'dave@att.com') + ' ; ' +
isnull(i.notification_email, 'dave@att.com' ) + ';'+
            from b2bi_issues i WHERE i.uniqueid = @issueid
    
SET @Message1 = 'SELECT ''' + @Header + CHAR(10) + CHAR(10) +
@Message + ''''
            EXEC master.dbo.xp_sendmail @recipients=@EmailAddress,
@query=@Message1, @subject=@Subject, @no_output='True',
@no_header='True', @width=3257

    --- Close cursor ---
    CLOSE curMsgList
    DEALLOCATE curMsgList
END --IF

DROP Table #MsgList
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

So how can I get it to be the new issue comments and the new 'status'
in the email?

Thanks for you help.

We're All in This Together
Dave

Relevant Pages

  • Re: SQL Server Functions
    ... CREATE FUNCTION FN_IMS_RX_ITEMS (@DateTimeOfExtract DATETIME, ... > @DataSupplyGroup INT) ... > DispensedPIP INT, ... > LEFT OUTER JOIN ...
    (microsoft.public.sqlserver.programming)
  • Re: Sort, Page, Filter, Count of filtered rows ?
    ... COUNTOVER(PARTITION BY PensionFundID) ... I can't see any way of doing this apart from repeating the WHERE clause ... @QueryRowCount Int Output, ... LEFT OUTER JOIN dbo.CurrencyConverter Cu ...
    (microsoft.public.sqlserver.programming)
  • Need Help Optimizing a SPROC
    ... @CarrierID int, ... RequestorID int, ... StatusID int, ... RIGHT OUTER JOIN FNOs INNER JOIN IMSI ON FNOs.FNOID = IMSI.CarrierID ...
    (microsoft.public.sqlserver.programming)
  • RE: newbie, querie help/advice
    ... dbo.STUDENT.Student_ID LEFT OUTER JOIN ... where course_name = 'database' ... > Which students are enrolled in both Databases and Networking? ... > Student_ID int NOT NULL, ...
    (microsoft.public.sqlserver.programming)
  • Re: Why data could not be committed into table?
    ... int default 0, ... CREATE TRIGGER Feeds_update_trg ... DECLARE @PrevClose decimal ... > production system, execute it through QA against that same production ...
    (microsoft.public.sqlserver.programming)