How do i close a current connection to a database using t-sql?
I fail some time to drop the database getting messages that it's
currently in use.
Using the wizard to delete the database, i could check the option to
close all connections to the db, but how do i do it using t-sql?
best regards
10  62282 
I did actually,
USE [Master];
GO
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'MyDB')
BEGIN
PRINT N'Database [MyDB] already exists';
DROP DATABASE MyDB;
END
GO
Similary, is there a similar command like 'disconnect' such as in DB2?
On 27 May 2006 19:48:27 -0700, coosa wrote: How do i close a current connection to a database using t-sql?
I fail some time to drop the database getting messages that it's
currently in use.
Hi coosa,
If you want to disconnect YOUR OWN connection to a database, check out
Erland's reply.
If you need to do maintenance but can't because OTHER people still have
open connections to a database, then you might want to use one of the
following
ALTER DATABASE <dbname> SET SINGLE_USER
or
ALTER DATABASE <dbname> SET SINGLE_USER WITH ROLLBACK_IMMEDI ATE
The first version will allow curent connection to finish their business
and commit their work. The latter option immediately disconnects all
open conenctions and rolls back any changes from unfinished
transactions.
--
Hugo Kornelis, SQL Server MVP
That might be it; some thing remotly is still in use. Then, is there a
way to determine which connections are being used and wait for them but
prevent any new connections?
On 28 May 2006 18:46:15 -0700, coosa wrote: That might be it; some thing remotly is still in use. Then, is there a
way to determine which connections are being used
Hi coosa,
EXEC sp_who2;
and wait for them but
prevent any new connections?
ALTER DATABASE <dbname> SET SINGLE_USER;
(By _not_ adding the WITH ROLLBACK_IMMEDI ATE option, you tell SQL Server
to disallow new connections but wait until existing connections are
broken before setting the DB to single user)
Note that many front-end programs keep their connection open, mostly
being idle while the person on the screen enters data, answers a phone
call or visits the water cooler. If you waiting for those connections to
close, you won't have your DB in single-user state before the office
closes.
--
Hugo Kornelis, SQL Server MVP
It's interesting what's happening ...
i run:
USE Master;
GO
EXEC sp_who2;
GO
The 'MyDb' is still under the status RUNNABLE for the command 'SELECT
INTO' under the ProgamName 'Microsoft SQL Server Management Studio -
Query'.
I run the command again after a minute and it disappears.
It seems when i swith the use to a different DB, the change has no
IMMEDIATE effect.
Again, using the "Management Studio", by right clicking the Database
Name and choosing to "Delete", two check boxes can be selected and the
latter is "Close existing Connections" and it never failed to delete.
I have used the suggestion of usning both "ALTER DATABASE <dbname> SET
SINGLE_USER WITH ROLLBACK_IMMEDI ATE" and "ALTER DATABASE <dbname> SET
SINGLE_USER" but it's the same. Erland suggestion recommnds stoping the
entire server which i can't afford since there are other databases
running.
On 29 May 2006 08:22:10 -0700, coosa wrote: It's interesting what's happening ...
i run:
USE Master;
GO
EXEC sp_who2;
GO
The 'MyDb' is still under the status RUNNABLE for the command 'SELECT
INTO' under the ProgamName 'Microsoft SQL Server Management Studio -
Query'.
I run the command again after a minute and it disappears.
It seems when i swith the use to a different DB, the change has no
IMMEDIATE effect.
Hi coosa,
Very strange. I have never experienced or heard this before. And I was
unable to reproduce - when I ran the code above, sp_who2 reported the
connection to be runnable in the master DB.
Again, using the "Management Studio", by right clicking the Database
Name and choosing to "Delete", two check boxes can be selected and the
latter is "Close existing Connections" and it never failed to delete.
Under the hood, Management Studio uses the ALTER DATABASE command I
suggested, with the ROLLBACK_IMMEDI ATE option. This is easy to verify:
make a DB, open some windows in MS to connect to this test DB, then
right-click the DB, click "Delete", check "Close existing connections",
then instead of clicking "OK", click "Script / Script to Clipboard".
Finally, paste the contents of the clipboard in a query window or in a
text file. Here's what was generated on my computer:
EXEC msdb.dbo.sp_del ete_database_ba ckuphistory @database_name = N'Temp'
GO
USE [master]
GO
ALTER DATABASE [Temp] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
/****** Object: Database [Temp] Script Date: 05/30/2006 00:53:16
******/
DROP DATABASE [Temp]
GO
I have used the suggestion of usning both "ALTER DATABASE <dbname> SET
SINGLE_USER WITH ROLLBACK_IMMEDI ATE" and "ALTER DATABASE <dbname> SET
SINGLE_USER" but it's the same.
What does "the same" mean? Do you get any error messages? If so, what
messages?
What happpens if you open a query window in SSMS, then type (or copy)
and execute the query below (replacing MyDB [twice!] with the actual
name of the DB you want to drop). If you get any errors, please copy and
paste the exact messages into a reply to this message (unless you're
running a localized Cyrillic or similar installation - in that case, a
translation is actually preferred <g>)
USE master
go
ALTER DATABASE MyDB SET SINGLE_USER WITH ROLLBACK_IMMEDI ATE
go
DROP DATABASE MyDB
go
Of course, you should replace MyDB with the real name of your database
(two times!)
Erland suggestion recommnds stoping the
entire server which i can't afford since there are other databases
running.
Erland though you were asking how to force connection to the _SERVER_ to
be broken. For dropping a database, it suffices to break the connection
to the _database_. I know Erland well enough to be 100% sure that he'd
never recommend shutting down a server to drop connections to a DB.
--
Hugo Kornelis, SQL Server MVP This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: dw |
last post by:
Which is the most efficient way to hit the database: 1) to open/close the
connection for each call to the database on a page (we have Subs that do
this); 2) or use the same connection and close it at the end? My feeling is
that 2 is better, but just wanted to confirm with the experts. Thanks in
advance.
|
by: billd |
last post by:
I have a function that returns a SqlDataReader created by:
result = command.ExecuteReader(CommandBehavior.CloseConnection);
I then bind the result to a grid control:
myDataGrid.DataSource = sr;
myDataGrid.DataBind();
Do I need to explicitly close the DataReader (and thus the connection) after
|
by: Craig |
last post by:
I have some methods that open a database connection, get some data and then
return a datareader. How do I manage closing the connection to the database
then?
public OracleDataReader ExecuteCommand(string cmdStr) {
Connect();
OracleCommand cmd = new OracleCommand(cmdStr, this._conn);
OracleDataReader reader = cmd.ExecuteReader();
return reader;
}
|
by: mescano |
last post by:
I am currently implementing a singleton pattern for accessing a
database. Is it advisable to close the connection to the database at
all -- thus leaving it open or should it be closed. If closed, when
should I it close it -- after the execution of the each command to the
database? If leaving open, what impacts does it have. Imagining that
it is one connection to the database.
Thanks,
mescano
|
by: Varangian |
last post by:
Hello there people,
I'm having some kind of problem. I have a function that returns a
datareader. At some point using the application I get an error
"Unspecified error" (ssssoooo helpful) :). I think I know the problem.
My Connection remains open.
Is there a way I can do to close the Connection. below is the code.
Thank you very much as always
| | |
by: bonita |
last post by:
In my ASP.NET page, I have 2 checkboxes for users to choose which
crystal report they want to display. These two reports use different
tables. If report1 has been choosen and displayed in the crystal
report, then I cannot check another checkbox to display report2
afterwards. If I close the website and open again, I can choose report2
and display as crystal report correct, but now I cannot check the
checkbox1 to display report 1 afterwards....
|
by: DavideR |
last post by:
I'm working with vs2005 (vb.net)
i need to detach a database
autoclose property is set to true
close cursor on commit is set to true
i use the sp_detach with adodb (the program has been converted from vb6)
and i receive the error that db is still in use becouse the connection is
still actibe
i still access to file via adodb
in the routine in which i read the db the code is
sub readdb
|
by: Jack |
last post by:
I have a WebRequest object that I use to log into a site and then post some
XML. In doing this I set the KeepAlive = true so that it maintains the
connection and does operates undo the initial login.
Is there a way to force this connection to close after I an done with it.
What happens is that I may need to post the XML in several chunks due to
size therefore I cannot set the KeepAlive to False when I post the XML and
the way the code...
|
by: Max2006 |
last post by:
Hi,
I saw the Business Logic Layer pattern suggested at the following link and I
found that the pattern never closes the connection:
http://www.asp.net/learn/dataaccess/tutorial02cs.aspx?tabid=63
I am using strongly typed datasets and TableadApters. I assume that
TableadApters always closes the connections automatically. Is that correct
assumption?
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| | |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| | |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
