Dropping Loading/Suspect database

Hi:
I restored a database from the backup. It is showing Loading/Suspect in
enterprise manager. How do I drop this database and try again. What could
have caused this? Please let me know.
Thanks
Sohail,
Have you tried the DROP DATABASE statement from Query Analyzer? Is this a
test box, if so and the former doesn't work, you might try KILLing the
process performing the RESTORE (if available).
HTH
Jerry
"Sohail" <Sohail@.discussions.microsoft.com> wrote in message
news:97BDB53E-F7DF-434E-B66F-E9C28B4BC024@.microsoft.com...
> Hi:
> I restored a database from the backup. It is showing Loading/Suspect in
> enterprise manager. How do I drop this database and try again. What
> could
> have caused this? Please let me know.
> Thanks
|||Actually I do not have access to the clients machine. Will the mode field in
the following query tell me the spid.
SELECTmode, status, status2
FROM master..sysdatabases
WHERE name = 'XYZ'
Thanks
"Jerry Spivey" wrote:

> Sohail,
> Have you tried the DROP DATABASE statement from Query Analyzer? Is this a
> test box, if so and the former doesn't work, you might try KILLing the
> process performing the RESTORE (if available).
> HTH
> Jerry
> "Sohail" <Sohail@.discussions.microsoft.com> wrote in message
> news:97BDB53E-F7DF-434E-B66F-E9C28B4BC024@.microsoft.com...
>
>
|||Hi,
SPID column in sysprocess gives the processID. use the below query to get
the peocess id.
SELECT SPID,cmd FROM master..sysdatabases WHERE name = 'XYZ'
After getting the SPID use the KILL statement to kill the old restore
process
KILL SPID
Thanks
Hari
SQL Server MVP
"Sohail" <Sohail@.discussions.microsoft.com> wrote in message
news:7DA75FB6-9B36-48CB-8744-64DD60BFD7D9@.microsoft.com...[vbcol=seagreen]
> Actually I do not have access to the clients machine. Will the mode field
> in
> the following query tell me the spid.
> SELECT mode, status, status2
> FROM master..sysdatabases
> WHERE name = 'XYZ'
> Thanks
> "Jerry Spivey" wrote:
|||Hi Hari:
SPID and CMD fields do not exist in master..sysdatabases table. Is this the
right table?
"Hari Prasad" wrote:

> Hi,
> SPID column in sysprocess gives the processID. use the below query to get
> the peocess id.
> SELECT SPID,cmd FROM master..sysdatabases WHERE name = 'XYZ'
> After getting the SPID use the KILL statement to kill the old restore
> process
> KILL SPID
> Thanks
> Hari
> SQL Server MVP
>
> "Sohail" <Sohail@.discussions.microsoft.com> wrote in message
> news:7DA75FB6-9B36-48CB-8744-64DD60BFD7D9@.microsoft.com...
>
>
sql

Dropping Loading/Suspect database

