How to drop users programmatically through a sp

Hi,

I want to drop users programmatically, is this possible? I tried

create PROCEDURE [dbo].[sp_DelAllUnusedLogins] AS
BEGIN
SET NOCOUNT ON;
DECLARE @.UserName nvarchar(128)

DECLARE user_cursor CURSOR FOR
select name from sys.sysusers where status=12 and name
not in ('NT AUTHORITY\SYSTEM','dbo')

OPEN user_cursor

FETCH NEXT FROM user_cursor INTO @.UserName
WHILE @.@.FETCH_STATUS = 0
BEGIN

DROP USER @.UserName
FETCH NEXT FROM user_cursor INTO @.UserName

END

CLOSE user_cursor
DEALLOCATE user_cursor
END

But @.UserName isn't accepted..
Okay, I found
sp_dropuser
;) Which works fine

|||

Sp_dropuser is deprecated and will be removed in a future release. You can use the DROP USER statement within dynamic SQL to drop the user from a procedure. sp_dropuser does in fact do the same thing: it calls DROP USER.

Thanks
Laurentiu

|||

How do I use drop user in dynamic SQL?

I tried:
declare @.User varchar(100);
select @.User='SomeExistingUser';
drop user @.User;

This will produce the error:
Meldung 102, Ebene 15, Status 1, Zeile 3
Falsche Syntax in der N?he von '@.User'.

=>Invalid syntax near '@.User'

|||

YOu have embed the whole thing in an execution context like:

declare @.Statement varchar(100);
select @.Statement ='DROP USER + ' SomeExistingUser';
EXEC(@.Statement);

HTH, Jens Suessmeyer.

http://www.slqserver2005.de

|||

Here's the actual code that we execute within sp_dropuser:

set @.stmtU = 'drop user ' + quotename(@.name_in_db, ']')

-- drop the owner
exec (@.stmtU)

You need to use quotename to not allow SQL Injection.

Thanks
Laurentiu

How to drop user names after copying database?

I copied a database from a SQL 2000 server to a SQL 2005 server. In the
node for that database, and in the security > users node, it shows the users
from the 2000 box. Of course, those users don't exist on the 2005 box.
The GUI (SQL Server Management Studio) won't let me right click and delete.
When I try that, it says an error occurred. I thought 2005 would be smarter
than 2000. Microsoft has done a terrible job with the UI upgrade after 5
years. I know the answer is in running some lines in query analyzer to
remove those false user names from the db, but I can't remember what they
are, and I don't know if it is different for sql 2005 that it was under
2000.Run this in the said database, then execute the output it generates. That
will clean up the users for you.
set quoted_identifier off
select "exec sp_dropuser '" + name + "'" from sysusers where uid between 3
and 16383
AndyP,
Sr. Database Administrator,
MCDBA 2003
"HK" wrote:

> I copied a database from a SQL 2000 server to a SQL 2005 server. In the
> node for that database, and in the security > users node, it shows the use
rs
> from the 2000 box. Of course, those users don't exist on the 2005 box.
> The GUI (SQL Server Management Studio) won't let me right click and delete
.
> When I try that, it says an error occurred. I thought 2005 would be smart
er
> than 2000. Microsoft has done a terrible job with the UI upgrade after 5
> years. I know the answer is in running some lines in query analyzer to
> remove those false user names from the db, but I can't remember what they
> are, and I don't know if it is different for sql 2005 that it was under
> 2000.
>
>|||Exactly what I was looking for. Thanks!
"AndyP" <AndyP@.discussions.microsoft.com> wrote in message
news:CD6F6C73-B481-4D5F-B6A8-5C2C78985F9D@.microsoft.com...[vbcol=seagreen]
> Run this in the said database, then execute the output it generates. That
> will clean up the users for you.
>
> set quoted_identifier off
> select "exec sp_dropuser '" + name + "'" from sysusers where uid between 3
> and 16383
>
> --
> AndyP,
> Sr. Database Administrator,
> MCDBA 2003
>
> "HK" wrote:
>
users[vbcol=seagreen]
delete.[vbcol=seagreen]
smarter[vbcol=seagreen]
5[vbcol=seagreen]
they[vbcol=seagreen]

How to drop user names after copying database?

I copied a database from a SQL 2000 server to a SQL 2005 server. In the
node for that database, and in the security > users node, it shows the users
from the 2000 box. Of course, those users don't exist on the 2005 box.
The GUI (SQL Server Management Studio) won't let me right click and delete.
When I try that, it says an error occurred. I thought 2005 would be smarter
than 2000. Microsoft has done a terrible job with the UI upgrade after 5
years. I know the answer is in running some lines in query analyzer to
remove those false user names from the db, but I can't remember what they
are, and I don't know if it is different for sql 2005 that it was under
2000.
Run this in the said database, then execute the output it generates. That
will clean up the users for you.
set quoted_identifier off
select "exec sp_dropuser '" + name + "'" from sysusers where uid between 3
and 16383
AndyP,
Sr. Database Administrator,
MCDBA 2003
"HK" wrote:

