B
Bob
we are using records from our HR system and attempting to create an event to
email to the supervisor when an employee schedules a day off. we have
created the following code that creates the event, attaches and sends the
event to the supervisor. When the supervisor attempts to open it, it fails
with the message to use a gregorian date instead of a lunar date. Is this a
formatting issue. It appears to work in the lastest version of Outlook but
in prior versions it fails. In lieu of upgrading all of our software, what
is in this code that is making it fail in previous versions?
USE [SawyerMisc]
GO
/****** Object: StoredProcedure [dbo].[SendEmployeeReviewEmails] Script
Date: 07/17/2008 10:16:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[SendPTOToSupervisors]
as
declare @Today datetime
set @Today = convert(datetime,convert(varchar,getdate(),101))
declare @FromDate datetime
declare @ThroughDate datetime
declare @SupervisorUsername varchar(51)
declare @EmployeeName varchar(51)
declare @Notes varchar(255)
declare @Hours int
declare @AccrualType varchar(10)
declare @CCs varchar(500)
declare @emailBody varchar(4000)
declare @emailSubject varchar(250)
declare curPTOs cursor for
select
open curPTOs
fetch next from curPTOs into @FromDate, @ThroughDate, @SupervisorUsername,
@EmployeeName,
@Notes, @Hours, @AccrualType, @emailBody, @emailSubject
while @@fetch_status = 0
begin
set @CCs = ''
set @emailSubject = 'Paid Time Off'
-- get the PTO approvals
select SupervisorUsername,
AccrOption,
FromDate,
ThroughDate,
Hours,
Notes,
EEName
from dbo.PTORequests
where DateProcessed = null
set @FromDate = FromDate
@ThroughDate = ThroughDate
@SupervisorUsername = SupervisorUsername + '@srhllc.com'
@EmployeeName = EEName
@Notes = Notes
@Hours = Hours
@AccrualType = AccrOption
@emailBody = '@EEName' + ' has scheduled ' + '@AccrOption' + ' time from '
+ '@FromDate' + ' through ' + '@ThroughDate' +
' for a total of ' + '@Hours' + ' hours.'
--set @SupervisorUsername = '(e-mail address removed)' --send to me when deving
--set @emailBody = + @emailBody
BEGIN:VCALENDAR
VERSION:2.0
PRODID:-//hacksw/handcal//NONSGML v1.0//EN
BEGIN:VEVENT
DTSTARTFromDate
DTENDThroughDate
SUMMARYTO
END:VEVENT
END:VCALENDAR
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'UltiProMail',
@recipients = @SupervisorUsername,
@copy_recipients = @CCs,
@subject = @emailSubject,
@body = @emailBody,
@body_format = 'HTML'
fetch next from fetch next from curPTOs into @FromDate, @ThroughDate,
@SupervisorUsername, @EmployeeName,
@Notes, @Hours, @AccrualType, @emailBody, @emailSubject
-- need an update of the record somewhere in the code
end -- cursor loop
close curPTOs
deallocate curPTOs
email to the supervisor when an employee schedules a day off. we have
created the following code that creates the event, attaches and sends the
event to the supervisor. When the supervisor attempts to open it, it fails
with the message to use a gregorian date instead of a lunar date. Is this a
formatting issue. It appears to work in the lastest version of Outlook but
in prior versions it fails. In lieu of upgrading all of our software, what
is in this code that is making it fail in previous versions?
USE [SawyerMisc]
GO
/****** Object: StoredProcedure [dbo].[SendEmployeeReviewEmails] Script
Date: 07/17/2008 10:16:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[SendPTOToSupervisors]
as
declare @Today datetime
set @Today = convert(datetime,convert(varchar,getdate(),101))
declare @FromDate datetime
declare @ThroughDate datetime
declare @SupervisorUsername varchar(51)
declare @EmployeeName varchar(51)
declare @Notes varchar(255)
declare @Hours int
declare @AccrualType varchar(10)
declare @CCs varchar(500)
declare @emailBody varchar(4000)
declare @emailSubject varchar(250)
declare curPTOs cursor for
select
open curPTOs
fetch next from curPTOs into @FromDate, @ThroughDate, @SupervisorUsername,
@EmployeeName,
@Notes, @Hours, @AccrualType, @emailBody, @emailSubject
while @@fetch_status = 0
begin
set @CCs = ''
set @emailSubject = 'Paid Time Off'
-- get the PTO approvals
select SupervisorUsername,
AccrOption,
FromDate,
ThroughDate,
Hours,
Notes,
EEName
from dbo.PTORequests
where DateProcessed = null
set @FromDate = FromDate
@ThroughDate = ThroughDate
@SupervisorUsername = SupervisorUsername + '@srhllc.com'
@EmployeeName = EEName
@Notes = Notes
@Hours = Hours
@AccrualType = AccrOption
@emailBody = '@EEName' + ' has scheduled ' + '@AccrOption' + ' time from '
+ '@FromDate' + ' through ' + '@ThroughDate' +
' for a total of ' + '@Hours' + ' hours.'
--set @SupervisorUsername = '(e-mail address removed)' --send to me when deving
--set @emailBody = + @emailBody
BEGIN:VCALENDAR
VERSION:2.0
PRODID:-//hacksw/handcal//NONSGML v1.0//EN
BEGIN:VEVENT
DTSTARTFromDate
DTENDThroughDate
SUMMARYTO
END:VEVENT
END:VCALENDAR
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'UltiProMail',
@recipients = @SupervisorUsername,
@copy_recipients = @CCs,
@subject = @emailSubject,
@body = @emailBody,
@body_format = 'HTML'
fetch next from fetch next from curPTOs into @FromDate, @ThroughDate,
@SupervisorUsername, @EmployeeName,
@Notes, @Hours, @AccrualType, @emailBody, @emailSubject
-- need an update of the record somewhere in the code
end -- cursor loop
close curPTOs
deallocate curPTOs