Droping/Removing Identity from a Column

I want to remove Identity from a column within my table. I know that I can do it via the Enterprise Manager. But I need to remove it via a script. Does any one know how this can be done?
I've tried the following:
Alter Table FX_Operator_List drop Identity Operator_ID
go
In the above statment I want to drop "Identity" from column "Operator_ID"
in the table "FX_Operator_List".
I can't get it to work.
Sorry, you can't add or remove the identity property through ALTER TABLE.
You can do what enterprise manager does behind the scenes, or a slight
variation:
- add an INT column, move the data, drop the old column, rename the new
column
- create a new table, move the data over, drop the old table, rename the new
table
I'll leave it as an exercise to the reader to figure out which is which...
but both will accomplish the goal.
http://www.aspfaq.com/
(Reverse address to reply.)
"Larry Bird" <LarryBird@.discussions.microsoft.com> wrote in message
news:70EBD2DF-5924-4CBF-9EEC-3B895538A767@.microsoft.com...
> I want to remove Identity from a column within my table. I know that I
can do it via the Enterprise Manager. But I need to remove it via a script.
Does any one know how this can be done?
> I've tried the following:
> Alter Table FX_Operator_List drop Identity Operator_ID
> go
> In the above statment I want to drop "Identity" from column "Operator_ID"
> in the table "FX_Operator_List".
> I can't get it to work.
|||Behind the scenes, EM re-creates the table without the identity property.
You'll have to do the same.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Larry Bird" <LarryBird@.discussions.microsoft.com> wrote in message
news:70EBD2DF-5924-4CBF-9EEC-3B895538A767@.microsoft.com...
I want to remove Identity from a column within my table. I know that I can
do it via the Enterprise Manager. But I need to remove it via a script.
Does any one know how this can be done?
I've tried the following:
Alter Table FX_Operator_List drop Identity Operator_ID
go
In the above statment I want to drop "Identity" from column "Operator_ID"
in the table "FX_Operator_List".
I can't get it to work.
sql

Droping/Removing Identity from a Column

I want to remove Identity from a column within my table. I know that I can do it via the Enterprise Manager. But I need to remove it via a script. Does any one know how this can be done?
I've tried the following:
Alter Table FX_Operator_List drop Identity Operator_ID
go
In the above statment I want to drop "Identity" from column "Operator_ID"
in the table "FX_Operator_List".
I can't get it to work.Sorry, you can't add or remove the identity property through ALTER TABLE.
You can do what enterprise manager does behind the scenes, or a slight
variation:
- add an INT column, move the data, drop the old column, rename the new
column
- create a new table, move the data over, drop the old table, rename the new
table
I'll leave it as an exercise to the reader to figure out which is which...
but both will accomplish the goal.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Larry Bird" <LarryBird@.discussions.microsoft.com> wrote in message
news:70EBD2DF-5924-4CBF-9EEC-3B895538A767@.microsoft.com...
> I want to remove Identity from a column within my table. I know that I
can do it via the Enterprise Manager. But I need to remove it via a script.
Does any one know how this can be done?
> I've tried the following:
> Alter Table FX_Operator_List drop Identity Operator_ID
> go
> In the above statment I want to drop "Identity" from column "Operator_ID"
> in the table "FX_Operator_List".
> I can't get it to work.|||Behind the scenes, EM re-creates the table without the identity property.
You'll have to do the same.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Larry Bird" <LarryBird@.discussions.microsoft.com> wrote in message
news:70EBD2DF-5924-4CBF-9EEC-3B895538A767@.microsoft.com...
I want to remove Identity from a column within my table. I know that I can
do it via the Enterprise Manager. But I need to remove it via a script.
Does any one know how this can be done?
I've tried the following:
Alter Table FX_Operator_List drop Identity Operator_ID
go
In the above statment I want to drop "Identity" from column "Operator_ID"
in the table "FX_Operator_List".
I can't get it to work.

Droping/Removing Identity from a Column