> I copied a database from a SQL 2000 server to a SQL 2005 server. In the
> node for that database, and in the security > users node, it shows the users
> from the 2000 box. Of course, those users don't exist on the 2005 box.
> The GUI (SQL Server Management Studio) won't let me right click and delete.
> When I try that, it says an error occurred. I thought 2005 would be smarter
> than 2000. Microsoft has done a terrible job with the UI upgrade after 5
> years. I know the answer is in running some lines in query analyzer to
> remove those false user names from the db, but I can't remember what they
> are, and I don't know if it is different for sql 2005 that it was under
> 2000.
>
>
|||Exactly what I was looking for. Thanks!
"AndyP" <AndyP@.discussions.microsoft.com> wrote in message
news:CD6F6C73-B481-4D5F-B6A8-5C2C78985F9D@.microsoft.com...[vbcol=seagreen]
> Run this in the said database, then execute the output it generates. That
> will clean up the users for you.
>
> set quoted_identifier off
> select "exec sp_dropuser '" + name + "'" from sysusers where uid between 3
> and 16383
>
> --
> AndyP,
> Sr. Database Administrator,
> MCDBA 2003
>
> "HK" wrote:
users[vbcol=seagreen]
delete.[vbcol=seagreen]
smarter[vbcol=seagreen]
5[vbcol=seagreen]
they[vbcol=seagreen]

How to drop user names after copying database?

I copied a database from a SQL 2000 server to a SQL 2005 server. In the
node for that database, and in the security > users node, it shows the users
from the 2000 box. Of course, those users don't exist on the 2005 box.
The GUI (SQL Server Management Studio) won't let me right click and delete.
When I try that, it says an error occurred. I thought 2005 would be smarter
than 2000. Microsoft has done a terrible job with the UI upgrade after 5
years. I know the answer is in running some lines in query analyzer to
remove those false user names from the db, but I can't remember what they
are, and I don't know if it is different for sql 2005 that it was under
2000.Run this in the said database, then execute the output it generates. That
will clean up the users for you.
set quoted_identifier off
select "exec sp_dropuser '" + name + "'" from sysusers where uid between 3
and 16383
AndyP,
Sr. Database Administrator,
MCDBA 2003
"HK" wrote:
> I copied a database from a SQL 2000 server to a SQL 2005 server. In the
> node for that database, and in the security > users node, it shows the users
> from the 2000 box. Of course, those users don't exist on the 2005 box.
> The GUI (SQL Server Management Studio) won't let me right click and delete.
> When I try that, it says an error occurred. I thought 2005 would be smarter
> than 2000. Microsoft has done a terrible job with the UI upgrade after 5
> years. I know the answer is in running some lines in query analyzer to
> remove those false user names from the db, but I can't remember what they
> are, and I don't know if it is different for sql 2005 that it was under
> 2000.
>
>|||Exactly what I was looking for. Thanks!
"AndyP" <AndyP@.discussions.microsoft.com> wrote in message
news:CD6F6C73-B481-4D5F-B6A8-5C2C78985F9D@.microsoft.com...
> Run this in the said database, then execute the output it generates. That
> will clean up the users for you.
>
> set quoted_identifier off
> select "exec sp_dropuser '" + name + "'" from sysusers where uid between 3
> and 16383
>
> --
> AndyP,
> Sr. Database Administrator,
> MCDBA 2003
>
> "HK" wrote:
> > I copied a database from a SQL 2000 server to a SQL 2005 server. In the
> > node for that database, and in the security > users node, it shows the
users
> > from the 2000 box. Of course, those users don't exist on the 2005 box.
> > The GUI (SQL Server Management Studio) won't let me right click and
delete.
> > When I try that, it says an error occurred. I thought 2005 would be
smarter
> > than 2000. Microsoft has done a terrible job with the UI upgrade after
5
> > years. I know the answer is in running some lines in query analyzer to
> > remove those false user names from the db, but I can't remember what
they
> > are, and I don't know if it is different for sql 2005 that it was under
> > 2000.
> >
> >
> >

how to drop the identity property ?

We have a table that has an identity property associated to a column ? How
can we drop it ?
Disclaimer: Get a good backup before you start mucking around with the
schema.
Probably have to create a new INT column, UPDATE it:
UPDATE Table1
SET NewCol = OldIdentityCol
Then use ALTER TABLE to drop OldIdentityCol. You'll also need to Drop and
Rebuild any constraints, etc.
If you use EM to change the Identity property of the table, it does
something similar - except that it creates the entire table again and should
preserve any constraints for you.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OcXYlz3UFHA.544@.TK2MSFTNGP15.phx.gbl...
> We have a table that has an identity property associated to a column ? How
> can we drop it ?
>
|||Hi,
There is no direct command in SQL Server to drop the Identity property in a
table. You may need to drop and recreate the table with no identity.
Thanks
Hari
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OcXYlz3UFHA.544@.TK2MSFTNGP15.phx.gbl...
> We have a table that has an identity property associated to a column ? How
> can we drop it ?
>

how to drop the identity property ?

We have a table that has an identity property associated to a column ? How
can we drop it ?Disclaimer: Get a good backup before you start mucking around with the
schema.
Probably have to create a new INT column, UPDATE it:
UPDATE Table1
SET NewCol = OldIdentityCol
Then use ALTER TABLE to drop OldIdentityCol. You'll also need to Drop and
Rebuild any constraints, etc.
If you use EM to change the Identity property of the table, it does
something similar - except that it creates the entire table again and should
preserve any constraints for you.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OcXYlz3UFHA.544@.TK2MSFTNGP15.phx.gbl...
> We have a table that has an identity property associated to a column ? How
> can we drop it ?
>|||Hi,
There is no direct command in SQL Server to drop the Identity property in a
table. You may need to drop and recreate the table with no identity.
Thanks
Hari
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OcXYlz3UFHA.544@.TK2MSFTNGP15.phx.gbl...
> We have a table that has an identity property associated to a column ? How
> can we drop it ?
>

