Drop A Database When The Name Is Supplied As A Varible
Jan 17, 2008
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
Hi,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
I'm trying to create a page in a web browser where users can type the name of a company in a text box and to get details of the company. I can hard code like the below example and it brings up the details i'm looking for.
The problem is when I try to replicated this with a stored procedure it doesn't work.
Thanks for any help in advance,
Code:
select * from company where companyname like 'companyx%'
Hi, I have a DTS which has a connection, 3 destination text files and aProcess execution job which runs a simple batch file to concatenatethis 3 files into a 4th text file "Endeud.txt". I added an activeXcommand Job to input a date which is saved in a global variable. Ineed to add this date at the end of the Endeud.txt file (The date wouldbe the last line of Endeud.txt. How can I do this?
In Execute SQL Task, I'm facing following error when Result Set assigns value to the global variable.
SSIS package "Package.dtsx" starting.Error: 0xC002F309 at Execute SQL Task, Execute SQL Task: An error occurred while assigning a value to variable "IDCount": "Value does not fall within the expected range.".Task failed: Execute SQL TaskWarning: 0x80019002 at Package: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.SSIS package "Package.dtsx" finished: Failure.
My SQL statement is "Select count(*) as AliasNm from <TableName>" and my Result Set type is Single Row.
I mentioned in the Result Set's Result Name as AliasNm.
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
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!
Hello 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
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.
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!
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.
Hi 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
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 ?
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.
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?
I 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.
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.
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.
Hello,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
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?
// 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);
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
