January 9, 2020 at 9:22 am
Hi,
In my sql server database, there is table called Ticket_NUM and there are few fields like
TicketDate, TicketNum,UploadDate,Amount, Name, DOB
I want to find if there any duplicate records based on these columns(TicketDate, TicketNum,UploadDate,Amount) loaded for current date and time in the table , if yes find those records and send email notification to user by attaching duplicate records.
Is there any process which can keep running and check every 10 minute against this table to find duplicate records for current date and time and send email notification to users.
January 9, 2020 at 9:36 am
Yes, write a query to identify any such duplicates. Put it in a job and use Database Mail to notify the user if duplicates are found.
I have to ask, would it not be easier to prevent duplicates from getting in the table in the first place? You can do that with a primary key or unique constraint.
John
January 9, 2020 at 9:48 am
Thanks John for reply.
Yes due to some reason we can't stop loading duplicate at first place. we can't modify the table schema as it used by other processes.
How I can use attachment and send email through job.Any help with sql script can be helpful.
January 10, 2020 at 11:48 am
Hi, I have setup sql job and used below query to get the data and then send email as attachment
select @query = 'SET NOCOUNT ON;select TicketDate, TicketNum,UploadDate,Amount, Name, DOB from Ticket_Num
group by TicketDate, TicketNum,UploadDate,Amount
having count(*)>1 and TICKETNUM is not null
and Name in (''VS Day Cash'',''VS Non Cash'')
and CONVERT(varchar, UploadDate, 23)=CONVERT(varchar, getdate(), 23) order by DATE desc;SET NOCOUNT OFF'
exec msdb..sp_send_dbmail @recipients = @recip,
@subject = @subject,
@body = @body,
@query = @query,
@attach_query_result_as_file = 1,
@query_attachment_filename = @attch,
@query_result_width = 400
I am getting error while running the sql job.
The exception is " Incorrect Syntax near VS"
I am suing double quote , not sure where is the issue in syntax.
Can someone please assist with correct sql.
January 10, 2020 at 12:02 pm
You've put this in a job step, right? Please will you post the whole code from the job step? I suspect that what you've posted is only a snippet, since otherwise you'd get an error about variables not having been declared.
You might consider only sending the e-mail if duplicates are found, otherwise the users you're sending it to may start to get annoyed!
John
January 10, 2020 at 12:15 pm
Yes I have put in job step.
full script as requested.
declare @subject varchar(255)
, @query nvarchar(4000)
, @body varchar(255)
, @sep varchar(1)
, @att varchar(50)
, @recip varchar(255)
select @recip = 's.smith@xyz.com;'
select @body = ''
select @subject = 'Duplicate Data - ' + convert(varchar, getdate()-1, 106)
select @sep = char(9)
select @att = 'Duplicate Data' + convert(varchar,getdate()-1, 112) + '.csv'
declare @cnt int
Select @cnt= count(*)
from
(
select TicketDate, TicketNum,UploadDate,Amount, Name, DOB from Ticket_Num
group by TicketDate, TicketNum,UploadDate,Amount
having count(*)>1 and TICKETNUM is not null
and Name in(''VS Day Cash'',''VS Non Cash'')
and CONVERT(varchar, UploadDate, 23)=CONVERT(varchar, getdate(), 23)
) newtable
Print @cnt
IF @cnt >1
Begin
select @query = 'SET NOCOUNT ON;select TicketDate, TicketNum,UploadDate,Amount, Name, DOB from Ticket_Num
group by TicketDate, TicketNum,UploadDate,Amount
having count(*)>1 and TICKETNUM is not null
and Name in(''VS Day Cash'',''VS Non Cash'')
and CONVERT(varchar, UploadDate, 23)=CONVERT(varchar, getdate(), 23) order by DEALDATE desc;SET NOCOUNT OFF'
exec msdb..sp_send_dbmail @recipients = @recip,
@subject = @subject,
@body = @body,
@query = @query,
@attach_query_result_as_file = 1,
@query_attachment_filename = @att,
@query_result_separator = @sep,
@query_result_width = 400
END
ELSE
Begin
select @subject = 'No Duplicate Found'
exec msdb..sp_send_dbmail @recipients = @recip,
@subject = @subject,
@body = @body,
@query = @query,
@query_result_separator = @sep,
@query_result_width = 400
END
January 10, 2020 at 12:18 pm
You need to take the double quotes out of the first query - since the query itself is not enclosed in quotes, they only need to be single.
John
January 10, 2020 at 12:28 pm
Sorry, It is single quote only in first query but still getting same exception.
January 10, 2020 at 1:37 pm
So the code you posted isn't the code that's in your job step? Please will you post the (whole) code that's giving the error?
John
January 10, 2020 at 1:37 pm
No need for double single quotes. Try this instead.
AND Name IN ('VS DAY Cash', 'VS Non Cash')
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 10, 2020 at 2:38 pm
Hi Both,
the code is same as I posted.
However if I use the single quote in second query then also same issue.
again posting the same code which is giving error
declare @subject varchar(255)
, @query nvarchar(4000)
, @body varchar(255)
, @sep varchar(1)
, @att varchar(50)
, @recip varchar(255)
select @recip = 's.smith@xyz.com;'
select @body = ''
select @subject = 'Duplicate Data - ' + convert(varchar, getdate()-1, 106)
select @sep = char(9)
select @att = 'Duplicate Data' + convert(varchar,getdate()-1, 112) + '.csv'
declare @cnt int
Select @cnt= count(*)
from
(
select TicketDate, TicketNum,UploadDate,Amount, Name, DOB from Ticket_Num
group by TicketDate, TicketNum,Name,UploadDate,Amount,DOB
having count(*)>1 and TICKETNUM is not null
and Name in('VS Day Cash','VS Non Cash')
and CONVERT(varchar, UploadDate, 23)=CONVERT(varchar, getdate(), 23)
) newtable
Print @cnt
IF @cnt >1
Begin
select @query = 'SET NOCOUNT ON;select TicketDate, TicketNum,UploadDate,Amount, Name, DOB from Ticket_Num
group by TicketDate, TicketNum,Name,UploadDate,Amount,DOB
having count(*)>1 and TICKETNUM is not null
and Name in(''VS Day Cash'',''VS Non Cash'')
and CONVERT(varchar, UploadDate, 23)=CONVERT(varchar, getdate(), 23) order by DEALDATE desc;SET NOCOUNT OFF'
exec msdb..sp_send_dbmail @recipients = @recip,
@subject = @subject,
@body = @body,
@query = @query,
@attach_query_result_as_file = 1,
@query_attachment_filename = @att,
@query_result_separator = @sep,
@query_result_width = 400
END
ELSE
Begin
select @subject = 'No Duplicate Found'
exec msdb..sp_send_dbmail @recipients = @recip,
@subject = @subject,
@body = @body,
@query = @query,
@query_result_separator = @sep,
@query_result_width = 400
END
January 10, 2020 at 3:01 pm
The only thing I can think of, in that case, is that you have things in your HAVING clause that ought to be in a WHERE clause.
SELECT
TicketDate
,TicketNum
,ploadDate
,Amount
,Name
,DOB
FROM Ticket_Num
WHERE TicketNum IS NOT NULL
AND Name IN ('VS Day Cash','VS Non Cash')
AND CAST(UploadDate AS date) = CAST(GETDATE() AS date) -- date arithmetic is more efficient than character conversions
GROUP BY
TicketDate
,TicketNum
,ploadDate
,Amount
,Name
,DOB
HAVING COUNT(*) > 1;
John
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply