.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Detach database with drop connections

Posted By:      Posted Date: April 10, 2011    Points: 0   Category :


What is the significance of dropping connections (by chosing drop connection option) while detaching the database? Can it create problems if I don't select this option?



View Complete Post

More Related Resource Links

Detach/Attach database question

I recently restored a database from SQL7 to SQL2005.  When restoring the database I needed to create a c:\data\data folder or else it would not restore the mdf and ldf files. I would like to move these files to a new location.  Copied the the two files to a new folder, C:\database.  Using the sql studio manager, preformed a task to detach the database, then attach the database with the new location. This seemed to work, I am able to access my application that uses this database.  However, any changes I make to the program, are not writing changes back to the mdf file.  When I open the program, these changes are still in place, but the mdf file should be updated, correct?  When the database files were stored in the c:\data\data folder, any little change I made to the program changed the modified time for the mdf file. Is there something missing or where could these changes be written to?

how to in Insert database NULL if drop down list item is not selected ?

hi,   on my aspx I have the below DDL   <asp:DropDownList ID="ColorDDL" runat="server" DataSourceID="ColorObj"                                                 DataTextField="Size" DataValueField="BustId" Width="150px"                                                 AppendDataBoundItems="True">                                                    <asp:ListItem Value="-1">(Optional)</asp:ListItem>                             &n

Recovery of Database Disabled SQL Agent, Cannot Detach Database, Etc... Please Help

I am a beginner SQL user, as the company laid off the IT guy and I am stuck with this.  So I really need some help on this one.  Any ideas would be appreciated. I was doing a merge join command to put data from one database to another.  These are 60GB databases with valuable data.  Halfway through I was told by client to just stop the query.  I did this but it left the good database with a large 80GB log file. When I rebooted the machine the recovery of this database took 5-10 minutes...  Thus the SQL Agent disabled itself waiting for recovery.  After recovery was 100% complete with no errors SQL Agent will not restart.  I can do a force turn on using a "1" statement, but the SQL Agent log file says it is still waiting for recovery. In any event, I cannot detach the database because it says 1 user connected.  I cannot backup, drop, etc...  I saw in one forum a guy said to turn service off, copy database to a new location, turn service back on, delete the database, attach copied database...  But I am a little worried about deleting. Once again - I am a SQL newbie and really need a pointer on this one.  I need to free this database and get a it back to a normal sized log file. Advice???

Difference between truncate and drop table on database fragmentation


I have a group of developers that are creating stored procedures, in them they are dropping a table and re-creating the table and then continuing with the processing.  My question is if there is any difference between the effect of dropping a table and truncating the table on database fragmentatiom.




Add Database through App_Data Error- Connections to SQL Server Files (*.mdf) require SQL Express


I am trying to add a new database via the App_Data Folder and I receive an error described below.

I had attached an mdf file to try to use it in SQL Server 2008 Developer and Visual Studio 2010, which was is included in a book's lesson.  Whenever I try to add a database within the App_Data Folder I get an error.

I can create a db by attaching an mdf via SQL Server Mgmt Studio, it connects fine, and I can see tables.

Mgmt Studio- Right Click Databases, Attach, Add, then once mdf is located:

The mdf file location, Database Name, Attach As, -- are all the same:


Do I need to change the Database Name?

Visual Studio- Server Explorer connects fine to mdf OR regular Database, I can see the tables.

However when I attempt to add ANY TYPE OF SQL SERVER DATABASE through the App_Data folder I receive this error "Connections to SQL Server Files (*.mdf) require SQL Server Express 2008 to function properly.  Please verify the installation of the component or download from the URl."

I tested this for both a normal dbo (non mdf) and the mdf.dbo I attached before, with the same error!

Im running XP SP3 w/ Visual Studio 2010 and SQL Server 2008 R2 Developer.  I had previously removed SQL Server Express.

Drop Database



I have the following:

if exists (select name from sys.databases where name = @DatabaseName)
  alter database @DatabaseName set single_user with rollback immediate
  drop database @DatabaseName

I get an error on the second go:

Incorrect sintax near go.

Basically I am checking if the database exists. If yes then close all connections to it and drop it.

What am I missing?

Am I closing the connections correctly?



fx Controls instead of Drop Down Lists for Lookup fields in InfoPath Secondary Data Connections


I have a secondary data connection with a lookup field into another list. To display the fields text value instead of the index, I am using a drop down list which gets its choices from the linked list. This information is read-only so I disabled the control via formatting rule.

A calculated value control might be better suited, but my XPath skills are not very "developed". Has anyone done this before? Taken the index from the bound field and extracted the text value from a second data connection? Thx


Does ALTER DATABASE SET RESTRICTED_USER block until connections are killed?


Hi, we have a database restore script that runs an ALTER DATABASE [Database] SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE command.  The next statement is a WAITFOR DELAY with 30 seconds.  This is to ensure any non compliant connections are killed before continuing.

However, I'm wondering if the WAITFOR is actually necessary?  Would the ALTER DATABASE command not block until all connections have been killed?


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


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.

Can not drop a database in SQL 2005


Hello all,

SQL 2005 Standard Edition X64 version 9.00.3042.00

I have an interesting problem here that I can not seem to figure out.  I am trying to drop a user database and it keeps failing.

First, I started off using a simple DROP statement:

DROP DATABASE database_name

I received an error saying it was in use.  No problem, I figured.  So I then tried to execute this:


No luck here either.  I received an error message saying a lock could not be placed on the database.  Try again later.  Hmm.  For kicks, I tried the detach database stored procedure:

exec sp_detach_db @dbname = 'database_name', @skipchecks = 'true', @keepfulltextindexfile = 'false'

No luck here either.  I got curious and tried to see what process was using the database:

SELECT * FROM sysprocesses WHERE dbid = DB_ID('database_name')

I got no results.  So apparently nothing is using it.

For added fun, I checked the database properties by going into SSMS and right clicking the database and selecting Properties.  This is where it gets interesting.  I received an error dialog that states:

Cannot show requested dialog.  Th

A correct way to drop MS SQL database


Hi all. I need to programmatically remove the MS SQL database which used in my application. Currently I'm using the following code for this:

SqlConnection conn = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=<PATH_TO_MDF_FILE>;Integrated Security=True;Pooling = False;User Instance=True;MultipleActiveResultSets=True");<br/>
SqlCommand alterCommand = new SqlCommand("ALTER DATABASE \"<PATH_TO_MDF_FILE>\" SET SINGLE_USER WITH ROLLBACK IMMEDIATE", conn);<br/>
SqlCommand dropCommand = new SqlCommand("DROP DATABASE \"<PATH_TO_MDF_FILE>\";", conn);<br/>
//Not sure that pools clearing actually needed<br/>
//"Cannot drop database "<PATH_TO_MDF_FILE>" because it is currently in use." exception is raised here<br/>

Cannot drop filegroup from Database in case when the file, asociated with it, is corrupted

I have the following problem:
Have a DB with 4 filegroups with patition function and schema and one table attached to the schema. After restarting the SQL Server one of the ndf files become corrupted. I don't have backup. I set the filegroup offline and set the DB to multiuser, but I cannot remove the filegroup or file attached to the filegroup or rename the filegroup. It is not recomended to transfer the data to the new table with new schema. Does anybady has an idea how to solve this issue?

can we attach database again after drop it

i want to know that we are able to attached database again after drop it.please clear my point....

generic use of database connections, commands, datareaders


I have an app that is reading and writing from databases that are structurally identical but are from different systems, primarily SQL Server and Access 2010.  When using the respective .Net objects such as connections, commands, datareaders, the code seems to be often the same (except for transactions - Access doesn't do transactions?).  But the objects are different - sqlconnection versus oledbconnection, etc.

Is there a standard way of writing data access code that works regardless of the database type?

Database mirroring trouble configuring 2 connections


I am trying to set up the following. 


I have configured the sql server side no problem.  i am having issue getting the application that uses the database to connect to the mirrored server in the event of  failure.

The application that utalises the database connects through the data sources (ODBC), System DSN, SQL driver.  I see no option in the configuration for the addition of a 2nd IP.  Many of the technical references i have read refer to the data source connection string, i cannot find this in the registry however.  Please help!

I am used to a 3rd party mirroring software where you connect to a logical IP address which then routes through to whichever database is running.

Saving database connections


I am trying to save connections in management studio so I don't have to re-enter them every time.  I have to connect to multiple servers and I just want to be able to open management studio and have them already in there.  Is there anyway to do this?


Multiple database support with Entity Framework

One of the features introduced in Entity Framework is being database independent. Which mean each database provider can support Entity Framework by implementing its provider.

This feature allows you build applications independent from the underplaying database provider. In this post I'm going to show how to build an application that support multiple databases using same conceptual model made by Entity Framework.
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend