[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How can I Disconnect all SQL SERVER 2000 active Conncetions using Delphi code or sql syntax?

Posted on 2007-10-16
11
Medium Priority
?
2,471 Views
Last Modified: 2013-11-23
Hello .
I'm develpoing a module of an accounting application that needs to restore a backed up Database in SQL SERVER 2000 . I'm using Delphi to do this , I'e created a query to restore the Selected back up file , when I test my restore query in Query analyzer , it works correctly , but when I  use it in delphi application using ADOQuery to execute , it raises an exception and messages , the database which I'm about to restore is in use .
Now I want to know how can I disconnect all the actve connections to the specific database in SQL server using Delphi code or a SQL syntax which is able to be used in my Delphi code .
0
Comment
Question by:Iman_Davoodian
  • 4
  • 3
  • 2
  • +2
11 Comments
 
LVL 17

Expert Comment

by:Chris Mangus
ID: 20089791
Check the article at: http://www.wisesoft.co.uk/Articles/SQL%20Server/sp_killusers.doc

This is a very slick solution...
0
 
LVL 30

Accepted Solution

by:
nmcdermaid earned 375 total points
ID: 20090316
Try connecting to the MASTER database rather than connecting to the database you are trying to restore... if indeed it is your connection that is the issue.
0
 
LVL 18

Expert Comment

by:Johnjces
ID: 20090863
I would not use an ADOQuery to run a command. Use ADOCommand instead and see what happens.

John
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Iman_Davoodian
ID: 20093916
Dear Johnjces ,
I try to close all my open connections , but It raises that error again . I'm sure that there is an open connection somewhere in my application that causes this error . I tried so much to find that open connection , but I couldn't . I have not any time more , so I have to get an emergency solution to solve this problem at this time , that why , I'm looking for a solution to close all open connection without any condition ...
0
 
LVL 18

Expert Comment

by:Johnjces
ID: 20094170
Iman,

I have not heard of SQL Server needing to close connections to it before "something happens" to SQL. Like backup or restore. If a user had an active table open and a restore came along, the user might have an issue if trying to modify a now non existant or different record. MS SQL Server "knows" the state of its connections and caches stuff... usually.

In your application that restores a backup database (or is it just a table in the database), are any tables open in that specific application that is doing the restoring? I would check for that.

Lastly, I do not know of any way via SQL Server or SQL code to close all client connections to the SQL server itself.

I think what I would do is use a TCPClientSocket and TCPServerSocket. When your application is ready to restore, have it broadcast some string via TCP that your clients will listen for. When that string is sent and received your clients close all database connections with a message to the user. Have your app wait a moment then restore the db. Then your application could broadcast another string and have the clients reconnect to the database, again with a message to your users.

John
0
 
LVL 17

Expert Comment

by:Chris Mangus
ID: 20094258
The solution I posted will close all connections to a particular database.

FWIW, if a user is connected to a database SQL Server will NOT do a restore of the database.  The restore will fail and you'll get a message telling you that there are active connections.  Backups, obviously, will work just fine while users are connected.
0
 
LVL 18

Expert Comment

by:Johnjces
ID: 20094336
cmangus,

Sorry, I didn't see your post/read the doc. That is great to know as I did not know such could be done! I have restored a SQL Server DB some time ago and I had one user still open, that I didn't know about who complained afterwards. The restore worked.

Her tables may have been closed but the connection open. So, do the tables just need to be closed or the active connections, since she was active or so I believe?

Thanks for any further thouhhts info. I hate giving out bad advice!

John
0
 
LVL 17

Expert Comment

by:Chris Mangus
ID: 20094617
I'm not sure how your restore worked with an open user connection to that database.  All database connections have to be closed.

My guess would be that the user wasn't connected to the database you restored.  How was the user connecting?  If it was through MSAccess, unless they have the table open they don't have an open connection.
0
 
LVL 18

Expert Comment

by:Johnjces
ID: 20094680
CMangus,

Thanks for the reply. It wasn't Access, but MS SQL Server 2000. She was using a app I wrote in Delphi using ADO.

I cannot nor am  ever certain, of what exactly any user was/is doing at any given time in the environment I work in as users here can be "odd". So, I guess I really do not know other than she told me the app was running then when she went to update a record or open a new one "things were different". I told her about the restored db etc and that was the cause of her troubles, but I can never be 100% certain if in fact the app was running on her PC.

Anyway, thanks!



0
 

Expert Comment

by:ohag
ID: 20437750
i have full source code project for backup & restore my email is : pazinger@gmail.com , if interested
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 20446093
Reading this post and clarifying.....

1.The user WAS in the database
2. JohnJces ran the script to disconnect all users
3. JohnJces successfully restored the database
4. The user complained because they were turfed out at step 2



>> Her tables may have been closed but the connection open. So, do the tables just need to be closed or the active connections, since she was active or so I believe?


Closing a connection 'closes' the tables.
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question