1. Mail Send from SQL
USE [Timesheet09-Oct-2009]
GO
/****** Object: StoredProcedure [dbo].[pcWarantyStartDatePreAlert] Script Date: 08/04/2010 09:21:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[pcWarantyStartDatePreAlert]
-- @PONumber VARCHAR(100),
-- @TimeIntervalId INT
AS
BEGIN
BEGIN TRY
IF EXISTS( SELECT 0 FROM [Timesheet09-Oct-2009].[dbo].[Projects] WHERE CONVERT(CHAR(10), WarrantyStartDate, 101) = CONVERT(CHAR(10),GETDATE(),101))
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'WarrantyStartDateAlerts',
@recipients = 'mahadevan.v@congruentindia.com;',
@copy_recipients = 'SET NOCOUNT ON
SELECT EmailAddress From [User]
JOIN (SELECT ResponsiablePersonID, PROJECTID FROM Projects
WHERE CONVERT(CHAR(10), WarrantyStartDate, 101) = CONVERT(CHAR(10),GETDATE(),101))
ProjectRes ON [user].USerID = ProjectRes.ResponsiablePersonID
SET NOCOUNT OFF',
@query = 'SET NOCOUNT ON
SELECT [ProjectName]
,[RegionMaster].[RegionName]
,[Status]
,[User].Firstname
,[WarrantyStartDate]
,[WarrantyDays]
,[WarrantyEndDate]
FROM [Timesheet09-Oct-2009].[dbo].[Projects]
LEFT JOIN [Timesheet09-Oct-2009].[dbo].[RegionMaster] ON Projects.[RegionId] = RegionMaster.RegionID
LEFT JOIN [Timesheet09-Oct-2009].[dbo].[USER] ON Projects.[ResponsiablePersonID] = [USER].UserID
WHERE CONVERT(CHAR(10), WarrantyStartDate, 101) = CONVERT(CHAR(10),GETDATE(),101)
SET NOCOUNT OFF',
@subject = 'The Warranty date for the project listed starts from tomorrow'
END
END TRY
BEGIN CATCH
DECLARE @Error VARCHAR(1000)
SET @Error = ERROR_MESSAGE()
RAISERROR(@Error, 16, 1)
END CATCH
END
Mail Send from SQL Format 2
USE [Timesheet09-Oct-2009]
GO
/****** Object: StoredProcedure [dbo].[pcWarantyStartDateAlert] Script Date: 08/04/2010 10:58:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[pcWarantyStartDateAlert]
AS
BEGIN
SET NOCOUNT ON;
DECLARE @tableHTML NVARCHAR(MAX)
DECLARE @LeadMailID NVARCHAR(MAX)
DECLARE Mail_Cursor CURSOR FOR
SELECT EmailAddress From [User]
JOIN (SELECT ResponsiablePersonID, PROJECTID FROM Projects
WHERE CONVERT(CHAR(10), WarrantyStartDate, 101) = CONVERT(CHAR(10),GETDATE(),101))
ProjectRes ON [user].USerID = ProjectRes.ResponsiablePersonID
OPEN Mail_Cursor
FETCH NEXT FROM Mail_Cursor
INTO @LeadMailID
WHILE @@FETCH_STATUS = 0
BEGIN
SET @LeadMailID = REPLACE(@LeadMailID,',',';')
FETCH NEXT FROM Mail_Cursor
INTO @LeadMailID
END
CLOSE Mail_Cursor
DEALLOCATE Mail_Cursor
SET @tableHTML =
N'<H1>Project Waranty Start Date Alert</H1>' +
N'<table border="1">' +
N'<tr><th>SNo</th><th>Projecdt Name</th><th>Region Name</th>' +
N'<th>Status</th><th>Responsible Person</th><th>Waranty Start Date</th>' +
N'<th>Warranty Days</th><th>Warranty End Date</th></tr>' +
CAST ( ( SELECT td = ROW_NUMBER() OVER (ORDER BY [ProjectName]), ''
,td = [ProjectName], ''
,td = [RegionMaster].[RegionName],''
,td =[Status], ''
,td =[User].Firstname, ''
,td =[WarrantyStartDate], ''
,td =[WarrantyDays], ''
,td =[WarrantyEndDate], ''
FROM [Timesheet09-Oct-2009].[dbo].[Projects]
LEFT JOIN [Timesheet09-Oct-2009].[dbo].[RegionMaster] ON Projects.[RegionId] = RegionMaster.RegionID
LEFT JOIN [Timesheet09-Oct-2009].[dbo].[USER] ON Projects.[ResponsiablePersonID] = [USER].UserID
WHERE CONVERT(CHAR(10), WarrantyStartDate, 101) = CONVERT(CHAR(10),GETDATE(),101)
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;
EXEC msdb.dbo.sp_send_dbmail
@subject = 'The Warranty date for the project listed starts Today',
@profile_name = 'WarrantyStartDateAlert2',
@recipients = @LeadMailID,
@copy_recipients = 'mahadevan.v@congruentindia.com',
@body = @tableHTML,
@body_format = 'HTML' ;
SET NOCOUNT OFF;
END
<!--
USE [BIServer]
GO
/****** Object: StoredProcedure [dbo].[sp_BIServerHealthCheckMail] Script Date: 11/25/2010 15:01:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[sp_BIServerHealthCheckMail]
AS
BEGIN
SET NOCOUNT ON;
DECLARE @tableHTML NVARCHAR(MAX)
DECLARE @PingCount INT
SELECT @Pingcount = Count(*) from PingServer
SET @tableHTML =
N'<H1>CIT Server Health Check</H1>' +
N'<table border="1">' +
N'<tr><th>SNo</th><th>SYSTEM NAME</th>' +
N'<th>PING RESULT</th>' +
N'<th>SERVER STATUS</th>' +
N'<th>PROCESSED DATE</th></tr>' +
CAST ( ( SELECT td = ROW_NUMBER() OVER (ORDER BY Ping DESC, PingServer.[CITServer]), ''
,td = PingServer.[CITServer], ''
,td = PING,''
,td = CASE WHEN PING like '%Request timed out%' THEN 'Connection Failed'
ELSE 'Connection Succeeded' END, ''
,td =GETDATE(), ''
FROM [BIServer].[dbo].[PingServer]
JOIN (SELECT [CITServer],MAX(SERID) AS SIDS
FROM [BIServer].[dbo].[PingServer]
GROUP BY [CITServer]
) SERPING ON PingServer.SERID = SERPING.SIDS
ORDER BY PING DESC, PingServer.[CITServer]
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;
IF (@PingCount >0)
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@subject = 'CIT SERVER HEALTH CHECK',
@profile_name = 'BIServerHealthCheck',
@recipients = 'sathishkumarg@congruentindia.com;Chalapathi.M@congruentindia.com;Satheeshkumar.m@congruentindia.com',
@copy_recipients = 'vinod.s@congruentindia.com;mahadevan.v@congruentindia.com',
@body = @tableHTML,
@body_format = 'HTML' ;
END
SET NOCOUNT OFF;
END
No comments:
Post a Comment