can not drop user from database

I can not delete user from a database in sql2005 beta 3.
the message errror is :

TITLE: SQL Server Management Studio
-

Drop failed for User 'Amministratore'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft SQL Server&ProdVer=9.00.0981.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Drop+User&LinkId=20476

-
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

-

The database principal owns a schema and cannot be dropped. (Microsoft SQL Server, Error: 15138)

in sql 2000 I can delete the user very easy, but in sql 2005 I don't understant How to do it.

In SQL Server 2005, schemas are real entities. You cannot drop a user that owns schemas; you first have to either drop the schemas or change their owner to be another user.

Thanks
Laurentiu

|||

The previous answer is helpful. I am just elaborating for newbies in SQL 2005.

1) Expand Schemas(should be like a folder under <yourdatabase> -> Security) .
2) Delete the unwanted "userSchema".
3) Then, go back to the User(a folder like thing) and delete it.

|||

Hi,

I am having the same problem. When i went to delete the schema..i got the error message "drop failed for schema"

Cannot drop schema 'wch1' because it is being referenced by object 'Alert_List'. (.Net SqlClient Data Provider)

any suggestions?

|||

Before dropping a schema, it must be empty. Looks like in your case, you still have an object in the schema: Alert_List. You may either choose to drop this object first or you may choose to move it to another schema (using ALTER SCHEMA TRANSFER). When the schema will be empty, you will be able to drop it.

Thanks
Laurentiu

|||Is there any way i can tell what objects my schema has?|||

You can query the catalogs. For example, you can execute the following query:

select * from sys.objects where schema_id = schema_id('s')

to find out the objects that reside in schema 's'.

Thanks
Laurentiu

|||

I also had this problem.I was not able to find out which Schema that the login owned.I do not know of a stored proc function that will list ownership of schemas given an owner.Maybe someone can add that to this thread.

I was able to see which schemas were owned by the login by viewing the properties of each schema and seeing who what listed as the owner.

Example:

I opened the properties window of schema db_datareader and notices that the owner was User1.I changed the owner to be db_datareader and then was able to drop user1.

Regards,

DataSort

|||

Schemas are owned by users, not by logins. In SQL Server, logins and users are not the same thing.

To find out the schemas owned by a user, you can run the following query:

select * from sys.schemas where principal_id = user_id('user_name')

Thanks
Laurentiu

|||Thank you!

can not drop user from database

I can not delete user from a database in sql2005 beta 3.
the message errror is :

TITLE: SQL Server Management Studio
-

Drop failed for User 'Amministratore'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft SQL Server&ProdVer=9.00.0981.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Drop+User&LinkId=20476

-
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

-

The database principal owns a schema and cannot be dropped. (Microsoft SQL Server, Error: 15138)

in sql 2000 I can delete the user very easy, but in sql 2005 I don't understant How to do it.

In SQL Server 2005, schemas are real entities. You cannot drop a user that owns schemas; you first have to either drop the schemas or change their owner to be another user.

Thanks
Laurentiu

|||

The previous answer is helpful. I am just elaborating for newbies in SQL 2005.

1) Expand Schemas(should be like a folder under <yourdatabase> -> Security) .
2) Delete the unwanted "userSchema".
3) Then, go back to the User(a folder like thing) and delete it.

|||

Hi,

I am having the same problem. When i went to delete the schema..i got the error message "drop failed for schema"

Cannot drop schema 'wch1' because it is being referenced by object 'Alert_List'. (.Net SqlClient Data Provider)

any suggestions?

|||

Before dropping a schema, it must be empty. Looks like in your case, you still have an object in the schema: Alert_List. You may either choose to drop this object first or you may choose to move it to another schema (using ALTER SCHEMA TRANSFER). When the schema will be empty, you will be able to drop it.

Thanks
Laurentiu

|||Is there any way i can tell what objects my schema has?|||

You can query the catalogs. For example, you can execute the following query:

select * from sys.objects where schema_id = schema_id('s')

to find out the objects that reside in schema 's'.

Thanks
Laurentiu

|||

I also had this problem.I was not able to find out which Schema that the login owned.I do not know of a stored proc function that will list ownership of schemas given an owner.Maybe someone can add that to this thread.

I was able to see which schemas were owned by the login by viewing the properties of each schema and seeing who what listed as the owner.

Example:

I opened the properties window of schema db_datareader and notices that the owner was User1.I changed the owner to be db_datareader and then was able to drop user1.

Regards,

DataSort

|||

Schemas are owned by users, not by logins. In SQL Server, logins and users are not the same thing.

To find out the schemas owned by a user, you can run the following query:

select * from sys.schemas where principal_id = user_id('user_name')

Thanks
Laurentiu

|||Thank you!

can not drop user from database

I can not delete user from a database in sql2005 beta 3.
the message errror is :

TITLE: SQL Server Management Studio
-

Drop failed for User 'Amministratore'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft SQL Server&ProdVer=9.00.0981.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Drop+User&LinkId=20476

-
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

-

The database principal owns a schema and cannot be dropped. (Microsoft SQL Server, Error: 15138)

in sql 2000 I can delete the user very easy, but in sql 2005 I don't understant How to do it.

In SQL Server 2005, schemas are real entities. You cannot drop a user that owns schemas; you first have to either drop the schemas or change their owner to be another user.

Thanks
Laurentiu

|||

The previous answer is helpful. I am just elaborating for newbies in SQL 2005.

1) Expand Schemas(should be like a folder under <yourdatabase> -> Security) .
2) Delete the unwanted "userSchema".
3) Then, go back to the User(a folder like thing) and delete it.

|||

Hi,

I am having the same problem. When i went to delete the schema..i got the error message "drop failed for schema"

Cannot drop schema 'wch1' because it is being referenced by object 'Alert_List'. (.Net SqlClient Data Provider)

any suggestions?

|||

Before dropping a schema, it must be empty. Looks like in your case, you still have an object in the schema: Alert_List. You may either choose to drop this object first or you may choose to move it to another schema (using ALTER SCHEMA TRANSFER). When the schema will be empty, you will be able to drop it.

Thanks
Laurentiu

|||Is there any way i can tell what objects my schema has?|||

You can query the catalogs. For example, you can execute the following query:

select * from sys.objects where schema_id = schema_id('s')

to find out the objects that reside in schema 's'.

Thanks
Laurentiu

|||

I also had this problem.I was not able to find out which Schema that the login owned.I do not know of a stored proc function that will list ownership of schemas given an owner.Maybe someone can add that to this thread.

I was able to see which schemas were owned by the login by viewing the properties of each schema and seeing who what listed as the owner.

Example:

I opened the properties window of schema db_datareader and notices that the owner was User1.I changed the owner to be db_datareader and then was able to drop user1.

Regards,

DataSort

|||

Schemas are owned by users, not by logins. In SQL Server, logins and users are not the same thing.

To find out the schemas owned by a user, you can run the following query:

select * from sys.schemas where principal_id = user_id('user_name')

Thanks
Laurentiu

|||Thank you!

can not drop user from database

I can not delete user from a database in sql2005 beta 3.
the message errror is :

TITLE: SQL Server Management Studio
-

Drop failed for User 'Amministratore'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft SQL Server&ProdVer=9.00.0981.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Drop+User&LinkId=20476

-
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

-

The database principal owns a schema and cannot be dropped. (Microsoft SQL Server, Error: 15138)

in sql 2000 I can delete the user very easy, but in sql 2005 I don't understant How to do it.

In SQL Server 2005, schemas are real entities. You cannot drop a user that owns schemas; you first have to either drop the schemas or change their owner to be another user.

Thanks
Laurentiu

|||

The previous answer is helpful. I am just elaborating for newbies in SQL 2005.

1) Expand Schemas(should be like a folder under <yourdatabase> -> Security) .
2) Delete the unwanted "userSchema".
3) Then, go back to the User(a folder like thing) and delete it.

|||

Hi,

I am having the same problem. When i went to delete the schema..i got the error message "drop failed for schema"

Cannot drop schema 'wch1' because it is being referenced by object 'Alert_List'. (.Net SqlClient Data Provider)

any suggestions?

|||

Before dropping a schema, it must be empty. Looks like in your case, you still have an object in the schema: Alert_List. You may either choose to drop this object first or you may choose to move it to another schema (using ALTER SCHEMA TRANSFER). When the schema will be empty, you will be able to drop it.

Thanks
Laurentiu

|||Is there any way i can tell what objects my schema has?|||

You can query the catalogs. For example, you can execute the following query:

select * from sys.objects where schema_id = schema_id('s')

to find out the objects that reside in schema 's'.

Thanks
Laurentiu

|||

I also had this problem.I was not able to find out which Schema that the login owned.I do not know of a stored proc function that will list ownership of schemas given an owner.Maybe someone can add that to this thread.

I was able to see which schemas were owned by the login by viewing the properties of each schema and seeing who what listed as the owner.

Example:

I opened the properties window of schema db_datareader and notices that the owner was User1.I changed the owner to be db_datareader and then was able to drop user1.

Regards,

DataSort

|||

Schemas are owned by users, not by logins. In SQL Server, logins and users are not the same thing.

To find out the schemas owned by a user, you can run the following query:

select*fromsys.schemaswhere principal_id =user_id('user_name')

Thanks
Laurentiu

|||Thank you!

can not drop user from database

I can not delete user from a database in sql2005 beta 3.
the message errror is :

TITLE: SQL Server Management Studio
-

Drop failed for User 'Amministratore'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft SQL Server&ProdVer=9.00.0981.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Drop+User&LinkId=20476

-
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

-

The database principal owns a schema and cannot be dropped. (Microsoft SQL Server, Error: 15138)

in sql 2000 I can delete the user very easy, but in sql 2005 I don't understant How to do it.

In SQL Server 2005, schemas are real entities. You cannot drop a user that owns schemas; you first have to either drop the schemas or change their owner to be another user.

Thanks
Laurentiu

|||

The previous answer is helpful. I am just elaborating for newbies in SQL 2005.

1) Expand Schemas(should be like a folder under <yourdatabase> -> Security) .
2) Delete the unwanted "userSchema".
3) Then, go back to the User(a folder like thing) and delete it.

|||

Hi,

I am having the same problem. When i went to delete the schema..i got the error message "drop failed for schema"

Cannot drop schema 'wch1' because it is being referenced by object 'Alert_List'. (.Net SqlClient Data Provider)

any suggestions?

|||

Before dropping a schema, it must be empty. Looks like in your case, you still have an object in the schema: Alert_List. You may either choose to drop this object first or you may choose to move it to another schema (using ALTER SCHEMA TRANSFER). When the schema will be empty, you will be able to drop it.

Thanks
Laurentiu

|||Is there any way i can tell what objects my schema has?|||

You can query the catalogs. For example, you can execute the following query:

select * from sys.objects where schema_id = schema_id('s')

to find out the objects that reside in schema 's'.

Thanks
Laurentiu

|||

I also had this problem.I was not able to find out which Schema that the login owned.I do not know of a stored proc function that will list ownership of schemas given an owner.Maybe someone can add that to this thread.

I was able to see which schemas were owned by the login by viewing the properties of each schema and seeing who what listed as the owner.

Example:

I opened the properties window of schema db_datareader and notices that the owner was User1.I changed the owner to be db_datareader and then was able to drop user1.

Regards,

DataSort

|||

Schemas are owned by users, not by logins. In SQL Server, logins and users are not the same thing.

To find out the schemas owned by a user, you can run the following query:

select * from sys.schemas where principal_id = user_id('user_name')

Thanks
Laurentiu

|||Thank you!
sql

Can not drop merge replication.

Hi, i have a publisher running sql server 2000 sp3. I have
configured a merge replication with two push
subscriptions. I need to delete the subscriptions and the
publication but when i try to do it, i get this error:
Error 207: invalid column name 'enabled_for_syncmgr'
I'll really appreciate your help because i need to remove
replication from this database.
Regards,
Maria,
try running sp_helpsubscription @.publication = 'publication' , @.article =
'article'.
For each one found, run sp_dropmergesubscription, then drop the publication.
If it errors, please state at which point, and any relevant data from
sp_helpsubscription.
Regards,
Paul Ibison
|||Thanks a lot for your help. Yesterday i tryed to disable
this database for merge publications and it displays the
same error.
After that I execute the procedure you recommend me. Today
I found that the database is not configured for
replication but i still see the publication and
subscriptions in the replication monitor. The snapshot and
the subscriptions are failed displaying:
"The 'databasename' database is not published for merge
replication. "
And the subscribers displays:
"Publication 'publicationname' does not exists. The step
fails."
If i try to delete the publication diplays this error:
"Error: 21776. [SQL-DMO]The name 'publicationname' was not
found in the mergepublications collation..."
What should I do to delete the publication and subscribers
from the replication monitor?
Thanks a lot for your help.

>--Original Message--
>Maria,
>try running sp_helpsubscription @.publication
= 'publication' , @.article =
>'article'.
>For each one found, run sp_dropmergesubscription, then
drop the publication.
>If it errors, please state at which point, and any
relevant data from
>sp_helpsubscription.
>Regards,
>Paul Ibison
>
>.
>
|||Maria,
if you're lucky, this database is not needed to be used for replication as a publisher or subscriber. In this case you can run
sp_removedbreplication 'dbname'.
If not, then I'd script out what you need, remove what you can using hte replication interface in EM, then run the script above because there are some corrupted system table records which are better removed. Subsequently you can readd your scripts. Depend
ing on your replication you may be able to resume replication without reinitializing the data. Anyway, hopefully this is academic and you can simply run the command above on its own.
HTH,
Paul Ibison

can not delete tempdb

Hi,
after a huge ODBC operation to read some data from a DB table, my tempdb
is now 2.8 GB and I can not delete tempdb. Query analyzer does not work,
right mouse click and delete does not work, dbcc checks does not work. I
even stopped the service and started it again, but tempdb did not
regenerated and still 2.8 GB. I manually deleted the tempdb.mdf file and
log file but after I started the MSSQLSERVER service, they are all
recovered as 2.8 GB.
How can I get rid of this tempdb?
regards,
atxTempdb can not be dropped. In fact, your concern is just to reduce size of
the tempdb. You can use DBCC Shrinkdatabase or DBCC Shrinkfile for that.
"atx" wrote:

> Hi,
> after a huge ODBC operation to read some data from a DB table, my tempdb
> is now 2.8 GB and I can not delete tempdb. Query analyzer does not work,
> right mouse click and delete does not work, dbcc checks does not work. I
> even stopped the service and started it again, but tempdb did not
> regenerated and still 2.8 GB. I manually deleted the tempdb.mdf file and
> log file but after I started the MSSQLSERVER service, they are all
> recovered as 2.8 GB.
> How can I get rid of this tempdb?
> regards,
> atx
>|||Look at trying "Method 1 to Shrink Tempdb" in the KB article below
http://support.microsoft.com/?id=307487
"atx" <atx@.xerox.com> wrote in message
news:uBQHOwC9FHA.2832@.TK2MSFTNGP14.phx.gbl...
> Hi,
> after a huge ODBC operation to read some data from a DB table, my tempdb
> is now 2.8 GB and I can not delete tempdb. Query analyzer does not work,
> right mouse click and delete does not work, dbcc checks does not work. I
> even stopped the service and started it again, but tempdb did not
> regenerated and still 2.8 GB. I manually deleted the tempdb.mdf file and
> log file but after I started the MSSQLSERVER service, they are all
> recovered as 2.8 GB.
> How can I get rid of this tempdb?
> regards,
> atx

can not delete tempdb

Hi,
after a huge ODBC operation to read some data from a DB table, my tempdb
is now 2.8 GB and I can not delete tempdb. Query analyzer does not work,
right mouse click and delete does not work, dbcc checks does not work. I
even stopped the service and started it again, but tempdb did not
regenerated and still 2.8 GB. I manually deleted the tempdb.mdf file and
log file but after I started the MSSQLSERVER service, they are all
recovered as 2.8 GB.
How can I get rid of this tempdb?
regards,
atxTempdb can not be dropped. In fact, your concern is just to reduce size of
the tempdb. You can use DBCC Shrinkdatabase or DBCC Shrinkfile for that.
"atx" wrote:
> Hi,
> after a huge ODBC operation to read some data from a DB table, my tempdb
> is now 2.8 GB and I can not delete tempdb. Query analyzer does not work,
> right mouse click and delete does not work, dbcc checks does not work. I
> even stopped the service and started it again, but tempdb did not
> regenerated and still 2.8 GB. I manually deleted the tempdb.mdf file and
> log file but after I started the MSSQLSERVER service, they are all
> recovered as 2.8 GB.
> How can I get rid of this tempdb?
> regards,
> atx
>|||Look at trying "Method 1 to Shrink Tempdb" in the KB article below
http://support.microsoft.com/?id=307487
"atx" <atx@.xerox.com> wrote in message
news:uBQHOwC9FHA.2832@.TK2MSFTNGP14.phx.gbl...
> Hi,
> after a huge ODBC operation to read some data from a DB table, my tempdb
> is now 2.8 GB and I can not delete tempdb. Query analyzer does not work,
> right mouse click and delete does not work, dbcc checks does not work. I
> even stopped the service and started it again, but tempdb did not
> regenerated and still 2.8 GB. I manually deleted the tempdb.mdf file and
> log file but after I started the MSSQLSERVER service, they are all
> recovered as 2.8 GB.
> How can I get rid of this tempdb?
> regards,
> atx

can not delete tempdb

Hi,
after a huge ODBC operation to read some data from a DB table, my tempdb
is now 2.8 GB and I can not delete tempdb. Query analyzer does not work,
right mouse click and delete does not work, dbcc checks does not work. I
even stopped the service and started it again, but tempdb did not
regenerated and still 2.8 GB. I manually deleted the tempdb.mdf file and
log file but after I started the MSSQLSERVER service, they are all
recovered as 2.8 GB.
How can I get rid of this tempdb?
regards,
atx
Tempdb can not be dropped. In fact, your concern is just to reduce size of
the tempdb. You can use DBCC Shrinkdatabase or DBCC Shrinkfile for that.
"atx" wrote:

> Hi,
> after a huge ODBC operation to read some data from a DB table, my tempdb
> is now 2.8 GB and I can not delete tempdb. Query analyzer does not work,
> right mouse click and delete does not work, dbcc checks does not work. I
> even stopped the service and started it again, but tempdb did not
> regenerated and still 2.8 GB. I manually deleted the tempdb.mdf file and
> log file but after I started the MSSQLSERVER service, they are all
> recovered as 2.8 GB.
> How can I get rid of this tempdb?
> regards,
> atx
>
|||Look at trying "Method 1 to Shrink Tempdb" in the KB article below
http://support.microsoft.com/?id=307487
"atx" <atx@.xerox.com> wrote in message
news:uBQHOwC9FHA.2832@.TK2MSFTNGP14.phx.gbl...
> Hi,
> after a huge ODBC operation to read some data from a DB table, my tempdb
> is now 2.8 GB and I can not delete tempdb. Query analyzer does not work,
> right mouse click and delete does not work, dbcc checks does not work. I
> even stopped the service and started it again, but tempdb did not
> regenerated and still 2.8 GB. I manually deleted the tempdb.mdf file and
> log file but after I started the MSSQLSERVER service, they are all
> recovered as 2.8 GB.
> How can I get rid of this tempdb?
> regards,
> atx

can not delete pagefile.sys

Hi Guys,

I don't know if this is the right place to post this issue. Please help.

I have done some tweaking and reconfigured pagefile of our server.

Previouly, the pagefile was distributed to different drives (6 drives in all).

Now, I stored the pagefile on a single drive. After doing this, I noticed that the pagefile from the other drives still exist and I can't delete them.

I want to delete this file on the other drives so I can free up some space but I couldn't. Please Help.

Thanks,

Larry

Larry,

Go into the pagefile settings and click on each drive that you don't want the pagefile on. On each drive, click the radio button that says No Pagefile and then click the Set button. Do this on each drive where you don't want a pagefile. After you're done reboot your server and then see if the pagefiles are still there, they shouldn't be.

Can not delete FT catalog, why?

I have a large db and some large FT catalogs.
When I try to delete ftcatalog I see msg: "Server can not obtain LOCKS ..."
Why this message?
Its my test-server and no any users.
SQL Server used AWE and 5G from 8G.
Thanks.
Do you have a population currently running or are you using change tracking?
If so you will have to stop the population or disable change tracking to
remove the catalogs. Normally you will not have to do this, but underload
you may experience locking while trying to do this.
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
"Oleg Cherkasenko" <oleg@.opel.com.ua> wrote in message
news:edv%23rO0GFHA.3244@.TK2MSFTNGP09.phx.gbl...
> I have a large db and some large FT catalogs.
> When I try to delete ftcatalog I see msg: "Server can not obtain LOCKS
...."
> Why this message?
> Its my test-server and no any users.
> SQL Server used AWE and 5G from 8G.
>
> Thanks.
>
sql

Can not delete database. Gives Error 21776. Suggestions please?

Hi,
I just installed MS SQL 2000 SE then SP3 on Windows 2000 Server. I
then backed-up a database from another MS SQL 2000 server and restored
it. After the restore, I noticed not all my tables were there, so I
decided to just do an import instead of restore. So I'm now trying to
delete the restored table, but I get the following error:
Microsoft SQL-DMO:
Error 21776:[SQL-DMO]The Name 'Accounting Database' was not found in
the Database collection. If the name is a qualified name, use [] to
seperate various parts of the name, and try again.
This is causing all sorts of problems now. Is there a script or
something I can run that will remove this database 100%? I want to
start clean, but use this same DB name. If I have to do this
manually, what tables in what database do I need to modify? And why
does Microsoft make this so difficult?
Thanks,
Alex.Hi Alex,
From Query Analyzer , Login as SA and execute the below command ,
sp_helpdb
From the result set , cut and past the Accounting database name along with
the below command,
drop database <paste the dbname>
Thanks
Hari
MCDBA
"Alex" <alex@.totallynerd.com> wrote in message
news:2ba4b4eb.0310270740.3be18d34@.posting.google.com...
> Hi,
> I just installed MS SQL 2000 SE then SP3 on Windows 2000 Server. I
> then backed-up a database from another MS SQL 2000 server and restored
> it. After the restore, I noticed not all my tables were there, so I
> decided to just do an import instead of restore. So I'm now trying to
> delete the restored table, but I get the following error:
> Microsoft SQL-DMO:
> Error 21776:[SQL-DMO]The Name 'Accounting Database' was not found in
> the Database collection. If the name is a qualified name, use [] to
> seperate various parts of the name, and try again.
> This is causing all sorts of problems now. Is there a script or
> something I can run that will remove this database 100%? I want to
> start clean, but use this same DB name. If I have to do this
> manually, what tables in what database do I need to modify? And why
> does Microsoft make this so difficult?
> Thanks,
> Alex.|||drop database [Accounting Database]
You need to wrap the database name in brackets since it has a space in the
name. Or you can right click on the database in QA and drop it, provided
that there are no connections.
-Morgan
"Alex" <alex@.totallynerd.com> wrote in message
news:2ba4b4eb.0310270740.3be18d34@.posting.google.com...
> Hi,
> I just installed MS SQL 2000 SE then SP3 on Windows 2000 Server. I
> then backed-up a database from another MS SQL 2000 server and restored
> it. After the restore, I noticed not all my tables were there, so I
> decided to just do an import instead of restore. So I'm now trying to
> delete the restored table, but I get the following error:
> Microsoft SQL-DMO:
> Error 21776:[SQL-DMO]The Name 'Accounting Database' was not found in
> the Database collection. If the name is a qualified name, use [] to
> seperate various parts of the name, and try again.
> This is causing all sorts of problems now. Is there a script or
> something I can run that will remove this database 100%? I want to
> start clean, but use this same DB name. If I have to do this
> manually, what tables in what database do I need to modify? And why
> does Microsoft make this so difficult?
> Thanks,
> Alex.|||Hi ,
Just issue this command ,
drop database [Accounting Database]
Thanks
Hari
MCDBA
"Morgan" <mfears@.spamcop.net> wrote in message
news:#5IV1sLnDHA.1764@.tk2msftngp13.phx.gbl...
> drop database [Accounting Database]
> You need to wrap the database name in brackets since it has a space in the
> name. Or you can right click on the database in QA and drop it, provided
> that there are no connections.
> -Morgan
> "Alex" <alex@.totallynerd.com> wrote in message
> news:2ba4b4eb.0310270740.3be18d34@.posting.google.com...
> > Hi,
> >
> > I just installed MS SQL 2000 SE then SP3 on Windows 2000 Server. I
> > then backed-up a database from another MS SQL 2000 server and restored
> > it. After the restore, I noticed not all my tables were there, so I
> > decided to just do an import instead of restore. So I'm now trying to
> > delete the restored table, but I get the following error:
> >
> > Microsoft SQL-DMO:
> > Error 21776:[SQL-DMO]The Name 'Accounting Database' was not found in
> > the Database collection. If the name is a qualified name, use [] to
> > seperate various parts of the name, and try again.
> >
> > This is causing all sorts of problems now. Is there a script or
> > something I can run that will remove this database 100%? I want to
> > start clean, but use this same DB name. If I have to do this
> > manually, what tables in what database do I need to modify? And why
> > does Microsoft make this so difficult?
> >
> > Thanks,
> >
> > Alex.
>

Can not delete data from View

I have a view is made from three tables by UNION ALL.
When I tried to delete some records by using 'Delete from My_New_View_2004..
.', I get error:
Server: Msg 4440, Level 16, State 9, Line 1
UNION ALL view is not updatable
Why and how to fix this problem so I can delete data from view?
Thanks for help.
-GeorgeA view containing a UNION is not updateable unless it can meet the criteria
for a Partitioned View. See the Partitioned Views topic in Books Online for
details.
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:sICdnfm2y_xb7_7dRVn-ig@.giganews.com...
> A view containing a UNION is not updateable unless it can meet the
criteria
> for a Partitioned View. See the Partitioned Views topic in Books Online
for
> details.
Alternatively, if you are running SQL 2000 you can have an INSTEAD OF DELETE
trigger that deletes from the underlying tables
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.614 / Virus Database: 393 - Release Date: 05/03/2004

Can not delete Agent Jobs

Seems that someone deleted the maintenace jobs and now I can not delete any
of the jobs associated with them. I get error:
TITLE: Microsoft SQL Server Management Studio
--
Attempt to retrieve data for object failed for Server 'IMPULSE-BPB'.
(Microsoft.SqlServer.Smo)
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attempt+to+retrieve+data+for+object+Server&LinkId=20476
--
ADDITIONAL INFORMATION:
The Job 'BP WSS Database Job.DB Backup Job for DB Maintenance Plan 'BP WSS
Database Job'' does not exist on the server. (Microsoft.SqlServer.Smo)
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.ObjectDoesNotExist&LinkId=20476
--
BUTTONS:
OK
--
Any Idea's?
ThanksClick on the Jobs node in SSMS and click Refresh.
You know, sometimes items are not refreshed automatically in SSMS (and EM of
course) and you see that Maintenance tasks are deleted but jobs are still
there but they are not actually. So you are trying to delete the deleted
stuff. I've seen this situation lots of times and I thought this could be
your situation.
--
Ekrem Ã?nsoy
"RickSpicknall" <RickSpicknall@.discussions.microsoft.com> wrote in message
news:8A0F2AF9-A5EC-4052-A162-99C94ABD5C96@.microsoft.com...
> Seems that someone deleted the maintenace jobs and now I can not delete
> any
> of the jobs associated with them. I get error:
> TITLE: Microsoft SQL Server Management Studio
> --
> Attempt to retrieve data for object failed for Server 'IMPULSE-BPB'.
> (Microsoft.SqlServer.Smo)
> For help, click:
> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attempt+to+retrieve+data+for+object+Server&LinkId=20476
> --
> ADDITIONAL INFORMATION:
> The Job 'BP WSS Database Job.DB Backup Job for DB Maintenance Plan 'BP WSS
> Database Job'' does not exist on the server. (Microsoft.SqlServer.Smo)
> For help, click:
> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.ObjectDoesNotExist&LinkId=20476
> --
> BUTTONS:
> OK
> --
> Any Idea's?
> Thanks|||Geez whiz, Now I feel dumb. Thanks However I still have one out there I
can not get rid of. I get error:
The DELETE statement conflicted with the REFERENCE constraint
"FK_subplan_job_id". The conflict occurred in database "msdb", table
"dbo.sysmaintplan_subplans", column 'job_id'.
The statement has been terminated. (.Net SqlClient Data Provider)
"Ekrem Ã?nsoy" wrote:
> Click on the Jobs node in SSMS and click Refresh.
> You know, sometimes items are not refreshed automatically in SSMS (and EM of
> course) and you see that Maintenance tasks are deleted but jobs are still
> there but they are not actually. So you are trying to delete the deleted
> stuff. I've seen this situation lots of times and I thought this could be
> your situation.
> --
> Ekrem Ã?nsoy
>
> "RickSpicknall" <RickSpicknall@.discussions.microsoft.com> wrote in message
> news:8A0F2AF9-A5EC-4052-A162-99C94ABD5C96@.microsoft.com...
> > Seems that someone deleted the maintenace jobs and now I can not delete
> > any
> > of the jobs associated with them. I get error:
> >
> > TITLE: Microsoft SQL Server Management Studio
> > --
> >
> > Attempt to retrieve data for object failed for Server 'IMPULSE-BPB'.
> > (Microsoft.SqlServer.Smo)
> >
> > For help, click:
> > http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attempt+to+retrieve+data+for+object+Server&LinkId=20476
> >
> > --
> > ADDITIONAL INFORMATION:
> >
> > The Job 'BP WSS Database Job.DB Backup Job for DB Maintenance Plan 'BP WSS
> > Database Job'' does not exist on the server. (Microsoft.SqlServer.Smo)
> >
> > For help, click:
> > http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.ObjectDoesNotExist&LinkId=20476
> >
> > --
> > BUTTONS:
> >
> > OK
> > --
> >
> > Any Idea's?
> > Thanks
>|||Hi Rick!
There is a Foreign Key constraint there.
Try this:
USE MSDB
SELECT * FROM dbo.sysmaintplan_subplans
Find your plan_id and then
DELETE FROM dbo.sysmaintplan_log WHERE plan_id = 'plan_id '
EXECUTE sp_delete_maintenance_plan 'plan_id '
Ekrem Ã?nsoy
"RickSpicknall" <RickSpicknall@.discussions.microsoft.com> wrote in message
news:F7029524-ACA4-4F62-85F7-18D5EB924F46@.microsoft.com...
> Geez whiz, Now I feel dumb. Thanks However I still have one out there I
> can not get rid of. I get error:
> The DELETE statement conflicted with the REFERENCE constraint
> "FK_subplan_job_id". The conflict occurred in database "msdb", table
> "dbo.sysmaintplan_subplans", column 'job_id'.
> The statement has been terminated. (.Net SqlClient Data Provider)
> "Ekrem Ã?nsoy" wrote:
>> Click on the Jobs node in SSMS and click Refresh.
>> You know, sometimes items are not refreshed automatically in SSMS (and EM
>> of
>> course) and you see that Maintenance tasks are deleted but jobs are still
>> there but they are not actually. So you are trying to delete the deleted
>> stuff. I've seen this situation lots of times and I thought this could be
>> your situation.
>> --
>> Ekrem Ã?nsoy
>>
>> "RickSpicknall" <RickSpicknall@.discussions.microsoft.com> wrote in
>> message
>> news:8A0F2AF9-A5EC-4052-A162-99C94ABD5C96@.microsoft.com...
>> > Seems that someone deleted the maintenace jobs and now I can not delete
>> > any
>> > of the jobs associated with them. I get error:
>> >
>> > TITLE: Microsoft SQL Server Management Studio
>> > --
>> >
>> > Attempt to retrieve data for object failed for Server 'IMPULSE-BPB'.
>> > (Microsoft.SqlServer.Smo)
>> >
>> > For help, click:
>> > http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attempt+to+retrieve+data+for+object+Server&LinkId=20476
>> >
>> > --
>> > ADDITIONAL INFORMATION:
>> >
>> > The Job 'BP WSS Database Job.DB Backup Job for DB Maintenance Plan 'BP
>> > WSS
>> > Database Job'' does not exist on the server. (Microsoft.SqlServer.Smo)
>> >
>> > For help, click:
>> > http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.ObjectDoesNotExist&LinkId=20476
>> >
>> > --
>> > BUTTONS:
>> >
>> > OK
>> > --
>> >
>> > Any Idea's?
>> > Thanks

Can I use this thing to import data from a table into a view.

Hi all,
I need to import data from a source table(table is in Sql) into a view. I
need to insert, delete or update the view to make sure all rows in the view
have the same data as the source table. Can you please give me how to design
this in a Integration Services project?
Thanks.Views contain no data -- there is nothing to insert, delete, or update.
Simply create the view, and every time you access it, the base tables
involved in the view will be queried. Therefore, the view will always have
the same data as the base tables.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"someone" <nospam@.stopspam.com> wrote in message
news:OusidbIcGHA.3840@.TK2MSFTNGP04.phx.gbl...
> Hi all,
> I need to import data from a source table(table is in Sql) into a view.
> I need to insert, delete or update the view to make sure all rows in the
> view have the same data as the source table. Can you please give me how to
> design this in a Integration Services project?
> Thanks.
>

Can I use this thing to import data from a table into a view.

Hi all,
I need to import data from a source table(table is in Sql) into a view. I
need to insert, delete or update the view to make sure all rows in the view
have the same data as the source table. Can you please give me how to design
this in a Integration Services project?
Thanks.Views contain no data -- there is nothing to insert, delete, or update.
Simply create the view, and every time you access it, the base tables
involved in the view will be queried. Therefore, the view will always have
the same data as the base tables.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"someone" <nospam@.stopspam.com> wrote in message
news:OusidbIcGHA.3840@.TK2MSFTNGP04.phx.gbl...
> Hi all,
> I need to import data from a source table(table is in Sql) into a view.
> I need to insert, delete or update the view to make sure all rows in the
> view have the same data as the source table. Can you please give me how to
> design this in a Integration Services project?
> Thanks.
>

Can I rollback a delete?

Hi!
I made a wrong delete in my database.
Can I recover the data of that table??
The query was:
delete from table
I should write something like this
delete from table where column = 87
ThanksSure..

BEGIN TRAN

DELETE FROM myTable99

ROLLBACK TRAN

...you didn't create a transaction?

Time to restore the DB from a backup to a new db and copy the data from there...

No back up?

How about printed listings and start typing?|||No printed listings ...
Better think of some excuse fast.

Any way have heard of a third party tool that might help .. am not sure .. something called log explorer from lumigent. Havent tried it out yet.|||Thank you!...Im now searching the backup information...Now the question is if I can restore only that table?

How?

Thanks

JP
Originally posted by Enigma
No printed listings ...
Better think of some excuse fast.

Any way have heard of a third party tool that might help .. am not sure .. something called log explorer from lumigent. Havent tried it out yet.|||No,

You can only restore the whole database...

DON"T overlay the existsing one, create a new DB (use attach), and then copy back the lost data...|||Yep, I have just started to try out Lumigent Log Explorer and it sure can recover deletes and other changes from the log.

Haven't done anything more than some small trials on it yet, but it don't seem to hard to use.

http://www.lumigent.com/products/le_sql/le_sql.htm|||Restore on to a new db, bcp out the table you want and bcp in into the old db

You might wanna check for dependencies of the table ..|||What's the cost? Is there a free demo?

Heard about it..never had a need...|||Havent had the need to use the log explorer till now ... my backups work just fine ... and i just bcp out the entire database tables once a day to take care of such cases. Takes care of such cases where data is accidently deleted or updated very fast.

Can I manually delete .bak files?

I'm running a SQL 2000 server and have a maintenance plan that does complete
backups daily, with a 10 day retention on files.
For some reason, I have .bak files that go back as far as January on several
databases, which is an issue in and of itself.
My question is, are all of the .bak files independent of each other and can
I safely delete older ones without affecting the more recent backups?
Thanks you,
Larry
Are we talking about database or log backups?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Larry" <Larry@.discussions.microsoft.com> wrote in message
news:C86E6A38-8449-4ADB-8F5D-0559B676AB16@.microsoft.com...
> I'm running a SQL 2000 server and have a maintenance plan that does complete
> backups daily, with a 10 day retention on files.
> For some reason, I have .bak files that go back as far as January on several
> databases, which is an issue in and of itself.
> My question is, are all of the .bak files independent of each other and can
> I safely delete older ones without affecting the more recent backups?
> Thanks you,
> Larry
|||Database, not transaction.
"Tibor Karaszi" wrote:

> Are we talking about database or log backups?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Larry" <Larry@.discussions.microsoft.com> wrote in message
> news:C86E6A38-8449-4ADB-8F5D-0559B676AB16@.microsoft.com...
>
|||A more recent db backup is not dependent on an earlier db backup. However if you need to do point in
time restore to some point between the earlier and the more recent db backup and you also have log
backups covering that period, then you want to save the earlier db backup.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Larry" <Larry@.discussions.microsoft.com> wrote in message
news:D06E7454-66E6-4153-8EFA-3B7A0FF7C62E@.microsoft.com...[vbcol=seagreen]
> Database, not transaction.
> "Tibor Karaszi" wrote:

Can I manually delete .bak files?

I'm running a SQL 2000 server and have a maintenance plan that does complete
backups daily, with a 10 day retention on files.
For some reason, I have .bak files that go back as far as January on several
databases, which is an issue in and of itself.
My question is, are all of the .bak files independent of each other and can
I safely delete older ones without affecting the more recent backups?
Thanks you,
LarryAre we talking about database or log backups?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Larry" <Larry@.discussions.microsoft.com> wrote in message
news:C86E6A38-8449-4ADB-8F5D-0559B676AB16@.microsoft.com...
> I'm running a SQL 2000 server and have a maintenance plan that does complete
> backups daily, with a 10 day retention on files.
> For some reason, I have .bak files that go back as far as January on several
> databases, which is an issue in and of itself.
> My question is, are all of the .bak files independent of each other and can
> I safely delete older ones without affecting the more recent backups?
> Thanks you,
> Larry|||Database, not transaction.
"Tibor Karaszi" wrote:
> Are we talking about database or log backups?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Larry" <Larry@.discussions.microsoft.com> wrote in message
> news:C86E6A38-8449-4ADB-8F5D-0559B676AB16@.microsoft.com...
> > I'm running a SQL 2000 server and have a maintenance plan that does complete
> > backups daily, with a 10 day retention on files.
> >
> > For some reason, I have .bak files that go back as far as January on several
> > databases, which is an issue in and of itself.
> >
> > My question is, are all of the .bak files independent of each other and can
> > I safely delete older ones without affecting the more recent backups?
> >
> > Thanks you,
> >
> > Larry
>|||A more recent db backup is not dependent on an earlier db backup. However if you need to do point in
time restore to some point between the earlier and the more recent db backup and you also have log
backups covering that period, then you want to save the earlier db backup.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Larry" <Larry@.discussions.microsoft.com> wrote in message
news:D06E7454-66E6-4153-8EFA-3B7A0FF7C62E@.microsoft.com...
> Database, not transaction.
> "Tibor Karaszi" wrote:
>> Are we talking about database or log backups?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Larry" <Larry@.discussions.microsoft.com> wrote in message
>> news:C86E6A38-8449-4ADB-8F5D-0559B676AB16@.microsoft.com...
>> > I'm running a SQL 2000 server and have a maintenance plan that does complete
>> > backups daily, with a 10 day retention on files.
>> >
>> > For some reason, I have .bak files that go back as far as January on several
>> > databases, which is an issue in and of itself.
>> >
>> > My question is, are all of the .bak files independent of each other and can
>> > I safely delete older ones without affecting the more recent backups?
>> >
>> > Thanks you,
>> >
>> > Larry
>>

Can I manually delete .bak files?

I'm running a SQL 2000 server and have a maintenance plan that does complete
backups daily, with a 10 day retention on files.
For some reason, I have .bak files that go back as far as January on several
databases, which is an issue in and of itself.
My question is, are all of the .bak files independent of each other and can
I safely delete older ones without affecting the more recent backups?
Thanks you,
LarryAre we talking about database or log backups?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Larry" <Larry@.discussions.microsoft.com> wrote in message
news:C86E6A38-8449-4ADB-8F5D-0559B676AB16@.microsoft.com...
> I'm running a SQL 2000 server and have a maintenance plan that does comple
te
> backups daily, with a 10 day retention on files.
> For some reason, I have .bak files that go back as far as January on sever
al
> databases, which is an issue in and of itself.
> My question is, are all of the .bak files independent of each other and ca
n
> I safely delete older ones without affecting the more recent backups?
> Thanks you,
> Larry|||Database, not transaction.
"Tibor Karaszi" wrote:

> Are we talking about database or log backups?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Larry" <Larry@.discussions.microsoft.com> wrote in message
> news:C86E6A38-8449-4ADB-8F5D-0559B676AB16@.microsoft.com...
>|||A more recent db backup is not dependent on an earlier db backup. However if
you need to do point in
time restore to some point between the earlier and the more recent db backup
and you also have log
backups covering that period, then you want to save the earlier db backup.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Larry" <Larry@.discussions.microsoft.com> wrote in message
news:D06E7454-66E6-4153-8EFA-3B7A0FF7C62E@.microsoft.com...[vbcol=seagreen]
> Database, not transaction.
> "Tibor Karaszi" wrote:
>