I want to remove Identity from a column within my table. I know that I can
do it via the Enterprise Manager. But I need to remove it via a script. Do
es any one know how this can be done?
I've tried the following:
Alter Table FX_Operator_List drop Identity Operator_ID
go
In the above statment I want to drop "Identity" from column "Operator_ID"
in the table "FX_Operator_List".
I can't get it to work.Sorry, you can't add or remove the identity property through ALTER TABLE.
You can do what enterprise manager does behind the scenes, or a slight
variation:
- add an INT column, move the data, drop the old column, rename the new
column
- create a new table, move the data over, drop the old table, rename the new
table
I'll leave it as an exercise to the reader to figure out which is which...
but both will accomplish the goal.
http://www.aspfaq.com/
(Reverse address to reply.)
"Larry Bird" <LarryBird@.discussions.microsoft.com> wrote in message
news:70EBD2DF-5924-4CBF-9EEC-3B895538A767@.microsoft.com...
> I want to remove Identity from a column within my table. I know that I
can do it via the Enterprise Manager. But I need to remove it via a script.
Does any one know how this can be done?
> I've tried the following:
> Alter Table FX_Operator_List drop Identity Operator_ID
> go
> In the above statment I want to drop "Identity" from column "Operator_ID"
> in the table "FX_Operator_List".
> I can't get it to work.|||Behind the scenes, EM re-creates the table without the identity property.
You'll have to do the same.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Larry Bird" <LarryBird@.discussions.microsoft.com> wrote in message
news:70EBD2DF-5924-4CBF-9EEC-3B895538A767@.microsoft.com...
I want to remove Identity from a column within my table. I know that I can
do it via the Enterprise Manager. But I need to remove it via a script.
Does any one know how this can be done?
I've tried the following:
Alter Table FX_Operator_List drop Identity Operator_ID
go
In the above statment I want to drop "Identity" from column "Operator_ID"
in the table "FX_Operator_List".
I can't get it to work.

Droping suspect publication database