Hi:
I restored a database from the backup. It is showing Loading/Suspect in
enterprise manager. How do I drop this database and try again. What could
have caused this? Please let me know.
ThanksSohail,
Have you tried the DROP DATABASE statement from Query Analyzer? Is this a
test box, if so and the former doesn't work, you might try KILLing the
process performing the RESTORE (if available).
HTH
Jerry
"Sohail" <Sohail@.discussions.microsoft.com> wrote in message
news:97BDB53E-F7DF-434E-B66F-E9C28B4BC024@.microsoft.com...
> Hi:
> I restored a database from the backup. It is showing Loading/Suspect in
> enterprise manager. How do I drop this database and try again. What
> could
> have caused this? Please let me know.
> Thanks|||Actually I do not have access to the clients machine. Will the mode field in
the following query tell me the spid.
SELECT mode, status, status2
FROM master..sysdatabases
WHERE name = 'XYZ'
Thanks
"Jerry Spivey" wrote:
> Sohail,
> Have you tried the DROP DATABASE statement from Query Analyzer? Is this a
> test box, if so and the former doesn't work, you might try KILLing the
> process performing the RESTORE (if available).
> HTH
> Jerry
> "Sohail" <Sohail@.discussions.microsoft.com> wrote in message
> news:97BDB53E-F7DF-434E-B66F-E9C28B4BC024@.microsoft.com...
> > Hi:
> >
> > I restored a database from the backup. It is showing Loading/Suspect in
> > enterprise manager. How do I drop this database and try again. What
> > could
> > have caused this? Please let me know.
> >
> > Thanks
>
>|||Hi,
SPID column in sysprocess gives the processID. use the below query to get
the peocess id.
SELECT SPID,cmd FROM master..sysdatabases WHERE name = 'XYZ'
After getting the SPID use the KILL statement to kill the old restore
process
KILL SPID
Thanks
Hari
SQL Server MVP
"Sohail" <Sohail@.discussions.microsoft.com> wrote in message
news:7DA75FB6-9B36-48CB-8744-64DD60BFD7D9@.microsoft.com...
> Actually I do not have access to the clients machine. Will the mode field
> in
> the following query tell me the spid.
> SELECT mode, status, status2
> FROM master..sysdatabases
> WHERE name = 'XYZ'
> Thanks
> "Jerry Spivey" wrote:
>> Sohail,
>> Have you tried the DROP DATABASE statement from Query Analyzer? Is this
>> a
>> test box, if so and the former doesn't work, you might try KILLing the
>> process performing the RESTORE (if available).
>> HTH
>> Jerry
>> "Sohail" <Sohail@.discussions.microsoft.com> wrote in message
>> news:97BDB53E-F7DF-434E-B66F-E9C28B4BC024@.microsoft.com...
>> > Hi:
>> >
>> > I restored a database from the backup. It is showing Loading/Suspect
>> > in
>> > enterprise manager. How do I drop this database and try again. What
>> > could
>> > have caused this? Please let me know.
>> >
>> > Thanks
>>|||Hi Hari:
SPID and CMD fields do not exist in master..sysdatabases table. Is this the
right table?
"Hari Prasad" wrote:
> Hi,
> SPID column in sysprocess gives the processID. use the below query to get
> the peocess id.
> SELECT SPID,cmd FROM master..sysdatabases WHERE name = 'XYZ'
> After getting the SPID use the KILL statement to kill the old restore
> process
> KILL SPID
> Thanks
> Hari
> SQL Server MVP
>
> "Sohail" <Sohail@.discussions.microsoft.com> wrote in message
> news:7DA75FB6-9B36-48CB-8744-64DD60BFD7D9@.microsoft.com...
> > Actually I do not have access to the clients machine. Will the mode field
> > in
> > the following query tell me the spid.
> >
> > SELECT mode, status, status2
> > FROM master..sysdatabases
> > WHERE name = 'XYZ'
> >
> > Thanks
> >
> > "Jerry Spivey" wrote:
> >
> >> Sohail,
> >>
> >> Have you tried the DROP DATABASE statement from Query Analyzer? Is this
> >> a
> >> test box, if so and the former doesn't work, you might try KILLing the
> >> process performing the RESTORE (if available).
> >>
> >> HTH
> >>
> >> Jerry
> >> "Sohail" <Sohail@.discussions.microsoft.com> wrote in message
> >> news:97BDB53E-F7DF-434E-B66F-E9C28B4BC024@.microsoft.com...
> >> > Hi:
> >> >
> >> > I restored a database from the backup. It is showing Loading/Suspect
> >> > in
> >> > enterprise manager. How do I drop this database and try again. What
> >> > could
> >> > have caused this? Please let me know.
> >> >
> >> > Thanks
> >>
> >>
> >>
>
>

Dropping Loading/Suspect database

Hi:
I restored a database from the backup. It is showing Loading/Suspect in
enterprise manager. How do I drop this database and try again. What could
have caused this? Please let me know.
ThanksSohail,
Have you tried the DROP DATABASE statement from Query Analyzer? Is this a
test box, if so and the former doesn't work, you might try KILLing the
process performing the RESTORE (if available).
HTH
Jerry
"Sohail" <Sohail@.discussions.microsoft.com> wrote in message
news:97BDB53E-F7DF-434E-B66F-E9C28B4BC024@.microsoft.com...
> Hi:
> I restored a database from the backup. It is showing Loading/Suspect in
> enterprise manager. How do I drop this database and try again. What
> could
> have caused this? Please let me know.
> Thanks|||Actually I do not have access to the clients machine. Will the mode field i
n
the following query tell me the spid.
SELECT mode, status, status2
FROM master..sysdatabases
WHERE name = 'XYZ'
Thanks
"Jerry Spivey" wrote:

> Sohail,
> Have you tried the DROP DATABASE statement from Query Analyzer? Is this a
> test box, if so and the former doesn't work, you might try KILLing the
> process performing the RESTORE (if available).
> HTH
> Jerry
> "Sohail" <Sohail@.discussions.microsoft.com> wrote in message
> news:97BDB53E-F7DF-434E-B66F-E9C28B4BC024@.microsoft.com...
>
>|||Hi,
SPID column in sysprocess gives the processID. use the below query to get
the peocess id.
SELECT SPID,cmd FROM master..sysdatabases WHERE name = 'XYZ'
After getting the SPID use the KILL statement to kill the old restore
process
KILL SPID
Thanks
Hari
SQL Server MVP
"Sohail" <Sohail@.discussions.microsoft.com> wrote in message
news:7DA75FB6-9B36-48CB-8744-64DD60BFD7D9@.microsoft.com...[vbcol=seagreen]
> Actually I do not have access to the clients machine. Will the mode field
> in
> the following query tell me the spid.
> SELECT mode, status, status2
> FROM master..sysdatabases
> WHERE name = 'XYZ'
> Thanks
> "Jerry Spivey" wrote:
>|||Hi Hari:
SPID and CMD fields do not exist in master..sysdatabases table. Is this the
right table?
"Hari Prasad" wrote:

> Hi,
> SPID column in sysprocess gives the processID. use the below query to get
> the peocess id.
> SELECT SPID,cmd FROM master..sysdatabases WHERE name = 'XYZ'
> After getting the SPID use the KILL statement to kill the old restore
> process
> KILL SPID
> Thanks
> Hari
> SQL Server MVP
>
> "Sohail" <Sohail@.discussions.microsoft.com> wrote in message
> news:7DA75FB6-9B36-48CB-8744-64DD60BFD7D9@.microsoft.com...
>
>

dropping database that had replication

Usually I use Enterprise Manager to remove the Merge Replication info before
trying to drop a database.
Now I used sp_dropmergepublication and successfully dropped the merge
publications associated with a database. When I try to do a DROP DATABASE
though, I receive :
Server: Msg 3724, Level 16, State 3, Line 1
Cannot drop the database 'xxxxxx' because it is being used for replication.
I cannot find what stored procedure(s) or other process I need to do to
remove any remaining replication info so the drop database will work.
TIA,
Doug
Doug,
please try sp_removedbreplication 'dbname'
failing that, sp_dboption 'dbname', 'merge publish','false'
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

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.

Drop table causes system to hang

I have a table in SQL 2000 that I cannot drop with either Enterprise Manager
or Query Analyzer (drop table tblName). I don't get an error message, I just
get the hour glass. I got the data out of it - I could ignore it, but I'd
rather get rid of it. I created it with a create table statement, if that
makes any difference.It must be being used by some process somehow.
You may set its access to Restrict Access: Members of... from the
database's properties and then try dropping it again?
Or detach that database and delete its "mdf" (ndf) and "ldf" files.
--
Ekrem Ã?nsoy
"mlwallin" <mlwallin@.discussions.microsoft.com> wrote in message
news:4D3ED88B-5A01-49F5-9210-DE1572BE0DE7@.microsoft.com...
>I have a table in SQL 2000 that I cannot drop with either Enterprise
>Manager
> or Query Analyzer (drop table tblName). I don't get an error message, I
> just
> get the hour glass. I got the data out of it - I could ignore it, but I'd
> rather get rid of it. I created it with a create table statement, if that
> makes any difference.|||Hi Ekrem,
How detaching and deleting mdf and ldf files can help to drop a table?
Thanks,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Ekrem Ã?nsoy" wrote:
> It must be being used by some process somehow.
> You may set its access to Restrict Access: Members of... from the
> database's properties and then try dropping it again?
> Or detach that database and delete its "mdf" (ndf) and "ldf" files.
> --
> Ekrem Ã?nsoy
>
> "mlwallin" <mlwallin@.discussions.microsoft.com> wrote in message
> news:4D3ED88B-5A01-49F5-9210-DE1572BE0DE7@.microsoft.com...
> >I have a table in SQL 2000 that I cannot drop with either Enterprise
> >Manager
> > or Query Analyzer (drop table tblName). I don't get an error message, I
> > just
> > get the hour glass. I got the data out of it - I could ignore it, but I'd
> > rather get rid of it. I created it with a create table statement, if that
> > makes any difference.
>|||I guess I was kinda drunk while typing that =) I probably assumed he wanted
to delete the database itself.
However it could work in terms of disconnecting processes which use the
mentioned table.
--
Ekrem Ã?nsoy
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:EC2F8E1B-A7C0-4147-BEE9-D81CAA9BCC8D@.microsoft.com...
> Hi Ekrem,
> How detaching and deleting mdf and ldf files can help to drop a table?
> Thanks,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Ekrem Ã?nsoy" wrote:
>> It must be being used by some process somehow.
>> You may set its access to Restrict Access: Members of... from the
>> database's properties and then try dropping it again?
>> Or detach that database and delete its "mdf" (ndf) and "ldf" files.
>> --
>> Ekrem Ã?nsoy
>>
>> "mlwallin" <mlwallin@.discussions.microsoft.com> wrote in message
>> news:4D3ED88B-5A01-49F5-9210-DE1572BE0DE7@.microsoft.com...
>> >I have a table in SQL 2000 that I cannot drop with either Enterprise
>> >Manager
>> > or Query Analyzer (drop table tblName). I don't get an error message,
>> > I
>> > just
>> > get the hour glass. I got the data out of it - I could ignore it, but
>> > I'd
>> > rather get rid of it. I created it with a create table statement, if
>> > that
>> > makes any difference.|||How about some constructive advice?
"Ekrem Ã?nsoy" wrote:
> I guess I was kinda drunk while typing that =) I probably assumed he wanted
> to delete the database itself.
> However it could work in terms of disconnecting processes which use the
> mentioned table.
> --
> Ekrem Ã?nsoy
>
> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> news:EC2F8E1B-A7C0-4147-BEE9-D81CAA9BCC8D@.microsoft.com...
> >
> > Hi Ekrem,
> >
> > How detaching and deleting mdf and ldf files can help to drop a table?
> >
> > Thanks,
> >
> > Ben Nevarez
> > Senior Database Administrator
> > AIG SunAmerica
> >
> >
> >
> > "Ekrem Ã?nsoy" wrote:
> >
> >> It must be being used by some process somehow.
> >>
> >> You may set its access to Restrict Access: Members of... from the
> >> database's properties and then try dropping it again?
> >>
> >> Or detach that database and delete its "mdf" (ndf) and "ldf" files.
> >>
> >> --
> >> Ekrem Ã?nsoy
> >>
> >>
> >>
> >> "mlwallin" <mlwallin@.discussions.microsoft.com> wrote in message
> >> news:4D3ED88B-5A01-49F5-9210-DE1572BE0DE7@.microsoft.com...
> >> >I have a table in SQL 2000 that I cannot drop with either Enterprise
> >> >Manager
> >> > or Query Analyzer (drop table tblName). I don't get an error message,
> >> > I
> >> > just
> >> > get the hour glass. I got the data out of it - I could ignore it, but
> >> > I'd
> >> > rather get rid of it. I created it with a create table statement, if
> >> > that
> >> > makes any difference.
> >>
>|||I'd check for blocking (sp_who, sp_who2, sp_lock etc).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"mlwallin" <mlwallin@.discussions.microsoft.com> wrote in message
news:5704E6AD-6BEE-421A-98BD-2F8BD177ABB0@.microsoft.com...
> How about some constructive advice?
>
> "Ekrem Ã?nsoy" wrote:
>> I guess I was kinda drunk while typing that =) I probably assumed he wanted
>> to delete the database itself.
>> However it could work in terms of disconnecting processes which use the
>> mentioned table.
>> --
>> Ekrem Ã?nsoy
>>
>> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
>> news:EC2F8E1B-A7C0-4147-BEE9-D81CAA9BCC8D@.microsoft.com...
>> >
>> > Hi Ekrem,
>> >
>> > How detaching and deleting mdf and ldf files can help to drop a table?
>> >
>> > Thanks,
>> >
>> > Ben Nevarez
>> > Senior Database Administrator
>> > AIG SunAmerica
>> >
>> >
>> >
>> > "Ekrem Ã?nsoy" wrote:
>> >
>> >> It must be being used by some process somehow.
>> >>
>> >> You may set its access to Restrict Access: Members of... from the
>> >> database's properties and then try dropping it again?
>> >>
>> >> Or detach that database and delete its "mdf" (ndf) and "ldf" files.
>> >>
>> >> --
>> >> Ekrem Ã?nsoy
>> >>
>> >>
>> >>
>> >> "mlwallin" <mlwallin@.discussions.microsoft.com> wrote in message
>> >> news:4D3ED88B-5A01-49F5-9210-DE1572BE0DE7@.microsoft.com...
>> >> >I have a table in SQL 2000 that I cannot drop with either Enterprise
>> >> >Manager
>> >> > or Query Analyzer (drop table tblName). I don't get an error message,
>> >> > I
>> >> > just
>> >> > get the hour glass. I got the data out of it - I could ignore it, but
>> >> > I'd
>> >> > rather get rid of it. I created it with a create table statement, if
>> >> > that
>> >> > makes any difference.
>> >>

Drop SA from database in 7.0!

Dear Experts,
I have created a database using the SA account and now sa is the dbo. From
the Security panel in the enterprise manager have created an account with a
password defaulting to the database. Now I wish to drop sa as dbo and when i
try to do that it companins that sa is owning objects in the database! Please
let me know if i can change ownership from sa to new user.
Thanks
Manish Sawjiani
Three Cheers to Technet for the Help!
Manish
Have a look at sp_changeobjectowner in the BOL
"Manish Sawjiani" <ManishSawjiani@.discussions.microsoft.com> wrote in
message news:EFE2DF18-B4D6-4C1F-BEC1-C54F8E0818AD@.microsoft.com...
> Dear Experts,
> I have created a database using the SA account and now sa is the dbo. From
> the Security panel in the enterprise manager have created an account with
a
> password defaulting to the database. Now I wish to drop sa as dbo and when
i
> try to do that it companins that sa is owning objects in the database!
Please
> let me know if i can change ownership from sa to new user.
> Thanks
> Manish Sawjiani
> --
> Three Cheers to Technet for the Help!
|||From Query Analyzer, you can change the database owner using
sp_changedbowner:
USE MyDatabase
EXEC sp_changedbowner 'MyLogin'
Note that the specified account must not be an existing database user. The
account will be mapped to the 'dbo' database user.
Hope this helps.
Dan Guzman
SQL Server MVP
"Manish Sawjiani" <ManishSawjiani@.discussions.microsoft.com> wrote in
message news:EFE2DF18-B4D6-4C1F-BEC1-C54F8E0818AD@.microsoft.com...
> Dear Experts,
> I have created a database using the SA account and now sa is the dbo. From
> the Security panel in the enterprise manager have created an account with
> a
> password defaulting to the database. Now I wish to drop sa as dbo and when
> i
> try to do that it companins that sa is owning objects in the database!
> Please
> let me know if i can change ownership from sa to new user.
> Thanks
> Manish Sawjiani
> --
> Three Cheers to Technet for the Help!
|||Guess at minimum the system object shave to be owned by dbo,
so you cant remove this special user.
http://www.microsoft.com/technet/pro.../c05ppcsq.mspx
and
dbo Versus db_owner
http://www.sqlservercentral.com/colu...abaseroles.asp
HTH, Jens Smeyer

Drop SA from database in 7.0!

Dear Experts,
I have created a database using the SA account and now sa is the dbo. From
the Security panel in the enterprise manager have created an account with a
password defaulting to the database. Now I wish to drop sa as dbo and when i
try to do that it companins that sa is owning objects in the database! Pleas
e
let me know if i can change ownership from sa to new user.
Thanks
Manish Sawjiani
--
Three Cheers to technet for the Help!Manish
Have a look at sp_changeobjectowner in the BOL
"Manish Sawjiani" <ManishSawjiani@.discussions.microsoft.com> wrote in
message news:EFE2DF18-B4D6-4C1F-BEC1-C54F8E0818AD@.microsoft.com...
> Dear Experts,
> I have created a database using the SA account and now sa is the dbo. From
> the Security panel in the enterprise manager have created an account with
a
> password defaulting to the database. Now I wish to drop sa as dbo and when
i
> try to do that it companins that sa is owning objects in the database!
Please
> let me know if i can change ownership from sa to new user.
> Thanks
> Manish Sawjiani
> --
> Three Cheers to technet for the Help!|||From Query Analyzer, you can change the database owner using
sp_changedbowner:
USE MyDatabase
EXEC sp_changedbowner 'MyLogin'
Note that the specified account must not be an existing database user. The
account will be mapped to the 'dbo' database user.
Hope this helps.
Dan Guzman
SQL Server MVP
"Manish Sawjiani" <ManishSawjiani@.discussions.microsoft.com> wrote in
message news:EFE2DF18-B4D6-4C1F-BEC1-C54F8E0818AD@.microsoft.com...
> Dear Experts,
> I have created a database using the SA account and now sa is the dbo. From
> the Security panel in the enterprise manager have created an account with
> a
> password defaulting to the database. Now I wish to drop sa as dbo and when
> i
> try to do that it companins that sa is owning objects in the database!
> Please
> let me know if i can change ownership from sa to new user.
> Thanks
> Manish Sawjiani
> --
> Three Cheers to technet for the Help!|||Guess at minimum the system object shave to be owned by dbo,
so you cant remove this special user.
http://www.microsoft.com/technet/pr...s/c05ppcsq.mspx
and
dbo Versus db_owner
http://www.sqlservercentral.com/col...br />
oles.asp
HTH, Jens Smeyer

drop or rename a table problem

I tried to drop or rename a table and it hang up for 5 minutes in Enterprise
Manager. Then I hard close EM and open it again. The table has not dropped
or changed. May I know how else can I drop the table? Thanks.Use Query Analyzer to drop the table.
drop table <table name>
Regards
Amish Shah|||Is it in 2000 or 2005?
As you didnt recive do not have persmission error iam ruling it out.
Whats the size of the table, if its huge better dont go for Em method, it
can put the EM in hung state. Use
"DROP TABLE tablename" in query analyzer.
--
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"00KobeBrian" wrote:
> I tried to drop or rename a table and it hang up for 5 minutes in Enterprise
> Manager. Then I hard close EM and open it again. The table has not dropped
> or changed. May I know how else can I drop the table? Thanks.
>
>

Drop Database

Hi all,

I try to restore a DB on MS SQL 2000 but something was wrog because restored DB compare on tree view of SQL Enterprise Manager commented with "(Loading/Suspect)".

I can't access to DB and I cant delete it. I obtain this : "Error 3724: Cannot drop the database 'XXXX' bacause it is being used for replication.

How can I delete or solve this problem?

Thank's a lot

bye

HID

Hi hidme...

To get the database out of the loading state, try the following statement:

restore database <dbname> with recovery

Assuming that brings your database up into an accessible state, then try dropping any publications/subscriptions/etc. that are included on the database, or try running the following as a last-ditch resort (only if you plan on destroying the database anyhow hopefully):

exec sp_removedbreplication @.dbname = '<dbname>'

Then you should be able to drop the database, repost if you continue to have problems,

HTH,

|||

Well,

thank's for your rapid suggest and sorry for may long break.

So the problem persist because I can't access to DB for restore..., what do you think if I try to shutdown DBMRS and I delete file_data.mdf ? Do you think a link on tree of SQL Manager go away?

Thanks

hid

|||

Hi hid...when you say you can't access the DB for restore, are you saying that you are trying to switch to that database to perform the restore? If so, you should be able to run the restore statement above from the master database against the database you are having trouble with.

If that's not what you are saying, could you provide a little clarification for me?

Also, don't delete the mdf/ldf files, the database in the tree will not disappear, it will simply show up as corrupt...

|||

I think he is trying to restore using the GUI by right clicking on the database.

hidme, don't use the GUI interface. Write the code into a query window and execute it against the master database.

Drop Database

Hi all,

I try to restore a DB on MS SQL 2000 but something was wrog because restored DB compare on tree view of SQL Enterprise Manager commented with "(Loading/Suspect)".

I can't access to DB and I cant delete it. I obtain this : "Error 3724: Cannot drop the database 'XXXX' bacause it is being used for replication.

How can I delete or solve this problem?

Thank's a lot

bye

HID

Hi hidme...

To get the database out of the loading state, try the following statement:

restore database <dbname> with recovery

Assuming that brings your database up into an accessible state, then try dropping any publications/subscriptions/etc. that are included on the database, or try running the following as a last-ditch resort (only if you plan on destroying the database anyhow hopefully):

exec sp_removedbreplication @.dbname = '<dbname>'

Then you should be able to drop the database, repost if you continue to have problems,

HTH,

|||

Well,

thank's for your rapid suggest and sorry for may long break.

So the problem persist because I can't access to DB for restore..., what do you think if I try to shutdown DBMRS and I delete file_data.mdf ? Do you think a link on tree of SQL Manager go away?

Thanks

hid

|||

Hi hid...when you say you can't access the DB for restore, are you saying that you are trying to switch to that database to perform the restore? If so, you should be able to run the restore statement above from the master database against the database you are having trouble with.

If that's not what you are saying, could you provide a little clarification for me?

Also, don't delete the mdf/ldf files, the database in the tree will not disappear, it will simply show up as corrupt...

|||

I think he is trying to restore using the GUI by right clicking on the database.

hidme, don't use the GUI interface. Write the code into a query window and execute it against the master database.

Drop database

I cannot drop a user defined database in the following situation:
In enterprise manager, I select that database, select tables.
In query analyser, run the statement: use master, go, drop that user
database, an error said that database is currently in use.
Then I back to enterprise manager, collapse the database folder. Run the
statement in query analyser again but got the same error.
The way I get around was go back to enterprise manager, select root level or
upper level database, press refresh.
Back to query analyser and run that SQL again and it worked.
Why do I need to refresh enterprise manager after select the root level
folder or collapse the database folder ?Because EM doesn't want to do unecessary database work unless it has to. So, the connection is still
in the old database until you refresh in EM.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Alan" <alanpltse@.yahoo.com.au> wrote in message news:OnzQiTEaDHA.440@.tk2msftngp13.phx.gbl...
> I cannot drop a user defined database in the following situation:
> In enterprise manager, I select that database, select tables.
> In query analyser, run the statement: use master, go, drop that user
> database, an error said that database is currently in use.
> Then I back to enterprise manager, collapse the database folder. Run the
> statement in query analyser again but got the same error.
> The way I get around was go back to enterprise manager, select root level or
> upper level database, press refresh.
> Back to query analyser and run that SQL again and it worked.
> Why do I need to refresh enterprise manager after select the root level
> folder or collapse the database folder ?
>

Drop a table

I tried to drop a table in Enterprise Manager but it won't allow me and it's
just a user table. How else can I drop it? Thanks.Can you give us the error message?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"00kobebrian" <abc@.yahoo.com> wrote in message news:%23r0w1vonGHA.764@.TK2MSFTNGP03.phx.gbl..
.
>I tried to drop a table in Enterprise Manager but it won't allow me and it'
s just a user table. How
>else can I drop it? Thanks.
>
>|||I hate to be the guy who asks this, but do make sure that you refresh
the treeview on the left after you drop. This happened to me once and I
was stumped for a long time. Finally, I closed enterprise manager and
restarted. Then I realized that the table dropped, but it wasn't being
reflected in the view.
Tibor Karaszi wrote:[vbcol=seagreen]
> Can you give us the error message?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "00kobebrian" <abc@.yahoo.com> wrote in message news:%23r0w1vonGHA.764@.TK2M
SFTNGP03.phx.gbl...|||"Rich" <rich@.adgooroo.com> wrote in message
news:1151941402.034081.206570@.j8g2000cwa.googlegroups.com...
> I hate to be the guy who asks this, but do make sure that you refresh
> the treeview on the left after you drop. This happened to me once and I
> was stumped for a long time. Finally, I closed enterprise manager and
> restarted. Then I realized that the table dropped, but it wasn't being
> reflected in the view.
BTW, you wouldn't be the first person to do this. :-)

>
> Tibor Karaszi wrote:
news:%23r0w1vonGHA.764@.TK2MSFTNGP03.phx.gbl...[vbcol=seagreen]
it's just a user table. How[vbcol=seagreen]
>

Drop a table

I tried to drop a table in Enterprise Manager but it won't allow me and it's
just a user table. How else can I drop it? Thanks.Can you give us the error message?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"00kobebrian" <abc@.yahoo.com> wrote in message news:%23r0w1vonGHA.764@.TK2MSFTNGP03.phx.gbl...
>I tried to drop a table in Enterprise Manager but it won't allow me and it's just a user table. How
>else can I drop it? Thanks.
>
>|||I hate to be the guy who asks this, but do make sure that you refresh
the treeview on the left after you drop. This happened to me once and I
was stumped for a long time. Finally, I closed enterprise manager and
restarted. Then I realized that the table dropped, but it wasn't being
reflected in the view.
Tibor Karaszi wrote:
> Can you give us the error message?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "00kobebrian" <abc@.yahoo.com> wrote in message news:%23r0w1vonGHA.764@.TK2MSFTNGP03.phx.gbl...
> >I tried to drop a table in Enterprise Manager but it won't allow me and it's just a user table. How
> >else can I drop it? Thanks.
> >
> >
> >
> >|||"Rich" <rich@.adgooroo.com> wrote in message
news:1151941402.034081.206570@.j8g2000cwa.googlegroups.com...
> I hate to be the guy who asks this, but do make sure that you refresh
> the treeview on the left after you drop. This happened to me once and I
> was stumped for a long time. Finally, I closed enterprise manager and
> restarted. Then I realized that the table dropped, but it wasn't being
> reflected in the view.
BTW, you wouldn't be the first person to do this. :-)
>
> Tibor Karaszi wrote:
> > Can you give us the error message?
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "00kobebrian" <abc@.yahoo.com> wrote in message
news:%23r0w1vonGHA.764@.TK2MSFTNGP03.phx.gbl...
> > >I tried to drop a table in Enterprise Manager but it won't allow me and
it's just a user table. How
> > >else can I drop it? Thanks.
> > >
> > >
> > >
> > >
>

