Iman_Davoodian
asked on
How can I Disconnect all SQL SERVER 2000 active Conncetions using Delphi code or sql syntax?
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 .
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 .
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I would not use an ADOQuery to run a command. Use ADOCommand instead and see what happens.
John
John
ASKER
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 ...
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 ...
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
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
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.
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.
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
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
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.
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.
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!
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!
i have full source code project for backup & restore my email is : pazinger@gmail.com , if interested
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.
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.
This is a very slick solution...