Hi.
I have a publication databse that become suspect. It is a test database, and
I don't have backup.
I'm trying drop this, but I can't, SQL Server return a error message: Cannot
drop the database 'teste_replica' because it is being used for replication.
I tried reset suspect status with sp_resetstatus but it doesn't worked.
Any suggestion?
Tanks.
sio Nunes
If you have tried sp_resetstatus, I would just like to know whether you
STOPPED and RE-STARTED services of SQL thereafter?
I hope this helps !
Reetesh B. Chhatpar
ShawMan Software Enterprises
www.shawmansoftware.com
Diamond is just another piece of coal that did well under pressure.
"Esio Nunes" <esio_nunes@.hotmail.com> wrote in message
news:Or%23B$gV0EHA.3120@.TK2MSFTNGP12.phx.gbl...
> Hi.
> I have a publication databse that become suspect. It is a test database,
and
> I don't have backup.
> I'm trying drop this, but I can't, SQL Server return a error message:
Cannot
> drop the database 'teste_replica' because it is being used for
replication.
> I tried reset suspect status with sp_resetstatus but it doesn't worked.
> Any suggestion?
> Tanks.
> sio Nunes
>
|||what happens when you issue a
sp_replicationdboption 'PublicationDatabase','publish', 'false'
or
sp_replicationdboption 'PublicationDatabase','merge publish', 'false'
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Reetesh B. Chhatpar" <reetesh@.widesystems.com> wrote in message
news:exURfqV0EHA.3596@.TK2MSFTNGP12.phx.gbl...
> If you have tried sp_resetstatus, I would just like to know whether you
> STOPPED and RE-STARTED services of SQL thereafter?
> I hope this helps !
> Reetesh B. Chhatpar
> ShawMan Software Enterprises
> www.shawmansoftware.com
> Diamond is just another piece of coal that did well under pressure.
>
> "Esio Nunes" <esio_nunes@.hotmail.com> wrote in message
> news:Or%23B$gV0EHA.3120@.TK2MSFTNGP12.phx.gbl...
> and
> Cannot
> replication.
>
|||I can't acess this database, since it is suspect.
Error messeage with sp_replicationdboption:
Server: Msg 945, Level 14, State 2, Line 183
Database 'TESTE_REPLICA' cannot be opened due to inaccessible files or
insufficient memory or disk space. See the SQL Server errorlog for details.
sio
"Hilary Cotter" <hilary.cotter@.gmail.com> escreveu na mensagem
news:uHh30zV0EHA.416@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> what happens when you issue a
> sp_replicationdboption 'PublicationDatabase','publish', 'false'
> or
> sp_replicationdboption 'PublicationDatabase','merge publish', 'false'
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> Now available for purchase at:
> http://www.nwsu.com/0974973602.html
> "Reetesh B. Chhatpar" <reetesh@.widesystems.com> wrote in message
> news:exURfqV0EHA.3596@.TK2MSFTNGP12.phx.gbl...
database,
>
|||I restarted the server, but it doesn't work.
sio
"Reetesh B. Chhatpar" <reetesh@.widesystems.com> escreveu na mensagem
news:exURfqV0EHA.3596@.TK2MSFTNGP12.phx.gbl...
> If you have tried sp_resetstatus, I would just like to know whether you
> STOPPED and RE-STARTED services of SQL thereafter?
> I hope this helps !
> Reetesh B. Chhatpar
> ShawMan Software Enterprises
> www.shawmansoftware.com
> Diamond is just another piece of coal that did well under pressure.
>
> "Esio Nunes" <esio_nunes@.hotmail.com> wrote in message
> news:Or%23B$gV0EHA.3120@.TK2MSFTNGP12.phx.gbl...
> and
> Cannot
> replication.
>
|||I've run into this problem.
I think the best way to get around it is to stop sql server and then
manually move the related database files to another location.
Then restart SQL Server and delete the rows from the sysdatabases. Then
recreate the database, and restore from backup.
I would advise you to open a support incident with Microsoft PSS on this one
for getting a supported way to fix this problem.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Esio Nunes" <esio_nunes@.hotmail.com> wrote in message
news:OuZP66V0EHA.1860@.TK2MSFTNGP15.phx.gbl...
>I can't acess this database, since it is suspect.
> Error messeage with sp_replicationdboption:
> Server: Msg 945, Level 14, State 2, Line 183
> Database 'TESTE_REPLICA' cannot be opened due to inaccessible files or
> insufficient memory or disk space. See the SQL Server errorlog for
> details.
>
> sio
> "Hilary Cotter" <hilary.cotter@.gmail.com> escreveu na mensagem
> news:uHh30zV0EHA.416@.TK2MSFTNGP10.phx.gbl...
> database,
>
|||1. Put the db into EMERGENCY BYPASS STATUS (32768)
-- so you can rebuild the log
sp_configure 'allow', 1
go
reconfigure with override
update sysdatabases
set status = -32768
where name='yourDBname'
2. Stop sql server - rename log file (You will delete
it later)
3. Start sql server. Rebuild the log.
dbcc rebuild_log
('yourDBname', 'TheDriveForLogFiles:\PathToLogFiles\logfilename.l df')
3. Put db back from EMERGENCY BYPASS STATUS (32768) to
status 16
update sysdatabases
set status = 16 -- or 24
where 'yourDBname'
4. Clean up
sp_configure 'allow', 0
go
reconfigure with override
Now you have solved the most probable cause of your 'Suspect’ status: a
corrupted log file. After this you have a clean log file and SQL will be able
to start the DB not heaving to roll back or forward corrupted transactions.
Then you can run DBCC CheckDB to see if the data file is OK or you can drop
the database as you wish.
|||I tryied this but the problem is with the data file, not log file.
sio
"Adrian Mos" <AdrianMos@.discussions.microsoft.com> escreveu na mensagem
news:3EF556B1-B7A2-4CC8-B461-7093574C68F3@.microsoft.com...
> 1. Put the db into EMERGENCY BYPASS STATUS (32768)
> -- so you can rebuild the log
> sp_configure 'allow', 1
> go
> reconfigure with override
> update sysdatabases
> set status = -32768
> where name='yourDBname'
> 2. Stop sql server - rename log file (You will delete
> it later)
> 3. Start sql server. Rebuild the log.
> dbcc rebuild_log
> ('yourDBname', 'TheDriveForLogFiles:\PathToLogFiles\logfilename.l df')
> 3. Put db back from EMERGENCY BYPASS STATUS (32768) to
> status 16
> update sysdatabases
> set status = 16 -- or 24
> where 'yourDBname'
> 4. Clean up
> sp_configure 'allow', 0
> go
> reconfigure with override
>
> Now you have solved the most probable cause of your 'Suspect' status: a
> corrupted log file. After this you have a clean log file and SQL will be
able
> to start the DB not heaving to roll back or forward corrupted
transactions.
> Then you can run DBCC CheckDB to see if the data file is OK or you can
drop
> the database as you wish.

