I want to check for duplicate data, if got duplicate data, then the
table will be dropped, if dun have, the table will not be dropped, but
the constraint and the indexed within the table will be dropped, and
an mail notification will be send. Below is my syntax uwing stored
procedure,when i run this stored procedure, it get an error message
:"Incorrect syntax near 'PK_Rewards_CatalogProducts_CS' ", Can anyone
tell me the correct way to drop the constraint and index?
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)
begin
ALTER TABLE [dbo].[Rewards_CatalogProducts_CS]
DROP CONSTRAINT [PK_Rewards_CatalogProducts_CS]
GO
ALTER TABLE [dbo].[Rewards_CatalogProducts_CS]
DROP INDEX [idx_categorycode]
GO
ALTER TABLE [dbo].[Rewards_CatalogProducts_CS]
DROP INDEX [idx_g_org_SupplierId]
use master
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'headache',
@.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
HI!
Not the Alter Statement is the problem. The problem is that The BEGIN has no
coresponding END in your IF Statement, because you are dividing your script
in several batches with the GO Statement.
IF BEGIN END has to be in one Batch and with GO you are starting a new Batch
which is the unit of Parsing and Compilation for SQL Server. GO is not a
SQL Statement. It tells Query Analyzer, osql,... to seperate the script into
units which are send separately to SQL Server (see BOL)
Just remove the GO statements, then it should work. They are not necessary
in your case.
Cheers,
Herbert
"tchangmian" <tchangmian@.yahoo.com.sg> schrieb im Newsbeitrag
news:6447ee25.0410070127.2321f3f1@.posting.google.c om...
> I want to check for duplicate data, if got duplicate data, then the
> table will be dropped, if dun have, the table will not be dropped, but
> the constraint and the indexed within the table will be dropped, and
> an mail notification will be send. Below is my syntax uwing stored
> procedure,when i run this stored procedure, it get an error message
> :"Incorrect syntax near 'PK_Rewards_CatalogProducts_CS' ", Can anyone
> tell me the correct way to drop the constraint and index?
> 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)
> begin
> ALTER TABLE [dbo].[Rewards_CatalogProducts_CS]
> DROP CONSTRAINT [PK_Rewards_CatalogProducts_CS]
> GO
> ALTER TABLE [dbo].[Rewards_CatalogProducts_CS]
> DROP INDEX [idx_categorycode]
> GO
> ALTER TABLE [dbo].[Rewards_CatalogProducts_CS]
> DROP INDEX [idx_g_org_SupplierId]
>
> use master
> 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'headache',
> @.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
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment