I try to run the following code in DTS, but i not work. What i want is
if do not have duplicate data, then the table will be dropped. While
if have duplicate data, the table will not be dropped but an email
notification will be sent. Why when there is no duplicate data, the
email still will be sent out?
if not exists(select catalog_code, count(catalog_code) from rd_awards
group
by catalog_code having
count(catalog_code) > 1)
begin
drop table [dbo].[Rewards_CatalogProducts_CS]
end
else
if exists(select catalog_code, count(catalog_code) from rd_awards
group
by catalog_code having
count(catalog_code) > 1)
use master
go
declare @.FROM NVARCHAR(4000),
@.FROM_NAME NVARCHAR(4000),
@.TO NVARCHAR(4000),
@.CC NVARCHAR(4000),
@.BCC NVARCHAR(4000),
@.priority NVARCHAR(10),
@.subject NVARCHAR(4000),
@.message NVARCHAR(4000),
@.type NVARCHAR(100),
@.attachments NVARCHAR(4000),
@.codepage INT,
@.rc INT
select @.FROM = N'sqlmail@.cyber-village.net',
@.FROM_NAME = N'ChangMian',
@.TO = N'tchangmian@.yahoo.com.sg',
@.CC = N'changmian@.cyber-village.net',
@.priority = N'High',
@.subject = N'Ei,can wor',
@.message = N'<HTML><H1>Hello SQL Server SMTP SQL
Mail</H1></HTML>',
@.type = N'text/html',
@.attachments = N'',
@.codepage = 0
exec @.rc = master.dbo.xp_smtp_sendmail
@.FROM = @.FROM,
@.TO = @.TO,
@.CC = @.CC,
@.priority = @.priority,
@.subject = @.subject,
@.message = @.message,
@.type = @.type,
@.attachments = @.attachments,
@.codepage = @.codepage,
@.server = N'mail.cyber-village.net'
select RC = @.rc
go
Your send mail script is in a different batch because of the 'GO' batch
terminator. Consider revising your script like the example below:
declare @.FROM NVARCHAR(4000),
@.FROM_NAME NVARCHAR(4000),
@.TO NVARCHAR(4000),
@.CC NVARCHAR(4000),
@.BCC NVARCHAR(4000),
@.priority NVARCHAR(10),
@.subject NVARCHAR(4000),
@.message NVARCHAR(4000),
@.type NVARCHAR(100),
@.attachments NVARCHAR(4000),
@.codepage INT,
@.rc INT
if not exists(select catalog_code, count(catalog_code) from rd_awards
group
by catalog_code having
count(catalog_code) > 1)
begin
drop table [dbo].[Rewards_CatalogProducts_CS]
end
else
begin
select @.FROM = N'sqlmail@.cyber-village.net',
@.FROM_NAME = N'ChangMian',
@.TO = N'tchangmian@.yahoo.com.sg',
@.CC = N'changmian@.cyber-village.net',
@.priority = N'High',
@.subject = N'Ei,can wor',
@.message = N'<HTML><H1>Hello SQL Server SMTP SQL
Mail</H1></HTML>',
@.type = N'text/html',
@.attachments = N'',
@.codepage = 0
exec @.rc = master.dbo.xp_smtp_sendmail
@.FROM = @.FROM,
@.TO = @.TO,
@.CC = @.CC,
@.priority = @.priority,
@.subject = @.subject,
@.message = @.message,
@.type = @.type,
@.attachments = @.attachments,
@.codepage = @.codepage,
@.server = N'mail.cyber-village.net'
select RC = @.rc
end
go
Hope this helps.
Dan Guzman
SQL Server MVP
"tchangmian" <tchangmian@.yahoo.com.sg> wrote in message
news:6447ee25.0410042022.52f52ac5@.posting.google.c om...
>I try to run the following code in DTS, but i not work. What i want is
> if do not have duplicate data, then the table will be dropped. While
> if have duplicate data, the table will not be dropped but an email
> notification will be sent. Why when there is no duplicate data, the
> email still will be sent out?
>
> if not exists(select catalog_code, count(catalog_code) from rd_awards
> group
> by catalog_code having
> count(catalog_code) > 1)
> begin
> drop table [dbo].[Rewards_CatalogProducts_CS]
> end
>
> else
> if exists(select catalog_code, count(catalog_code) from rd_awards
> group
> by catalog_code having
> count(catalog_code) > 1)
>
> use master
> go
> declare @.FROM NVARCHAR(4000),
> @.FROM_NAME NVARCHAR(4000),
> @.TO NVARCHAR(4000),
> @.CC NVARCHAR(4000),
> @.BCC NVARCHAR(4000),
> @.priority NVARCHAR(10),
> @.subject NVARCHAR(4000),
> @.message NVARCHAR(4000),
> @.type NVARCHAR(100),
> @.attachments NVARCHAR(4000),
> @.codepage INT,
> @.rc INT
> select @.FROM = N'sqlmail@.cyber-village.net',
> @.FROM_NAME = N'ChangMian',
> @.TO = N'tchangmian@.yahoo.com.sg',
> @.CC = N'changmian@.cyber-village.net',
> @.priority = N'High',
> @.subject = N'Ei,can wor',
> @.message = N'<HTML><H1>Hello SQL Server SMTP SQL
> Mail</H1></HTML>',
> @.type = N'text/html',
> @.attachments = N'',
> @.codepage = 0
> exec @.rc = master.dbo.xp_smtp_sendmail
> @.FROM = @.FROM,
> @.TO = @.TO,
> @.CC = @.CC,
> @.priority = @.priority,
> @.subject = @.subject,
> @.message = @.message,
> @.type = @.type,
> @.attachments = @.attachments,
> @.codepage = @.codepage,
> @.server = N'mail.cyber-village.net'
> select RC = @.rc
> go
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment