Drop Database Fails Because Of Aspnet_wp.exe
Jun 22, 2006Hey everyone.
View 2 RepliesHey everyone.
View 2 RepliesHi,I found this SQL in the news group to drop indexs in a table. I need ascript that will drop all indexes in all user tables of a givendatabase:DECLARE @indexName NVARCHAR(128)DECLARE @dropIndexSql NVARCHAR(4000)DECLARE tableIndexes CURSOR FORSELECT name FROM sysindexesWHERE id = OBJECT_ID(N'F_BI_Registration_Tracking_Summary')AND indid 0AND indid < 255AND INDEXPROPERTY(id, name, 'IsStatistics') = 0OPEN tableIndexesFETCH NEXT FROM tableIndexes INTO @indexNameWHILE @@fetch_status = 0BEGINSET @dropIndexSql = N' DROP INDEXF_BI_Registration_Tracking_Summary.' + @indexNameEXEC sp_executesql @dropIndexSqlFETCH NEXT FROM tableIndexes INTO @indexNameENDCLOSE tableIndexesDEALLOCATE tableIndexesTIARob
View 2 Replies View RelatedI have an application which is providing printing facility using a custom hardware interface device.
Whenever a print is required user punches in his ID number into the hardware device, and hence an underlying application gets the event and it calls a managed DLL, which inturn calls the Reporting services webservice on the same server. Problem starts after some days (typically after 2 -3 days), when the server response becomes slow and the aspnet_wp.exe restarts every now and then and after this sequence of happening, finally it crashes with some event in application in event log which reads:
The server was unable to allocate from the system nonpaged
pool because the pool was empty.
Also many times the applications log file reads :
There is an error in XML document (1, 948).
I think this error starts when the aspnet_wp.exe is restarting, but I am not sure.
The server operating system is Windows 2000 SP-4, MS SQL Server 2000 Standard Edition SP-4, MS Reporting Services 2000 Standard SP-2 with .Net Framework v1.1
The Server is running many applications apart from the ones listed above and there is a moderate usage of TCP/IP sockets for communicating with certain hardware devices.
Please help me out!
Does the aspnet_wp.exe get considered in memory limitations for SQL Express? (1GB limit) I'm asking because I run the Reporting Services on the same box (this is required with SQLE). When I run reports, it seems much of the work is in the aspnet_wp.exe process, so I'm trying to determine if running reports will get considered in the 1GB memory limit of SQLE. I know, however, that the ReportServer database and the ReportServerTempDB database WILL get included in the 1GB limit, but am trying to figure out if the aspnet_wp.exe process gets considered as well when it is running reports.
Thanks,
Michael
I am trying to delete a database that is labeled suspect. Is there a way to force a drop of a database? It is shows it is marked inaccessible when trying to delete.
Thanks, Steve
anyone know how to drop my databse on sql pleasee
charles
I am trying to drop a database, but keep getting the following error."cannot drop database 'blah' because it is currently being used forreplication".This db is not currently being replicated, but once was. It is thesubscriber side of an old replication pair.Can anyone tell what I have to do to make this go away?Thanks,TGru*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies View RelatedHello everyone!I'm from El Salvador and i have a problem, i have a sybase systemrunning on windows 2000 server, probably a programmer drop adatabase...but i dont know who did it?, is it possible to know who dropthe database?, if the answer is yes... plese help me, because we need tosolve this security problem in our company.Sybase saves all the TSQL and transaction somewhere?,Saludos....--Posted via http://dbforums.com
View 1 Replies View Relatedwhat is the sql query to drop all tables in a database in sql server 2000
View 5 Replies View RelatedHi everybody,
I would like to know if there would be any special way to force drop a database from an ASP.NET page.
When I try to do it in the normal way, it gives me an exception like: Cannot drop the database 'xxxxxxx' because it is currently in use. I'would have to wait until there is a timeout.
In fact that database can be accessed from another pages, but I want to know if I'd be able to force drop database even when another pages are using it.
Thanks in advance
I'd like to write a stored procedure to drop all objects in a SQL Server 2000 database owned by a particular uid. Originally I'd hoped to use these two stored proc built-ins for the task: sp_MScheck_uid_owns_anything (to get a list of all objects owned by a uid) and sp_MSdrop_object (to drop the objects). I've run into a few problems along the way:
1. If I run this command
EXEC sp_MScheck_uid_owns_anything 5
I get this weird error message:
"The user owns objects in the database and cannot be dropped."
Not sure why that is since I'm just trying to list the objects, not drop them.
2. I tried running a simple query to get the objects from the system table instead:
SELECT * from [dbo].[sysobjects] where uid = 5
This returns a resultSet as you'd expect. When I wrote a stored procedure to loop through these and use sp_MSdrop_object it seemed to fail whenever it encountered a foreign key object. Here is the error message:
The request for procedure 'name of foreign key' failed because 'name of foreign key' is a unknown type object.
Can anyone give advice as to the best way to go about doing this? I'd really prefer not to have to drop the entire database and recreate it. Thanks!
-Cliff
I have a list of 35 tables that need to drop the primary key index from in my database.
My problem is as follows for these 35 tables:
1. How can I get a list of all the primary keys for this subset of tables in my database
2. How can I drop just the PK for each of these tables?
I want an easy quick way to do this without having to manually do this for each of the 35 tables in my database. I dont want to do this for all tables just the subset.
Thanks
How can I drop all the indexes in all the tables in a MS 2000 SQL Server?
View 9 Replies View RelatedHi i m Uday,i want to delete ie Drop a database from specified location
for this i used this query as
DROP DATABASE AdventureWorks1,'C:Sql_dB_creatorsqlDB_creatorApp_DataAdventureWorks1'
but i gives incorrect syntax error.
So plz give the sql query to Drop a Database from specified path
Thank You
uday
Does anyone happen to have a script that will drop all database objects?
I'm looking for something generic that will work with any SQL Server 2000 database. I can write one myself, but I thought I would check here first.
Thanks in advance
John
Hi All,
I use SQL SERVER 2005 and SQL SERVER Mobile.
My SQL Server I created publication, and on mobile subscriber and did web Synchronization , no problem.
I deleted publication from under Local Publication with SQL SERVER Managment Studio.
and now I want drop database, I get this error "Drop failed for Database DBNAME ... Cannot drop database DBNAME because it is being using Replication"
How to I drop this database ?
And where registred this replication information ?
Regards,
Mahir Quluzade
I can not delete user from a database in sql2005 beta 3.
View 22 Replies View Related
Hi,
I am new to SQL Server 2005.Till now, I have been using a SP to execute DROP DATABASE command to drop databases on my existing database server.
but now i want to delete a database which is on a different SQL Server 2005 instance on a different machine. but i am not sure how to do this.
Can anyone please help me on this?
Any help would be appreciated.
Thanx in advance.
Kawal
when I do DROP DATABASE I'm getting errors because it's in use. But if I do a delete from the mgmt studio gui I have the option to close all connections as part of the delete. Is there a way to achieve the same effect from a sql script?
View 7 Replies View RelatedI was wondering if anyone may be familiar with a way to create a dropdown list that included a list of SQLServers. Basically I want to create a little application that lets a user point to an instance of sql server, similar to enterprise manager. I wanted to use C# and figured there may be some namespaces I could use that provide this functionality but was not sure.
Thanks,
Hi everybody,
I am using VB Webforms as my front end and MSDE 2000 as my back end. I am not able to drop a database after executing the following vb code on that database:
Dim vConnection As New SqlConnection(vConnectionString) Dim vAdapter As New SqlDataAdapter("SELECT party_type_code, party_type_name, party_type_imported" & _ " FROM Party_Type" & _ " ORDER BY party_type_name", vConnection) Dim vDataTable As New DataTable() vAdapter.Fill(vDataTable) vAdapter.Dispose() vConnection.Dispose()
On trying to drop the database using the SQL Command "DROP DATABASE PPCABCD2005", I get the error message:Cannot drop the database 'PPCABCD2005' because it is currently in use ...However, if I don't execute the above code, I am able to drop it. Why is it so? What is the solution to this problem?
Third question:
I'm backing up a database which has some users.
When I try to restore it to another machine which has the same users and already has an old version of the database the database cannot be accessed. I must drop the users from the database by using the stored procedures sp_dropuser <username> and then add it again to the database from the Enterprise Manager.
Why this happens??
Regards,
Manolis
Hi,
Any body please give me any reference to the C# code, how to drop a database on SQL SERVER CE.
thanx and regards
ramesh chandra
I am trying to drop a login but the system is telling me.
quote:Msg 15174, Level 16, State 1, Line 3
Login 'Mark' owns one or more database(s). Change the owner of the database(s) before dropping the login.
Question:
But how do i check to find out which objects or tables that this login is associated with.
Dallr
Hi,i have two servers. database A on server A . database A on server B(both sql server 2000 - both same d/b - server B being the standyby).if i delete databse A on server Ai have to deleted the standybycopy on server B.can anybody please guide me as to how to do this.Thanks,Shark.
View 1 Replies View RelatedHello,I would like to know is it possible to disable drop database for saor sysadmin. If saor sysadmin needs to drop the database , he/she mayhave to change status in one of the system tables (sysdatabases ?) andthen only database can be dropped . This is to avoid dropping thedatabase by mistake by sa.In books online under drop databaseSystem databases (msdb, master, model, tempdb) cannot be droppedI would like to know how this is implemented for system databases ?ThanksM A Srinivas
View 3 Replies View Related
I have created a stored procedure that will back up a database to a file then restore the database into a new db. The stored procedure accepts 2 parameters that determine the name of the old DB to be backed up and the name of the new DB to be created. The only problem now is that when you specify the name of a database that already eixists, the restore fails. I want to add an if statement that would check to see if the new db supplied exists, and if it does drop it before running the restore.
I thought the following would work but it does not
Code Block
declare @DB nvarchar (50)
set @DB = 'LDRPSsmap2k'
IF EXISTS (SELECT name FROM sys.databases WHERE name = @db)
Drop Database @db
I get When i run this
Msg 102, Level 15, State 1, Line 7
Incorrect syntax near '@db'.
How do I drop sql server mobile database at specifical time in my application in PPC?
And, How do I drop it when user input the worng password third times in my application in PPC?
Thank you very much.
Howdy Folks!
So I tried the typical IDBDataSourceAdmin:estroyDataSource() command to destroy an existing sql server 2005 express database and guess what it returned:
E_NOTIMPL (not implemented)
This leaves me in the dark on how I'm supposed to drop an existing database in SQL Native Client OLEDB. Any ideas?
My specific code is:
//Set initialization properties.
hr = m_pIDBInitialize->QueryInterface(IID_IDBProperties, (void **)&m_pIDBProperties);
CHKHR(hr, L"Failed to obtain IDBProperties interface", _ExitCreate);
// Sets properties in the Data Source and initialization property groups
hr = m_pIDBProperties->SetProperties(uiNumPropsets, dbpropset);
CHKHR(hr, L"Failed to set data source properties", _ExitCreate);
//Attempt to initialize connection
hr = m_pIDBInitialize->Initialize();
hr = m_pIDBDataSourceAdmin->DestroyDataSource();
hr = m_pIDBInitialize->Uninitialize();
Thanks!!
We are unable to drop a database user with the following error "Msg 15284, Level 16, State 1, Line 1
The database principal has granted or denied permissions to objects in the database and cannot be dropped."
On checking the database permissions we have the following permissions assigned to the user
classclass_descmajor_idminor_idgrantee_principal_idgrantor_principal_idtypepermission_namestatestate_desc
17SERVICE655360517SN SENDGGRANT
17SERVICE655370517SN SENDGGRANT
17SERVICE655380517SN SENDGGRANT
17SERVICE655390517SN SENDGGRANT
17SERVICE655400517SN SENDGGRANT
[Code] ....
How we can revoke the SEND permissions?
I have a drop down list that is populated by a stored procedure which has two int parameters (ID, STATUS). I need to default the drop down list to a value in the database that is associated to the ID & STATUS parameters.
The problem I am having is that it only works if the selected value is the first record on the drop down list. for any other records (outside the first record) from the drop down list i get "The 'SelectedIndex' and 'SelectedValue' attributes are mutually exclusive."
Here is the stored procedure I am using to populate the drop down list:
ALTER PROCEDURE [dbo].[usp_SelectName]
(
@pId Int,
@pStatus Int
)
AS
BEGIN
IF @pId = -1 --THIS VALUE IS USED AS DEFAULT (NOT POSTBACK)
[Code] ....
Here is the TABLE_NAMES that holds the data for the drop down list:
IN_ID IN_NAMESTATUS
6 New 0
4 Generic 0
3 Local 1
4 Storm 0
This is the TABLE_2 that holds the complete collected FORM data that has the selected record IN_ID from the TABLE_NAME:
ID STATUS IN_ID
111906
114406
114505
114606
114706
I restored a backup of a replication database named "distribution" to a non-replicated development instance (ss2000). Well, when I try to drop the database I am told I can not because it is used for replication. It isn't. But you now how Microsoft is . . . . So the question is: how do I drop the database without rebuilding the server, or jumping through firery hoops????
Thanks!
Scorched
Hi,
We can see current connections while trying to detach the database through SQL server 2005 Management studio.
Is there any way to drop all existing connections to an SQL server 2005 database with out detaching it.
Thanks