Mansi T.

asked • 01/03/20

I have developed this but email goes blank but actualy there is data in the table?? below i have excluded try catch block

ALTER PROCEDURE [dbo].[spReportDataSync_log]

AS

BEGIN

declare @last date = DATEADD(DAY, (DATEDIFF(DAY, 4, GETDATE()) / 7) * 7-7 , 4)

declare @current date = DATEADD(DAY ,7, @last)

DECLARE @Previous_week_date nvarchar(max),

@Previous_week_count nvarchar(max),

@Current_week_date nvarchar(max),

@Current_week_count nvarchar(max),

@Difference nvarchar(max),

@table_name nvarchar(max),

@ID nvarchar(max),

@Check nvarchar(10) = 'FALSE',

@SQLEmail nvarchar(max) = '',

@Email_Body nvarchar(max),

@useTransaction AS bit = 1,

@path AS nvarchar(100),

@returnResult AS bit = 1,

@printQuery AS bit = 1,

@message nvarchar(400),

@dcount nvarchar(400),

@Email_Body_fail nvarchar(400),

@status int


SET NOCOUNT ON;

BEGIN TRY

--Begin Transaction


IF @useTransaction = 1 --and @@trancount <= 0 and XACT_STATE() = 0

BEGIN TRANSACTION


-------TRUNCATE PARTICULAR COLUMN----------------

update lc set table_name = NULL from log_compare lc

update lc set last_week_date = NULL from log_compare lc

update lc set last_week_count = NULL from log_compare lc

update lc set current_week_date = NULL from log_compare lc

update lc set current_week_count = NULL from log_compare lc

update lc set difference = NULL from log_compare lc


select @status = isnull(@status,0) + 1


-----------TABLE NAME-----------------

update lc set table_name = SIP_tbl.name

--select distinct notelong,[name] as TableName,substring(notelong,charindex(':',notelong)+1, len(notelong)) RecordCount

from log_compare lc,

(select * from sys.tables

) SIP_tbl

where notelong like '%'+SIP_tbl.name+'%' and logDateInsert between @last and @current


---------LAST WEEK DATE----------------------

update lc set last_week_date = @last

from log_compare lc,(select * from sys.tables

) SIP_tbl

where notelong like '%'+SIP_tbl.name+'%' and logDateInsert between @last and @current


---------LAST WEEK COUNT----------

update lc set last_week_count =

(select substring(notelong,charindex(':',notelong)+1, len(notelong)) )

from log_compare lc,

(select * from sys.tables

) SIP_tbl

where notelong like '%'+SIP_tbl.name+'%' and logDateInsert = @last


----------CURRENT WEEK DATE---------------

update lc set current_week_date = @current

from log_compare lc,(select * from sys.tables

) SIP_tbl

where notelong like '%'+SIP_tbl.name+'%' and logDateInsert between @last and @current


--------------CURRENT WEEK COUNT-----------------------

update lc set current_week_count=

(select substring(notelong,charindex(':',notelong)+1, len(notelong)) )

from log_compare lc,

(select * from sys.tables

) SIP_tbl

where notelong like '%'+SIP_tbl.name+'%' and logDateInsert = @current


------DIFFERENCE--------------

--update lc set difference =

--(select convert(int,last_week_count) - convert(int,current_week_count) )from log_compare lc


-----------------EMAIL BODY---------------------

SELECT @PREVIOUS_WEEK_DATE = Last_week_date , @PREVIOUS_WEEK_COUNT= Last_week_count,@CURRENT_WEEK_DATE= current_week_date,@CURRENT_WEEK_COUNT= current_week_count , @TABLE_NAME = table_name FROM log_compare where id_Log = @ID


SET @SQLEMAIL = @SQLEMAIL + '<TR ><TD >' + ISNULL(@PREVIOUS_WEEK_DATE, '') + '</TD><TD >' + ISNULL(@PREVIOUS_WEEK_COUNT, '') + '</TD><TD >' + ISNULL(@CURRENT_WEEK_DATE, '') + '</TD><TD>' + ISNULL(@CURRENT_WEEK_COUNT, '') + '</TD><TD >' + ISNULL(@TABLE_NAME, '') + '</TD></TR>'

--select @status = isnull(@status,0) + 0


SET @Email_body = '<!DOCTYPE html><html>

<head>

<style>

table, th, td {

border: 1px solid black;

border-collapse: collapse;}

</style>

</head>

<p>Hello Team</p>

<p>Below is the data sync report for SIP portal to integration layer .</p>

<body>'


SET @Email_body = @Email_body + '<center><table><TH>PREVIOUS_WEEK_DATE</TH><TH>PREVIOUS_WEEK_COUNT</TH><TH >CURRENTT_WEEK_DATE</TH><TH >CURRENTT_WEEK_COUNT</TH><TH>TABLE_NAME</TH>'

SET @Email_body = @Email_body + @SQLEmail + '</table></center>'

IF (@status != 116)

SET @Email_Body = @Email_Body + '<P>NOTE : Logs are not maintained for all the tables . </P>'


SET @Email_body = @Email_body + +'<p>Thanks</p><p>** This is a system generated mail .</p></body></html>'



PRINT @Email_body


select @Email_body = case when isnull(@status,0) = 0 then @Email_body_fail else @Email_body end

select @status,@Email_body

Exec [spSendMail_SIP]

@smtpserver = ''

,@smtpserverport = '25'

,@From = ''

,@To = ''

,@Subject = 'REPORT_DATA_SYNC'

,@Body = @Email_body

,@attachment = null

,@printQuery = 0

1 Expert Answer

By:

Still looking for help? Get the right answer, fast.

Ask a question for free

Get a free answer to a quick problem.
Most questions answered within 4 hours.

OR

Find an Online Tutor Now

Choose an expert and meet online. No packages or subscriptions, pay only for the time you need.