Droping Partition Schema SQL 2005

I have a Partition schema PS1 mapped to a table name 'A'. And there
are 4 views v1,v2,v3,v4 are created based on table 'A'. I want drop
the Drop the Partition Schema. Could you help me to drop the Partition
Schema please?.
Thanks
>I have a Partition schema PS1 mapped to a table name 'A'. And there
> are 4 views v1,v2,v3,v4 are created based on table 'A'. I want drop
> the Drop the Partition Schema. Could you help me to drop the Partition
> Schema please?.
You can't drop the partition scheme while the table is still using it, so
the solution is to move the table to a filegroup or another partition
scheme. How you do that depends on whether you have indexes on the table or
not. The following information gives you some alternative methods depending
on your situation.
If you do not have a clustered index on the table.
1. Create a clustered index on the table in the statement specify a
filegroup (or other partition scheme). This places the data in the specified
filegroup by moving the data from the table to the leaf level of the
clustered index. Note that if you have nonclustered indexes on the table,
you should drop these before creating the clustered index. You can recreate
the nonclustered indexes after you have created the clustered index.
The syntax is
CREATE CLUSTERED INDEX index_name ON table_name(column_name)
ON filegroup_name -- for example, ON DEFAULT if you want to use the
default filegroup
2. Drop the clustered index if you don't want to keep it and the data will
remain in the filegroup.
3. Recreate any nonclustered indexes.
If you already have a clustered index on the table.
1. Drop any nonclustered indexes. You can recreate them later.
2. Use the DROP INDEX statement with the MOVE TO clause.
The syntax is
DROP INDEX index_name ON table_name
WITH (MOVE TO filegroup_name);
3. Recreate the clustered index and then any nonclustered indexes on the
table.
An alternative to using DROP INDEX is the CREATE INDEX WITH DROP_EXISTING
statement. The index name and column name(s) must be the same as the
existing index.
CREATE CLUSTERED INDEX index_name ON table_name(column_name)
WITH (DROP_EXISTING = ON)
ON filegroup_name
Once you have the table moved off of the partition, you can drop the
partition schemee using DROP PARTITION SCHEME partition_scheme_name
Hope that gets you going.
Gail
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
"Praveen" <apveen@.gmail.com> wrote in message
news:1186027495.971909.295310@.19g2000hsx.googlegro ups.com...
>I have a Partition schema PS1 mapped to a table name 'A'. And there
> are 4 views v1,v2,v3,v4 are created based on table 'A'. I want drop
> the Drop the Partition Schema. Could you help me to drop the Partition
> Schema please?.
> Thanks
>

Droping Partition Schema SQL 2005

I have a Partition schema PS1 mapped to a table name 'A'. And there
are 4 views v1,v2,v3,v4 are created based on table 'A'. I want drop
the Drop the Partition Schema. Could you help me to drop the Partition
Schema please?.
Thanks>I have a Partition schema PS1 mapped to a table name 'A'. And there
> are 4 views v1,v2,v3,v4 are created based on table 'A'. I want drop
> the Drop the Partition Schema. Could you help me to drop the Partition
> Schema please?.
You can't drop the partition scheme while the table is still using it, so
the solution is to move the table to a filegroup or another partition
scheme. How you do that depends on whether you have indexes on the table or
not. The following information gives you some alternative methods depending
on your situation.
If you do not have a clustered index on the table.
1. Create a clustered index on the table in the statement specify a
filegroup (or other partition scheme). This places the data in the specified
filegroup by moving the data from the table to the leaf level of the
clustered index. Note that if you have nonclustered indexes on the table,
you should drop these before creating the clustered index. You can recreate
the nonclustered indexes after you have created the clustered index.
The syntax is
CREATE CLUSTERED INDEX index_name ON table_name(column_name)
ON filegroup_name -- for example, ON DEFAULT if you want to use the
default filegroup
2. Drop the clustered index if you don't want to keep it and the data will
remain in the filegroup.
3. Recreate any nonclustered indexes.
If you already have a clustered index on the table.
1. Drop any nonclustered indexes. You can recreate them later.
2. Use the DROP INDEX statement with the MOVE TO clause.
The syntax is
DROP INDEX index_name ON table_name
WITH (MOVE TO filegroup_name);
3. Recreate the clustered index and then any nonclustered indexes on the
table.
An alternative to using DROP INDEX is the CREATE INDEX WITH DROP_EXISTING
statement. The index name and column name(s) must be the same as the
existing index.
CREATE CLUSTERED INDEX index_name ON table_name(column_name)
WITH (DROP_EXISTING = ON)
ON filegroup_name
Once you have the table moved off of the partition, you can drop the
partition schemee using DROP PARTITION SCHEME partition_scheme_name
Hope that gets you going.
Gail
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/...r/bb428874.aspx
"Praveen" <apveen@.gmail.com> wrote in message
news:1186027495.971909.295310@.19g2000hsx.googlegroups.com...
>I have a Partition schema PS1 mapped to a table name 'A'. And there
> are 4 views v1,v2,v3,v4 are created based on table 'A'. I want drop
> the Drop the Partition Schema. Could you help me to drop the Partition
> Schema please?.
> Thanks
>sql

Droping Partition Schema SQL 2005

I have a Partition schema PS1 mapped to a table name 'A'. And there
are 4 views v1,v2,v3,v4 are created based on table 'A'. I want drop
the Drop the Partition Schema. Could you help me to drop the Partition
Schema please?.
Thanks>I have a Partition schema PS1 mapped to a table name 'A'. And there
> are 4 views v1,v2,v3,v4 are created based on table 'A'. I want drop
> the Drop the Partition Schema. Could you help me to drop the Partition
> Schema please?.
You can't drop the partition scheme while the table is still using it, so
the solution is to move the table to a filegroup or another partition
scheme. How you do that depends on whether you have indexes on the table or
not. The following information gives you some alternative methods depending
on your situation.
If you do not have a clustered index on the table.
1. Create a clustered index on the table in the statement specify a
filegroup (or other partition scheme). This places the data in the specified
filegroup by moving the data from the table to the leaf level of the
clustered index. Note that if you have nonclustered indexes on the table,
you should drop these before creating the clustered index. You can recreate
the nonclustered indexes after you have created the clustered index.
The syntax is
CREATE CLUSTERED INDEX index_name ON table_name(column_name)
ON filegroup_name -- for example, ON DEFAULT if you want to use the
default filegroup
2. Drop the clustered index if you don't want to keep it and the data will
remain in the filegroup.
3. Recreate any nonclustered indexes.
If you already have a clustered index on the table.
1. Drop any nonclustered indexes. You can recreate them later.
2. Use the DROP INDEX statement with the MOVE TO clause.
The syntax is
DROP INDEX index_name ON table_name
WITH (MOVE TO filegroup_name);
3. Recreate the clustered index and then any nonclustered indexes on the
table.
An alternative to using DROP INDEX is the CREATE INDEX WITH DROP_EXISTING
statement. The index name and column name(s) must be the same as the
existing index.
CREATE CLUSTERED INDEX index_name ON table_name(column_name)
WITH (DROP_EXISTING = ON)
ON filegroup_name
Once you have the table moved off of the partition, you can drop the
partition schemee using DROP PARTITION SCHEME partition_scheme_name
Hope that gets you going.
Gail
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://technet.microsoft.com/en-us/sqlserver/bb428874.aspx
"Praveen" <apveen@.gmail.com> wrote in message
news:1186027495.971909.295310@.19g2000hsx.googlegroups.com...
>I have a Partition schema PS1 mapped to a table name 'A'. And there
> are 4 views v1,v2,v3,v4 are created based on table 'A'. I want drop
> the Drop the Partition Schema. Could you help me to drop the Partition
> Schema please?.
> Thanks
>

Droping Log File

I have two transaction log files, one on C drive and other on D drive. I want to drop the file from that database that is on C drive. Can anyone help me out on this with complete syntax.

I have taken a down time of one and half hour to accomplish this task

Here is what think

Take a full database backup
Take transaction log backup
Shrinkfile using DBCC with truncateonly option
emptyfile using dbcc or drop file using alter database remove

thanks for your input guys

RomeYou can do it all on EM. But the most important thing is to do a checkpoint to make sure everything is on disk, and then do a log backup. The log file should be empty, and you should be able to delete the log file from EM.

Droping and adding new user

Hi All,

I am having a serious problem of removing and adding again an user in a
database.

Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack

Somehow, the user was created earlier but was not able to run query
assigned to him. As a result I wanted to drop and recreate the user.

I have done many possible things to create the users again with the
same user id but failed.

1. I tried to delete this user from the enterprsie manager security-->
logins-->user1
It deletes but when I try to add again, it gives me error message
(Error 15023: user or role u'user1' already exist).

2. Then I tried in the db:
delete sysusers where name='user1'
it deletes the user1.

3. Again tried adding, got the message in 1.

4. Then I tried

use db1
EXEC sp_change_users_login 'Update_One', 'user1', 'user1'

Server: Msg 15291, Level 16, State 1, Procedure sp_change_users_login,
Line 88
Terminating this procedure. The User name 'user1' is absent or invalid.

I also tried master database and ran the following.

EXEC sp_droplogin 'user1'
The login 'user1' does not exist.

But If I try to add the login user1, get the error message.
Error 15023: user or role u'user1' already exist

I also ran the following when I got Ad hoc error message
execute sp_configure "allow updates",1
go
reconfigure with override
go

Could you please tell me how I can solve this problem.

I do highly appreciate your help.

Thanks a million in advance.

best regards,
mamunmicrosoft.public.dotnet.languages.vb (mamun_ah@.hotmail.com) writes:
> 2. Then I tried in the db:
> delete sysusers where name='user1'
> it deletes the user1.

You did what? You should only perform operations on system tables if
instructed so by a Microsoft Support Professional, or if you know
exactly what you are doing.

I would recommend that you open a case with Microsoft to sort this out.
Most likely, this will require more manual repairs, and I certainly
does not want to try to give directions from a distance.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Droping active connections

I have a database that is not droping connections when a
user logs out. Is there anyway to drop multiple
connections to a database? Or is there a way to drop all
connections for a specific user?
Thanks in advance,
SteveJeff,
Thanks. This worked great. It did exactly what I wanted
it to do with just a little bit of tweaking.
Thanks Again,
Steve
>--Original Message--
>there is no built-in way to drop all connections for a
>database or a user, but you can write your own stored
>procedure to exactly do that.
>-- Example
>declare @.dbname sysname
>set @.dbname = 'pubs'
>declare c1 cursor for
>select cast('kill ' as nchar(6)) + cast(spid as nchar
(5))
>KillCommand
> from master..sysprocesses
> where spid > 50
> and spid <> @.@.spid
> and program_Name not like 'SQLAgent%'
> and dbid = db_id(@.dbname)
>declare @.KillCommand nvarchar(60)
>open c1
>fetch c1 into @.KillCommand
>while @.@.fetch_status = 0
>begin
> print @.KillCommand
> exec sp_executesql @.KillCommand
> fetch c1 into @.KillCommand
>end
>close c1
>deallocate c1
>--
>>--Original Message--
>>I have a database that is not droping connections when
a
>>user logs out. Is there anyway to drop multiple
>>connections to a database? Or is there a way to drop
all
>>connections for a specific user?
>>Thanks in advance,
>>Steve
>>.
>.
>

Droping a table in subscrier !

Hello,
I had a merge replication that was replicating a table, and i deleted it,
now it doesn't let me drop that replicated table in my subscriber database.
It says that it is being used for replication.
I even stoped and started my server, but no success.
Any help !!!!!!!!
Thanks,
Mathew
Matthew,
have a look at using sp_MSunmarkreplinfo.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
sql

Droping a subscription

I have two orphan subscriptions on a SQL 2000 server. I need to drop both
of them. Can anyone help me out with the syntax to do that? I have been
trying to use sp_dropsubcriber and sp_dropsubscription, but I can't seem to
figure them out (of course I am assuming that one of these is the one I am
supposed to be using).
The two subscriptions are listed as
'GP8:Bg_Hauler_Tickets:Bg_Hauler_Tickets' and
'LANGES3:Bg_Hauler_Tickets:Bg_Hauler_Tickets'.
Thanks for any help you can give.
Jeff
Jeff,
you probably need to run sp_droppullsubscription on the subscriber. If the
database is no longer involved in replication, I'd run sp_removedbreplication
instead.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||That did the trick.
Thanks
Jeff
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:4D801C50-80FF-4F84-AF2A-22421A8937A5@.microsoft.com...
> Jeff,
> you probably need to run sp_droppullsubscription on the subscriber. If the
> database is no longer involved in replication, I'd run
> sp_removedbreplication
> instead.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Where are these subscriptions showing up? Are they showing up in EM, and
then when you drill down on them you get an error message? or are they in
the subscriber databases and you can't pull new subscriptions there.
If they are showing up in your subscription database you will probably need
to manually delete the contents of following rows
MSreplication_subscriptions
MSsubscription_agents
MSsubscription_properties
-
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Jeff Cichocki" <jeffc@.belgioioso.com> wrote in message
news:eGTI4KYAGHA.272@.TK2MSFTNGP09.phx.gbl...
>I have two orphan subscriptions on a SQL 2000 server. I need to drop both
>of them. Can anyone help me out with the syntax to do that? I have been
>trying to use sp_dropsubcriber and sp_dropsubscription, but I can't seem to
>figure them out (of course I am assuming that one of these is the one I am
>supposed to be using).
> The two subscriptions are listed as
> 'GP8:Bg_Hauler_Tickets:Bg_Hauler_Tickets' and
> 'LANGES3:Bg_Hauler_Tickets:Bg_Hauler_Tickets'.
> Thanks for any help you can give.
> Jeff
>

Droping a database after detached it.

I have detached a CBA database about a week ago and it seems like we don’t
need it any more.
Now, I want to clean my system and ran drop database and it said “Cannot
drop the database 'CBA’, because it does not exist in the system catalog.”
Is that mean I can simply delete the mdf and ldf physical files?
Thanks in advance.
Sanghun
Yes, you can delete the files... If the database is detached, the server no
longer has any knowledge of it.
However, I would highly recommend backing them up first. You never know...
"SangHunJung" <SangHunJung@.discussions.microsoft.com> wrote in message
news:91C87395-8094-45D5-8A1F-5A74BE53AF74@.microsoft.com...
> I have detached a CBA database about a week ago and it seems like we don't
> need it any more.
> Now, I want to clean my system and ran drop database and it said "Cannot
> drop the database 'CBA', because it does not exist in the system catalog."
> Is that mean I can simply delete the mdf and ldf physical files?
> Thanks in advance.
> Sanghun
>
|||> Now, I want to clean my system and ran drop database and it said “Cannot
> drop the database 'CBA’, because it does not exist in the system
catalog.”
> Is that mean I can simply delete the mdf and ldf physical files?
Yes. However if you want to remove all backup history etc., I would
re-attach it and then drop it.
http://www.aspfaq.com/
(Reverse address to reply.)
|||I see, that is what I wanted to do. I renamed to _old before I detached it
so it should be okay to attach again and drop it.
Thank you both of you!
Sunny
"Aaron [SQL Server MVP]" wrote:

> catalog.a€?
> Yes. However if you want to remove all backup history etc., I would
> re-attach it and then drop it.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>

Droping a database after detached it.

