| Groups | Blog | Home |
sql server msde : Open Connections - Can't Drop
I am using SQL Express and Visual Studion 2003. I am trying to DROP DATABASE Customers using a Stored Procedure and also using ADO.NET. Query: IF EXISTS (SELECT name FROM sys.databases WHERE name = Customers) DROP DATABASE [Customers] ConnectionString: RemoveConnectionString = "Server = MyComputer\SQLExpress;UID=sa; Password=MyPassword;Initial Catalog=master" Everytime I call the procedure or ExecuteNonQuery I get: Cannot Drop Database 'Customers' because database is currently in use. by sqlclient.Data.SqlClient.SqlCommand.ExecuteNonQuery() I have no problems Dropping the Tables. Thanks, Chuck
Where is this stored procedure. If it's in the database then you won't be able to use it to drop the database while you are in the same database. Are you trying to run the query directly after connecting useing the connection string just given. Or is there any "use Customer" before execute ExecuteNonQuery(). [quoted text, click to view] "Charles A. Lackman" <Charles@CreateItSoftware.net> wrote in message news:epQ433v9GHA.1172@TK2MSFTNGP03.phx.gbl... > Hello, > > I am using SQL Express and Visual Studion 2003. I am trying to DROP > DATABASE Customers using a Stored Procedure and also using ADO.NET. > > Query: > IF EXISTS (SELECT name FROM sys.databases WHERE name = Customers) DROP > DATABASE [Customers] > > ConnectionString: > RemoveConnectionString = "Server = MyComputer\SQLExpress;UID=sa; > Password=MyPassword;Initial Catalog=master" > > > Everytime I call the procedure or ExecuteNonQuery I get: > > Cannot Drop Database 'Customers' because database is currently in use. by > sqlclient.Data.SqlClient.SqlCommand.ExecuteNonQuery() > > I have no problems Dropping the Tables. > > Thanks, > Chuck > > >
Hello Thank You for you help.
I have the Stored Procedure inside the Master Table. I am immediately running the query given after connecting. I also cannot remove it from SQL Express Management Studio. - That is if I copy the SQL into a query window and run it. I get the exact same error. Chuck [quoted text, click to view] "Hardik Bati" <hardikb_removethis_@microsoft.com> wrote in message news:OLkzPow9GHA.4376@TK2MSFTNGP03.phx.gbl... Where is this stored procedure. If it's in the database then you won't be able to use it to drop the database while you are in the same database. Are you trying to run the query directly after connecting useing the connection string just given. Or is there any "use Customer" before execute ExecuteNonQuery(). [quoted text, click to view] "Charles A. Lackman" <Charles@CreateItSoftware.net> wrote in message news:epQ433v9GHA.1172@TK2MSFTNGP03.phx.gbl... > Hello, > > I am using SQL Express and Visual Studion 2003. I am trying to DROP > DATABASE Customers using a Stored Procedure and also using ADO.NET. > > Query: > IF EXISTS (SELECT name FROM sys.databases WHERE name = Customers) DROP > DATABASE [Customers] > > ConnectionString: > RemoveConnectionString = "Server = MyComputer\SQLExpress;UID=sa; > Password=MyPassword;Initial Catalog=master" > > > Everytime I call the procedure or ExecuteNonQuery I get: > > Cannot Drop Database 'Customers' because database is currently in use. by > sqlclient.Data.SqlClient.SqlCommand.ExecuteNonQuery() > > I have no problems Dropping the Tables. > > Thanks, > Chuck > > >
Hello,
I have been doing some testing. I am filling a Dataset using a DataAdapter. If I create a new connection to SQL Express without calling the DataAdapter's Fill Method I can Drop the Database. If I Fill the Dataset with the DataAdapter, dispose the connection and the DataAdapter and create a new connection it still wont Drop the Database. Even if the Dataset is empty it still does not work. It appears that calling the DataAdapter before dropping the table keeps the database in use weather it has finished it's job or not. I can't even call a Stored Procedure to drop the Database if I call the DataAdapter First. Even if I run the program and call the DataAdapter and go into SQL Express Management Studio, I still get the same error. Only when I shut down the program or drop the table without using the DataAdapter does it work. Is there a way around this? Thanks Chuck [quoted text, click to view] "Charles A. Lackman" <Charles@CreateItSoftware.net> wrote in message Hello,news:epQ433v9GHA.1172@TK2MSFTNGP03.phx.gbl... I am using SQL Express and Visual Studion 2003. I am trying to DROP DATABASE Customers using a Stored Procedure and also using ADO.NET. Query: IF EXISTS (SELECT name FROM sys.databases WHERE name = Customers) DROP DATABASE [Customers] ConnectionString: RemoveConnectionString = "Server = MyComputer\SQLExpress;UID=sa; Password=MyPassword;Initial Catalog=master" Everytime I call the procedure or ExecuteNonQuery I get: Cannot Drop Database 'Customers' because database is currently in use. by sqlclient.Data.SqlClient.SqlCommand.ExecuteNonQuery() I have no problems Dropping the Tables. Thanks, Chuck
The most likely cause is that the closed/disposed connection is pooled and
the pooled connection is preventing you from dropping the database. You can specify Pooling=False in the connection string (or similar, depending on the provider you are using) to disable connection pooling. Another method is to execute "ALTER DATABASE MyDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE" to kill all connections to the database befor the drop. -- Hope this helps. Dan Guzman SQL Server MVP [quoted text, click to view] "Charles A. Lackman" <Charles@CreateItSoftware.net> wrote in message
news:OgZmXqz9GHA.1172@TK2MSFTNGP03.phx.gbl... > Hello, > > I have been doing some testing. I am filling a Dataset using a > DataAdapter. > If I create a new connection to SQL Express without calling the > DataAdapter's Fill Method I can Drop the Database. > > If I Fill the Dataset with the DataAdapter, dispose the connection and the > DataAdapter and create a new connection it still wont Drop the Database. > > Even if the Dataset is empty it still does not work. It appears that > calling the DataAdapter before dropping the table keeps the database in > use > weather it has finished it's job or not. > > I can't even call a Stored Procedure to drop the Database if I call the > DataAdapter First. Even if I run the program and call the DataAdapter and > go into SQL Express Management Studio, I still get the same error. Only > when I shut down the program or drop the table without using the > DataAdapter > does it work. > > Is there a way around this? > > Thanks > > Chuck > > > "Charles A. Lackman" <Charles@CreateItSoftware.net> wrote in message > news:epQ433v9GHA.1172@TK2MSFTNGP03.phx.gbl... > Hello, > > I am using SQL Express and Visual Studion 2003. I am trying to DROP > DATABASE Customers using a Stored Procedure and also using ADO.NET. > > Query: > IF EXISTS (SELECT name FROM sys.databases WHERE name = Customers) DROP > DATABASE [Customers] > > ConnectionString: > RemoveConnectionString = "Server = MyComputer\SQLExpress;UID=sa; > Password=MyPassword;Initial Catalog=master" > > > Everytime I call the procedure or ExecuteNonQuery I get: > > Cannot Drop Database 'Customers' because database is currently in use. by > sqlclient.Data.SqlClient.SqlCommand.ExecuteNonQuery() > > I have no problems Dropping the Tables. > > Thanks, > Chuck > > > >
Don't see what you're looking for? Try a search.
|
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
| home · blog · groups | Questions? Comments? Contact the dn |