how to drop the identity property ?

We have a table that has an identity property associated to a column ? How
can we drop it ?Disclaimer: Get a good backup before you start mucking around with the
schema.
Probably have to create a new INT column, UPDATE it:
UPDATE Table1
SET NewCol = OldIdentityCol
Then use ALTER TABLE to drop OldIdentityCol. You'll also need to Drop and
Rebuild any constraints, etc.
If you use EM to change the Identity property of the table, it does
something similar - except that it creates the entire table again and should
preserve any constraints for you.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OcXYlz3UFHA.544@.TK2MSFTNGP15.phx.gbl...
> We have a table that has an identity property associated to a column ? How
> can we drop it ?
>|||Hi,
There is no direct command in SQL Server to drop the Identity property in a
table. You may need to drop and recreate the table with no identity.
Thanks
Hari
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OcXYlz3UFHA.544@.TK2MSFTNGP15.phx.gbl...
> We have a table that has an identity property associated to a column ? How
> can we drop it ?
>

How to drop sql server object?

Hi,
I am getting errors when I try to drop a column in sql server table.
Server: Msg 5074, Level 16, State 1, Line 1
The object 'DF__hdp_langu__langn__29572725' is dependent on column
'langname_ge'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE DROP COLUMN langname_ge failed because one or more objects
access this column.
I thought the object to be a constraint and tried dropping it first by
"alter table drop constraint" command.
But query analyser says that the concerned object is not a constraint. How
do I drop this object?
Thanks and Regards,
Celiacelia (celia.rexselin@.gmail.com) writes:
> I am getting errors when I try to drop a column in sql server table.
> Server: Msg 5074, Level 16, State 1, Line 1
> The object 'DF__hdp_langu__langn__29572725' is dependent on column
> 'langname_ge'.
> Server: Msg 4922, Level 16, State 1, Line 1
> ALTER TABLE DROP COLUMN langname_ge failed because one or more objects
> access this column.
> I thought the object to be a constraint and tried dropping it first by
> "alter table drop constraint" command.
> But query analyser says that the concerned object is not a constraint. How
> do I drop this object?
In this particular case you would do:
ALTER TABLE tbl DROP CONSTRAINT DF__hdp_langu__langn__29572725
A tip is that when you create tables is to always name your constraints
explicitly, that makes it easier to drop them. For instance:
CREATE TABLE a (a int NOT NULL,
b int NOT NULL CONSTRAINT df DEFAULT 12)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||You could search for dependents of the object you are trying to drop. For
example, if the object you are trying to drop is called 'ObjectName', then
try:
Select
ID ,
Object_Name( ID ) As Object ,
DepID ,
Object_Name( DepID ) As Dependent
From
SysDepends
Where
Id = Object_ID( 'ObjectName' )
"celia" <celia.rexselin@.gmail.com> wrote in message
news:Ooa$L1FFGHA.644@.TK2MSFTNGP09.phx.gbl...
Hi,
I am getting errors when I try to drop a column in sql server table.
Server: Msg 5074, Level 16, State 1, Line 1
The object 'DF__hdp_langu__langn__29572725' is dependent on column
'langname_ge'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE DROP COLUMN langname_ge failed because one or more objects
access this column.
I thought the object to be a constraint and tried dropping it first by
"alter table drop constraint" command.
But query analyser says that the concerned object is not a constraint. How
do I drop this object?
Thanks and Regards,
Celia|||Like everyone else has said, you have to drop the defaults and check
constraints first.
I have made a suggestion a while back on the feedback center. Please vote
for it :)
http://lab.msdn.microsoft.com/produ...14-4f026070abac
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"celia" <celia.rexselin@.gmail.com> wrote in message
news:Ooa$L1FFGHA.644@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I am getting errors when I try to drop a column in sql server table.
> Server: Msg 5074, Level 16, State 1, Line 1
> The object 'DF__hdp_langu__langn__29572725' is dependent on column
> 'langname_ge'.
> Server: Msg 4922, Level 16, State 1, Line 1
> ALTER TABLE DROP COLUMN langname_ge failed because one or more objects
> access this column.
> I thought the object to be a constraint and tried dropping it first by
> "alter table drop constraint" command.
> But query analyser says that the concerned object is not a constraint. How
> do I drop this object?
> Thanks and Regards,
> Celia
>
>

How to drop Primary key by specifying column only?

I checked column from xsd file which created from DataBase.

And before installing my application I will check any column in destination base whether have complete column or not.

And if there is any column which is not wanted column(not same as xsd structure)

I will delete it by creating sql command as follow "ALTER TABLE tableName DROP COLUMN column1, column2, ....."

and Execute it by program initialization.

So

I need delete it by run-time

However some column may be Primary Key with any reason

That's why I can't delete them by simple command

I expect to delete them by specific column instead of specific constraint name which is not sure name.

Please advise me...

I don't think you can do it by just specifying a column but you can do it this way:

To get the name of the primary key:

SELECT constraint_name
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE (TABLE_NAME = 'TestTable') AND (CONSTRAINT_TYPE = 'PRIMARY KEY')

To drop the primary key:

ALTER TABLE TestTable
DROP CONSTRAINT PK_TestTable

|||

Thanks a lot

:D

How to drop primary in script

Hi, does someone know how to drop primary key using TSQL? We need to put
everything into TSQL script. Somehow, I could not find anything in BOL.
please help.
Thanks, Anna
Look at Alter Table..drop constraint.. in BOL
"Anna Linne" <Anna Linne@.discussions.microsoft.com> wrote in message
news:C330A43E-CE07-428B-98FD-A56EB60B9634@.microsoft.com...
> Hi, does someone know how to drop primary key using TSQL? We need to put
> everything into TSQL script. Somehow, I could not find anything in BOL.
> please help.
> Thanks, Anna
|||HI
ALTER TABLE dbo.Table1
DROP CONSTRAINT PK_Table1
Andras Jakus MCDBA
"Anna Linne" wrote:

> Hi, does someone know how to drop primary key using TSQL? We need to put
> everything into TSQL script. Somehow, I could not find anything in BOL.
> please help.
> Thanks, Anna
|||alter table table1 add constraint pk primary key(column1)
alter table table1 drop constraint pk
"Anna Linne" wrote:

> Hi, does someone know how to drop primary key using TSQL? We need to put
> everything into TSQL script. Somehow, I could not find anything in BOL.
> please help.
> Thanks, Anna

How to drop primary in script

Hi, does someone know how to drop primary key using TSQL? We need to put
everything into TSQL script. Somehow, I could not find anything in BOL.
please help.
Thanks, AnnaLook at Alter Table..drop constraint.. in BOL
"Anna Linne" <Anna Linne@.discussions.microsoft.com> wrote in message
news:C330A43E-CE07-428B-98FD-A56EB60B9634@.microsoft.com...
> Hi, does someone know how to drop primary key using TSQL? We need to put
> everything into TSQL script. Somehow, I could not find anything in BOL.
> please help.
> Thanks, Anna|||HI
ALTER TABLE dbo.Table1
DROP CONSTRAINT PK_Table1
Andras Jakus MCDBA
"Anna Linne" wrote:
> Hi, does someone know how to drop primary key using TSQL? We need to put
> everything into TSQL script. Somehow, I could not find anything in BOL.
> please help.
> Thanks, Anna|||alter table table1 add constraint pk primary key(column1)
alter table table1 drop constraint pk
"Anna Linne" wrote:
> Hi, does someone know how to drop primary key using TSQL? We need to put
> everything into TSQL script. Somehow, I could not find anything in BOL.
> please help.
> Thanks, Anna

how to drop orphaned “FULLTEXT” datafile

Guys,
Does somebody know how to drop orphaned “FULLTEXT” datafile from database?
Recreating FTS with same name and/or with the same location didn’t help (as
expected)
Database was detached from 2K and attached to 2K5
select file_id, name, path, fulltext_catalog_id from sys.fulltext_catalogs
65537CN3_Catalog_3_stage_FTCY:\FTS\CN3\CN3_Catalog_3_stage_FTC7
select file_id, name, file_guid, type_desc, physical_name, state_desc
from sys.database_files where type_desc = 'FULLTEXT'
65537sysft_CN3_Catalog_3_stage_FTCEE7215FD-C2D3-45C8-8136-5B77D2210B73FULLTEXTY:\FTS\CN3\CN3_Catalog_3_stage_FTCONLINE
65538sysft_CN3_Catalog_3_stage_FTCD60287F6-A0CE-4854-83CA-74F4F23C78A2FULLTEXTY:\FTI\CN3_Catalog_3_stage_FTCONLINE
select file_id, name, file_guid, type_desc, physical_name AS
CurrentLocation, state_desc
from sys.master_files
where type_desc = 'FULLTEXT'
and database_id = 8;
65537sysft_CN3_Catalog_3_stage_FTCEE7215FD-C2D3-45C8-8136-5B77D2210B73FULLTEXTY:\FTS\CN3\CN3_Catalog_3_stage_FTCONLINE
In this case even if I drop FTC CN3_Catalog_3_stage_FTC
All records with file_id = 65537 will disappear, but 65538 will not.
And database backup will fail. :-)
If I execute
ALTER DATABASE my_db REMOVE FILE CN3_Catalog_2_stage_FTC;
Msg 5020, Level 16, State 1, Line 1
The primary data or log file cannot be removed from a database
http://support.microsoft.com/kb/923355/en-us
doesn’t help ether because FTS catalog not even listed in
sys.fulltext_catalogs
In SQL 2K in such cases running
sp_fulltext_service 'clean_up'
always helped, but not in SQL 2K5.
Let me know if you have an idea how to fix it,
Thank you,
OK

The answer is do not run OFFLINE on any file or filegroup your stuck.

Microsoft is is stuck and does not understand to bring it online.

What we need to do is first dremove the filegroup

ALTER DATABASE YourDatabaseName remove FILEGROUp [filegroup];

Then run the remove command

ALTER DATABASE YourDatabaseName remove FILE [logical_filename];



You will still see the filegroup offline status.

Now do a detach of the database and try to reattach the using the gui and then click on the script and then you will have the  files that are being attach to create the database.

Remove the file you do not need and you will get a database that does not have an offline file.

How to drop offline file/filegroups after piecemeal restore

I have a very large database (5 TB) with many file groups. Basically each
file group consists of one large table (70 - 80 GB). The backup structure is
set up to backup each filegroup after it's table is loaded (the data is
static) and then perform a differetial backup. The server crashed the other
day and I began the restore process. Unfortunately due to space limitations,
all of the file group backup files were not available. So I did a piecemeal
restore with the primary FG backup file and then all of the other available
FG backup files. The last differential file was restored with RECOVERY and
the database is now online. However there are about 30 file groups offline
now. How do I drop/remove these file/filegroups.
I can not remove the files with the alter database statement, this gets the
error:
Cannot add, remove, or modify a file in filegroup 'FG0001' because the
filegroup is offline
I am running SQL Server 2005 Enterprise edition (sp2)
Thanx in advance for any and all help,
Jeff Carrington
DBA
Comscore
According to Books Online, you should be able to use ALTER DATABASE ... REMOVE FILE to get rid of a
"defunkt" file and filegroup:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/055f9c6a-5c18-4942-98e7-ec918f0ff975.htm
Did you do the initial restore using the PARTIAL option?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jeff Carrington" <JeffCarrington@.discussions.microsoft.com> wrote in message
news:A3407889-76CA-45E5-89BE-19765A67639B@.microsoft.com...
>I have a very large database (5 TB) with many file groups. Basically each
> file group consists of one large table (70 - 80 GB). The backup structure is
> set up to backup each filegroup after it's table is loaded (the data is
> static) and then perform a differetial backup. The server crashed the other
> day and I began the restore process. Unfortunately due to space limitations,
> all of the file group backup files were not available. So I did a piecemeal
> restore with the primary FG backup file and then all of the other available
> FG backup files. The last differential file was restored with RECOVERY and
> the database is now online. However there are about 30 file groups offline
> now. How do I drop/remove these file/filegroups.
> I can not remove the files with the alter database statement, this gets the
> error:
> Cannot add, remove, or modify a file in filegroup 'FG0001' because the
> filegroup is offline
> I am running SQL Server 2005 Enterprise edition (sp2)
> Thanx in advance for any and all help,
> --
> Jeff Carrington
> DBA
> Comscore
|||Yes I did use the PARTIAL option when executing the first restore of the
primary file group. I read the same section in BOL, but my attempt to remove
the file gets the error stating the file group is offline.
Thanx for the help...
Jeff Carrington
"Tibor Karaszi" wrote:

> According to Books Online, you should be able to use ALTER DATABASE ... REMOVE FILE to get rid of a
> "defunkt" file and filegroup:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/055f9c6a-5c18-4942-98e7-ec918f0ff975.htm
> Did you do the initial restore using the PARTIAL option?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Jeff Carrington" <JeffCarrington@.discussions.microsoft.com> wrote in message
> news:A3407889-76CA-45E5-89BE-19765A67639B@.microsoft.com...
>
>

How to drop offline file/filegroups after piecemeal restore

I have a very large database (5 TB) with many file groups. Basically each
file group consists of one large table (70 - 80 GB). The backup structure is
set up to backup each filegroup after it's table is loaded (the data is
static) and then perform a differetial backup. The server crashed the other
day and I began the restore process. Unfortunately due to space limitations,
all of the file group backup files were not available. So I did a piecemeal
restore with the primary FG backup file and then all of the other available
FG backup files. The last differential file was restored with RECOVERY and
the database is now online. However there are about 30 file groups offline
now. How do I drop/remove these file/filegroups.
I can not remove the files with the alter database statement, this gets the
error:
Cannot add, remove, or modify a file in filegroup 'FG0001' because the
filegroup is offline
I am running SQL Server 2005 Enterprise edition (sp2)
Thanx in advance for any and all help,
--
Jeff Carrington
DBA
ComscoreAccording to Books Online, you should be able to use ALTER DATABASE ... REMOVE FILE to get rid of a
"defunkt" file and filegroup:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/055f9c6a-5c18-4942-98e7-ec918f0ff975.htm
Did you do the initial restore using the PARTIAL option?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jeff Carrington" <JeffCarrington@.discussions.microsoft.com> wrote in message
news:A3407889-76CA-45E5-89BE-19765A67639B@.microsoft.com...
>I have a very large database (5 TB) with many file groups. Basically each
> file group consists of one large table (70 - 80 GB). The backup structure is
> set up to backup each filegroup after it's table is loaded (the data is
> static) and then perform a differetial backup. The server crashed the other
> day and I began the restore process. Unfortunately due to space limitations,
> all of the file group backup files were not available. So I did a piecemeal
> restore with the primary FG backup file and then all of the other available
> FG backup files. The last differential file was restored with RECOVERY and
> the database is now online. However there are about 30 file groups offline
> now. How do I drop/remove these file/filegroups.
> I can not remove the files with the alter database statement, this gets the
> error:
> Cannot add, remove, or modify a file in filegroup 'FG0001' because the
> filegroup is offline
> I am running SQL Server 2005 Enterprise edition (sp2)
> Thanx in advance for any and all help,
> --
> Jeff Carrington
> DBA
> Comscore|||Yes I did use the PARTIAL option when executing the first restore of the
primary file group. I read the same section in BOL, but my attempt to remove
the file gets the error stating the file group is offline.
Thanx for the help...
--
Jeff Carrington
"Tibor Karaszi" wrote:
> According to Books Online, you should be able to use ALTER DATABASE ... REMOVE FILE to get rid of a
> "defunkt" file and filegroup:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/055f9c6a-5c18-4942-98e7-ec918f0ff975.htm
> Did you do the initial restore using the PARTIAL option?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Jeff Carrington" <JeffCarrington@.discussions.microsoft.com> wrote in message
> news:A3407889-76CA-45E5-89BE-19765A67639B@.microsoft.com...
> >I have a very large database (5 TB) with many file groups. Basically each
> > file group consists of one large table (70 - 80 GB). The backup structure is
> > set up to backup each filegroup after it's table is loaded (the data is
> > static) and then perform a differetial backup. The server crashed the other
> > day and I began the restore process. Unfortunately due to space limitations,
> > all of the file group backup files were not available. So I did a piecemeal
> > restore with the primary FG backup file and then all of the other available
> > FG backup files. The last differential file was restored with RECOVERY and
> > the database is now online. However there are about 30 file groups offline
> > now. How do I drop/remove these file/filegroups.
> >
> > I can not remove the files with the alter database statement, this gets the
> > error:
> > Cannot add, remove, or modify a file in filegroup 'FG0001' because the
> > filegroup is offline
> >
> > I am running SQL Server 2005 Enterprise edition (sp2)
> >
> > Thanx in advance for any and all help,
> > --
> > Jeff Carrington
> > DBA
> > Comscore
>
>