driver's sqlallochandle on sql_handle_env failed

i have just installed sql clinet
(Microsoft SQL Enterprise Manager Microsoft Corporation Version: 8.0)
on windows XP sp2 . whenever i try to reg new sql servers through Enterprise
Mgr it gives me this error . it looks like its associated with ODBC . need to
figure out whats the problem
Thanks & Regards
Sid
Try applying SQL Server 2000 SP4 + Cumulative Hotfix Build 2187.
http://www.microsoft.com/downloads/details.aspx?FamilyID=8e2dfc8d-c20e-4446-99a9-b7f0213f8bc5&DisplayLang=en
http://www.microsoft.com/downloads/details.aspx?FamilyID=9c9ab140-bdee-44df-b7a3-e6849297754a&displaylang=en
http://www.microsoft.com/downloads/details.aspx?FamilyID=1705bd2f-1fb8-4ec8-b3db-0935361308c7&displaylang=en
http://www.microsoft.com/downloads/details.aspx?familyid=A643980A-26A4-44C1-9B50-53E20E7210B5&displaylang=en
http://www.microsoft.com/downloads/details.aspx?FamilyId=243A8A89-74D6-48FD-933F-32FF9D8459C2&displaylang=en
http://www.microsoft.com/downloads/details.aspx?FamilyId=2BB62F35-D041-42AC-98DA-6EC97168BE21&displaylang=en
Sincerely,
Anthony Thomas

"moharil" <sid_m15@.yahoo.com> wrote in message
news:DCFB87D2-813A-4707-8D8B-9EDB6E781058@.microsoft.com...
> i have just installed sql clinet
> (Microsoft SQL Enterprise Manager Microsoft Corporation Version: 8.0)
> on windows XP sp2 . whenever i try to reg new sql servers through
Enterprise
> Mgr it gives me this error . it looks like its associated with ODBC . need
to
> figure out whats the problem
> --
> Thanks & Regards
> Sid

Drive missing from Enterprise Manager

Hi,

I have an SQL cluster on Windows 2003 with 2 drives (1 for dbs and 1 for logs). Both of these drives are accessible from Windows explorer.

In Enterprise Manager only the db drive appears as a location to house dbs and logs.

I can't work out how to get the second drive to appear and adding the path manual doesn't work as I end up with the other drive letter appending to the front eg "y:\z:\logs"

Has anyone seen this or have any ideas how to fix it?

Help much appreciated.What's your cluster like? Active/Passive or Active/Active?|||The cluster is active/passive.

Just figured it out, I forgot to add the log drive to the dependennce list for the SQL service.

DrillThrough - How to do it under OWC

I define the Drillthrough and able to browse the data under Analysis Manager.
But I can drillthrough under OWC(Office Web Components).
Can anyone let me know how to do it?
Look at the OWC Tool Pack -- it has an example.
http://www.microsoft.com/downloads/d...DisplayLang=en
Hope that helps.
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kam" <Kam@.discussions.microsoft.com> wrote in message
news:A9BF9E7B-0697-461B-B7E3-914AEBF4F0FF@.microsoft.com...
> I define the Drillthrough and able to browse the data under Analysis
Manager.
> But I can drillthrough under OWC(Office Web Components).
> Can anyone let me know how to do it?

DrillThrough - How to do it under OWC

I define the Drillthrough and able to browse the data under Analysis Manager
.
But I can drillthrough under OWC(Office Web Components).
Can anyone let me know how to do it?Look at the OWC Tool Pack -- it has an example.
http://www.microsoft.com/downloads/...&DisplayLang=en
Hope that helps.
--
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kam" <Kam@.discussions.microsoft.com> wrote in message
news:A9BF9E7B-0697-461B-B7E3-914AEBF4F0FF@.microsoft.com...
> I define the Drillthrough and able to browse the data under Analysis
Manager.
> But I can drillthrough under OWC(Office Web Components).
> Can anyone let me know how to do it?