I have detached a CBA database about a week ago and it seems like we donâ't
need it any more.
Now, I want to clean my system and ran drop database and it said â'Cannot
drop the database 'CBAâ', because it does not exist in the system catalog.â'
Is that mean I can simply delete the mdf and ldf physical files?
Thanks in advance.
SanghunYes, you can delete the files... If the database is detached, the server no
longer has any knowledge of it.
However, I would highly recommend backing them up first. You never know...
"SangHunJung" <SangHunJung@.discussions.microsoft.com> wrote in message
news:91C87395-8094-45D5-8A1F-5A74BE53AF74@.microsoft.com...
> I have detached a CBA database about a week ago and it seems like we don't
> need it any more.
> Now, I want to clean my system and ran drop database and it said "Cannot
> drop the database 'CBA', because it does not exist in the system catalog."
> Is that mean I can simply delete the mdf and ldf physical files?
> Thanks in advance.
> Sanghun
>|||> Now, I want to clean my system and ran drop database and it said â'Cannot
> drop the database 'CBAâ', because it does not exist in the system
catalog.â'
> Is that mean I can simply delete the mdf and ldf physical files?
Yes. However if you want to remove all backup history etc., I would
re-attach it and then drop it.
--
http://www.aspfaq.com/
(Reverse address to reply.)|||I see, that is what I wanted to do. I renamed to _old before I detached it
so it should be okay to attach again and drop it.
Thank you both of you!
Sunny
"Aaron [SQL Server MVP]" wrote:
> > Now, I want to clean my system and ran drop database and it said ââ?¬Å?Cannot
> > drop the database 'CBA�, because it does not exist in the system
> catalog.�
> > Is that mean I can simply delete the mdf and ldf physical files?
> Yes. However if you want to remove all backup history etc., I would
> re-attach it and then drop it.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>

droping a CONSTRAINT

I am having problem to find the right syntax to DROP a column with contrainst and recrate it
I get an error

if exists ( select * from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME='myTable'
and COLUMN_NAME='myDate' )
ALTER TABLE [dbo].[myTable] DROP COLUMN myDate
GO

ALTER TABLE [dbo].[myTable] WITH NOCHECK ADD
myDate datetime CONSTRAINT [DF_myDate] DEFAULT (GetDate())
GO

Query Analyser says :
Server: Msg 5074, Level 16, State 1, Line 5
The object 'DF_myDate' is dependent on column 'myDate'.
Server: Msg 4922, Level 16, State 1, Line 5
ALTER TABLE DROP COLUMN myDate failed because one or more objects access this column.
Server: Msg 2705, Level 16, State 4, Line 2
Column names in each table must be unique. Column name 'myDate' in table 'dbo.myTable' is specified more than once.

thank you for helpingYou do know that the first statement failed, and since it was isolated in it's own batch by the GO, the second statement tried to run. So that's uderstandable, since the column did not drop, you can't re-add it.

Do this, go in to Enterprise Manager, right click on the table and chose design make your changes, DON'T SAVE them, and click on the save script icon

It will show you exactly what to do|||I get an incredible script !!

I really dont understant
here I dop the column

if exists ( select * from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME='myTable'
and COLUMN_NAME='myDate' )
ALTER TABLE [dbo].[myTable] DROP COLUMN myDate
GO

then it doesnt exist any more ! why I cannot create it after ?|||because u failed to drop it....... u got error message :
Server: Msg 5074, Level 16, State 1, Line 5
The object 'DF_myDate' is dependent on column 'myDate'.
Server: Msg 4922, Level 16, State 1, Line 5
ALTER TABLE DROP COLUMN myDate failed because one or more objects access this column.

u cant drop the column, coz it has constraint 'DF_myDate'. drop the constraint first, then drop the column, then create the column with constraint again.

DROP INDEX gets Msg 3624

Hi all,
Please help.
I am running into problem of droping an Idex.
My statement is:
DROP INDEX tblPkgInfo.Tracking

I get the following:
Server: Msg 3624, Level 20, State 1, Line 1

Location: recbase.cpp:1371
Expression: m_nVars > 0
SPID: 51
Process ID: 2068

Connection Broken
----------

What do I do?What do you get when you run DBCC CHECKTABLE (tablename)?

where tablename is the name of your table