How to drop merge replication system tables

Hi, we have a database wich used merge replication.
We disabled merge on this db, but '%onflict%' system tables persists,
leading to error when trying to drop them.
How can I drop those tables?
TIA,
Roberto Souza.
You could use sp_subscription_cleanup
There's a fairly good list of sp's used in replication at this site:
http://doc.ddart.net/mssql/sql70/sp_00.htm
"Roberto Souza" wrote:

> Hi, we have a database wich used merge replication.
> We disabled merge on this db, but '%onflict%' system tables persists,
> leading to error when trying to drop them.
> How can I drop those tables?
> TIA,
> Roberto Souza.
>
>
|||AFAIR this won't remove them. You should be able to use
drop table and the tablename from Query Analyser though.
Rgds,
Paul Ibison

How to drop login

Hi all,
when i execute the sp, EXEC master..sp_droplogin 'Martin'
it returns error:
Login 'Martin' is aliased or mapped to a user in one or more database(s).
Drop the user or alias before dropping the login.
I know that the user 'Martin' has been granted to different DB. If I have to
drop the login, the login should be revoked the DB access before issue the
sp_droplogin.
So, is there a command than can drop the login directly, i mean no matter
there are any DB associated with the login?
Just similar to the Enterprise manager, under Security -> Logins ->
highlight the login and right click "delete"
It will remove all associated database with the login and then drop the
login.
Thanks in advance!
Martin
Behind the scenes, EM is dropping the user from the databases to which (s)he
had been granted access, and then it drops the login. To script it, you
would have to loop through all DB's for that user and do the drops, followed
by sp_droplogin.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Atenza" <Atenza@.mail.hongkong.com> wrote in message
news:uRl$boTyHHA.3564@.TK2MSFTNGP04.phx.gbl...
Hi all,
when i execute the sp, EXEC master..sp_droplogin 'Martin'
it returns error:
Login 'Martin' is aliased or mapped to a user in one or more database(s).
Drop the user or alias before dropping the login.
I know that the user 'Martin' has been granted to different DB. If I have to
drop the login, the login should be revoked the DB access before issue the
sp_droplogin.
So, is there a command than can drop the login directly, i mean no matter
there are any DB associated with the login?
Just similar to the Enterprise manager, under Security -> Logins ->
highlight the login and right click "delete"
It will remove all associated database with the login and then drop the
login.
Thanks in advance!
Martin
|||Hi
You can run SQL Server Profiler to see what is going on behind the scenes
when you delete the LOGIN via EM
"Atenza" <Atenza@.mail.hongkong.com> wrote in message
news:uRl$boTyHHA.3564@.TK2MSFTNGP04.phx.gbl...
> Hi all,
> when i execute the sp, EXEC master..sp_droplogin 'Martin'
> it returns error:
> Login 'Martin' is aliased or mapped to a user in one or more database(s).
> Drop the user or alias before dropping the login.
> I know that the user 'Martin' has been granted to different DB. If I have
> to drop the login, the login should be revoked the DB access before issue
> the sp_droplogin.
> So, is there a command than can drop the login directly, i mean no matter
> there are any DB associated with the login?
> Just similar to the Enterprise manager, under Security -> Logins ->
> highlight the login and right click "delete"
> It will remove all associated database with the login and then drop the
> login.
> Thanks in advance!
> Martin
>
>
|||oh, no shortcut...bad to know the fact... :p
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:um7DtsTyHHA.4276@.TK2MSFTNGP05.phx.gbl...
> Behind the scenes, EM is dropping the user from the databases to which
> (s)he
> had been granted access, and then it drops the login. To script it, you
> would have to loop through all DB's for that user and do the drops,
> followed
> by sp_droplogin.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Atenza" <Atenza@.mail.hongkong.com> wrote in message
> news:uRl$boTyHHA.3564@.TK2MSFTNGP04.phx.gbl...
> Hi all,
> when i execute the sp, EXEC master..sp_droplogin 'Martin'
> it returns error:
> Login 'Martin' is aliased or mapped to a user in one or more database(s).
> Drop the user or alias before dropping the login.
> I know that the user 'Martin' has been granted to different DB. If I have
> to
> drop the login, the login should be revoked the DB access before issue the
> sp_droplogin.
> So, is there a command than can drop the login directly, i mean no matter
> there are any DB associated with the login?
> Just similar to the Enterprise manager, under Security -> Logins ->
> highlight the login and right click "delete"
> It will remove all associated database with the login and then drop the
> login.
> Thanks in advance!
> Martin
>
>
|||woo, this absolutely new to me.....sounds great...
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ufvFgxTyHHA.4824@.TK2MSFTNGP02.phx.gbl...
> Hi
> You can run SQL Server Profiler to see what is going on behind the scenes
> when you delete the LOGIN via EM
>
> "Atenza" <Atenza@.mail.hongkong.com> wrote in message
> news:uRl$boTyHHA.3564@.TK2MSFTNGP04.phx.gbl...
>

How to drop login

Hi all,
when i execute the sp, EXEC master..sp_droplogin 'Martin'
it returns error:
Login 'Martin' is aliased or mapped to a user in one or more database(s).
Drop the user or alias before dropping the login.
I know that the user 'Martin' has been granted to different DB. If I have to
drop the login, the login should be revoked the DB access before issue the
sp_droplogin.
So, is there a command than can drop the login directly, i mean no matter
there are any DB associated with the login?
Just similar to the Enterprise manager, under Security -> Logins ->
highlight the login and right click "delete"
It will remove all associated database with the login and then drop the
login.
Thanks in advance!
MartinBehind the scenes, EM is dropping the user from the databases to which (s)he
had been granted access, and then it drops the login. To script it, you
would have to loop through all DB's for that user and do the drops, followed
by sp_droplogin.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Atenza" <Atenza@.mail.hongkong.com> wrote in message
news:uRl$boTyHHA.3564@.TK2MSFTNGP04.phx.gbl...
Hi all,
when i execute the sp, EXEC master..sp_droplogin 'Martin'
it returns error:
Login 'Martin' is aliased or mapped to a user in one or more database(s).
Drop the user or alias before dropping the login.
I know that the user 'Martin' has been granted to different DB. If I have to
drop the login, the login should be revoked the DB access before issue the
sp_droplogin.
So, is there a command than can drop the login directly, i mean no matter
there are any DB associated with the login?
Just similar to the Enterprise manager, under Security -> Logins ->
highlight the login and right click "delete"
It will remove all associated database with the login and then drop the
login.
Thanks in advance!
Martin|||Hi
You can run SQL Server Profiler to see what is going on behind the scenes
when you delete the LOGIN via EM
"Atenza" <Atenza@.mail.hongkong.com> wrote in message
news:uRl$boTyHHA.3564@.TK2MSFTNGP04.phx.gbl...
> Hi all,
> when i execute the sp, EXEC master..sp_droplogin 'Martin'
> it returns error:
> Login 'Martin' is aliased or mapped to a user in one or more database(s).
> Drop the user or alias before dropping the login.
> I know that the user 'Martin' has been granted to different DB. If I have
> to drop the login, the login should be revoked the DB access before issue
> the sp_droplogin.
> So, is there a command than can drop the login directly, i mean no matter
> there are any DB associated with the login?
> Just similar to the Enterprise manager, under Security -> Logins ->
> highlight the login and right click "delete"
> It will remove all associated database with the login and then drop the
> login.
> Thanks in advance!
> Martin
>
>|||oh, no shortcut...bad to know the fact... :p
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:um7DtsTyHHA.4276@.TK2MSFTNGP05.phx.gbl...
> Behind the scenes, EM is dropping the user from the databases to which
> (s)he
> had been granted access, and then it drops the login. To script it, you
> would have to loop through all DB's for that user and do the drops,
> followed
> by sp_droplogin.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Atenza" <Atenza@.mail.hongkong.com> wrote in message
> news:uRl$boTyHHA.3564@.TK2MSFTNGP04.phx.gbl...
> Hi all,
> when i execute the sp, EXEC master..sp_droplogin 'Martin'
> it returns error:
> Login 'Martin' is aliased or mapped to a user in one or more database(s).
> Drop the user or alias before dropping the login.
> I know that the user 'Martin' has been granted to different DB. If I have
> to
> drop the login, the login should be revoked the DB access before issue the
> sp_droplogin.
> So, is there a command than can drop the login directly, i mean no matter
> there are any DB associated with the login?
> Just similar to the Enterprise manager, under Security -> Logins ->
> highlight the login and right click "delete"
> It will remove all associated database with the login and then drop the
> login.
> Thanks in advance!
> Martin
>
>|||woo, this absolutely new to me.....sounds great...
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ufvFgxTyHHA.4824@.TK2MSFTNGP02.phx.gbl...
> Hi
> You can run SQL Server Profiler to see what is going on behind the scenes
> when you delete the LOGIN via EM
>
> "Atenza" <Atenza@.mail.hongkong.com> wrote in message
> news:uRl$boTyHHA.3564@.TK2MSFTNGP04.phx.gbl...
>

How to drop login

Hi all,
when i execute the sp, EXEC master..sp_droplogin 'Martin'
it returns error:
Login 'Martin' is aliased or mapped to a user in one or more database(s).
Drop the user or alias before dropping the login.
I know that the user 'Martin' has been granted to different DB. If I have to
drop the login, the login should be revoked the DB access before issue the
sp_droplogin.
So, is there a command than can drop the login directly, i mean no matter
there are any DB associated with the login?
Just similar to the Enterprise manager, under Security -> Logins ->
highlight the login and right click "delete"
It will remove all associated database with the login and then drop the
login.
Thanks in advance!
MartinBehind the scenes, EM is dropping the user from the databases to which (s)he
had been granted access, and then it drops the login. To script it, you
would have to loop through all DB's for that user and do the drops, followed
by sp_droplogin.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Atenza" <Atenza@.mail.hongkong.com> wrote in message
news:uRl$boTyHHA.3564@.TK2MSFTNGP04.phx.gbl...
Hi all,
when i execute the sp, EXEC master..sp_droplogin 'Martin'
it returns error:
Login 'Martin' is aliased or mapped to a user in one or more database(s).
Drop the user or alias before dropping the login.
I know that the user 'Martin' has been granted to different DB. If I have to
drop the login, the login should be revoked the DB access before issue the
sp_droplogin.
So, is there a command than can drop the login directly, i mean no matter
there are any DB associated with the login?
Just similar to the Enterprise manager, under Security -> Logins ->
highlight the login and right click "delete"
It will remove all associated database with the login and then drop the
login.
Thanks in advance!
Martin|||Hi
You can run SQL Server Profiler to see what is going on behind the scenes
when you delete the LOGIN via EM
"Atenza" <Atenza@.mail.hongkong.com> wrote in message
news:uRl$boTyHHA.3564@.TK2MSFTNGP04.phx.gbl...
> Hi all,
> when i execute the sp, EXEC master..sp_droplogin 'Martin'
> it returns error:
> Login 'Martin' is aliased or mapped to a user in one or more database(s).
> Drop the user or alias before dropping the login.
> I know that the user 'Martin' has been granted to different DB. If I have
> to drop the login, the login should be revoked the DB access before issue
> the sp_droplogin.
> So, is there a command than can drop the login directly, i mean no matter
> there are any DB associated with the login?
> Just similar to the Enterprise manager, under Security -> Logins ->
> highlight the login and right click "delete"
> It will remove all associated database with the login and then drop the
> login.
> Thanks in advance!
> Martin
>
>|||oh, no shortcut...bad to know the fact... :p
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:um7DtsTyHHA.4276@.TK2MSFTNGP05.phx.gbl...
> Behind the scenes, EM is dropping the user from the databases to which
> (s)he
> had been granted access, and then it drops the login. To script it, you
> would have to loop through all DB's for that user and do the drops,
> followed
> by sp_droplogin.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Atenza" <Atenza@.mail.hongkong.com> wrote in message
> news:uRl$boTyHHA.3564@.TK2MSFTNGP04.phx.gbl...
> Hi all,
> when i execute the sp, EXEC master..sp_droplogin 'Martin'
> it returns error:
> Login 'Martin' is aliased or mapped to a user in one or more database(s).
> Drop the user or alias before dropping the login.
> I know that the user 'Martin' has been granted to different DB. If I have
> to
> drop the login, the login should be revoked the DB access before issue the
> sp_droplogin.
> So, is there a command than can drop the login directly, i mean no matter
> there are any DB associated with the login?
> Just similar to the Enterprise manager, under Security -> Logins ->
> highlight the login and right click "delete"
> It will remove all associated database with the login and then drop the
> login.
> Thanks in advance!
> Martin
>
>|||woo, this absolutely new to me.....sounds great...
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ufvFgxTyHHA.4824@.TK2MSFTNGP02.phx.gbl...
> Hi
> You can run SQL Server Profiler to see what is going on behind the scenes
> when you delete the LOGIN via EM
>
> "Atenza" <Atenza@.mail.hongkong.com> wrote in message
> news:uRl$boTyHHA.3564@.TK2MSFTNGP04.phx.gbl...
>> Hi all,
>> when i execute the sp, EXEC master..sp_droplogin 'Martin'
>> it returns error:
>> Login 'Martin' is aliased or mapped to a user in one or more database(s).
>> Drop the user or alias before dropping the login.
>> I know that the user 'Martin' has been granted to different DB. If I have
>> to drop the login, the login should be revoked the DB access before issue
>> the sp_droplogin.
>> So, is there a command than can drop the login directly, i mean no matter
>> there are any DB associated with the login?
>> Just similar to the Enterprise manager, under Security -> Logins ->
>> highlight the login and right click "delete"
>> It will remove all associated database with the login and then drop the
>> login.
>> Thanks in advance!
>> Martin
>>
>

how to drop index?

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'&

---

Hello SQL Server SMTP SQL
Mail

---

',
@.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
endHI!
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.com...
> 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'&

---

Hello SQL Server SMTP SQL
> Mail

---

',
> @.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

how to drop index?

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