RE: Newbie help emailing a blob field
From: Nigel Rivett (sqlnr_at_hotmail.com)
Date: 11/07/04
- Next message: Gert-Jan Strik: "Re: Update statement in trigger gets bad execution plan"
- Previous message: Nigel Rivett: "RE: Is Redundant Data Ok?"
- In reply to: Dave the Project Portfolio Manager: "Newbie help emailing a blob field"
- Messages sorted by: [ date ] [ thread ]
Date: Sat, 6 Nov 2004 16:13:02 -0800
Haven't read all of this but to get the new info join to the table from the
inserted virtual table using the PK.
"Dave the Project Portfolio Manager" wrote:
> 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
>
- Next message: Gert-Jan Strik: "Re: Update statement in trigger gets bad execution plan"
- Previous message: Nigel Rivett: "RE: Is Redundant Data Ok?"
- In reply to: Dave the Project Portfolio Manager: "Newbie help emailing a blob field"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
