Is it safe to shutdown users?
Hi All,
We have an application that is halfway's linked to sql, and halfway to access tables.
I do have sometime to work on the sql back end to add a field for a table for example, and there are users connected there, is it safe to disconnect them thru the activity monitor?
my question is on both sides, mainly on access, but also from sql.
We have an application that is halfway's linked to sql, and halfway to access tables.
I do have sometime to work on the sql back end to add a field for a table for example, and there are users connected there, is it safe to disconnect them thru the activity monitor?
my question is on both sides, mainly on access, but also from sql.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
On a live db that people are in - and that table is in use? Pretty sure you will get an error that you cannot make design changes because table is in use.
mx
mx
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Thanks, AC.
I love learning something new every day!
Dale
I love learning something new every day!
Dale
ASKER
Hi experts,
I just got a chance to test Anthony's suggestion in a time when there were not significant amount of users connected to the database, however nobody has imposed a tablelock or any other blocking type other than the async_network_io mentioned above, which are all caused by select statements, and I was not able to add a column by the alter table either..
attached screenshot of both, current activity monitor (sorted by wait type) and how it performed when i ran the sql statement (until I had to cancel it.)
Doc1.doc
I just got a chance to test Anthony's suggestion in a time when there were not significant amount of users connected to the database, however nobody has imposed a tablelock or any other blocking type other than the async_network_io mentioned above, which are all caused by select statements, and I was not able to add a column by the alter table either..
attached screenshot of both, current activity monitor (sorted by wait type) and how it performed when i ran the sql statement (until I had to cancel it.)
Doc1.doc
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Anthony,
I am attaching a screenshot of the results I just got from running the sp_whoisactive,
Actually this is a typical case I encounter every time I want to make changes to the database (after hours..).
Therefore back to the original question posted, do you see any alternative way to get this task accomplished besides kicking out users, or implement Dale's original suggestion?
Doc2.doc
I am attaching a screenshot of the results I just got from running the sp_whoisactive,
Actually this is a typical case I encounter every time I want to make changes to the database (after hours..).
Therefore back to the original question posted, do you see any alternative way to get this task accomplished besides kicking out users, or implement Dale's original suggestion?
Doc2.doc
I am sorry I cannot read your screen shots, so I will repeat what I have said previously, if this is a well designed client/server application where connections are not kept open for hours on end then you can use ALTER TABLE. If on the other hand it is not well behaved and you keep connections open then yes you will have to shut down all users or worse still the the database itself. You can do this a couple of ways:
1 KILL open sessions.
2. Close the database and reopen as a single user.
Either way you risk that users lose data and in most shops that is unacceptable.
Pick your poison.
1 KILL open sessions.
2. Close the database and reopen as a single user.
Either way you risk that users lose data and in most shops that is unacceptable.
Pick your poison.
ASKER
Hi,
I guess those long transactions are caused by access when displaying records in combo boxes, it probably loads first just a small quantity of records and only later it keeps loading the rest, or perhaps it just never finishes as long as the user doesn't scroll down to the end of the list...
What is the best way to avoid that?
@JDettman,
I guess those long transactions are caused by access when displaying records in combo boxes, it probably loads first just a small quantity of records and only later it keeps loading the rest, or perhaps it just never finishes as long as the user doesn't scroll down to the end of the list...
What is the best way to avoid that?
@JDettman,
No, it doesn't close the JET connections...Is this also true in the scenario that I have running a sql select statement based on two tables where one is an access table and the other is a sql table?
What is the best way to avoid that?
If you have to use that design, there is nothing you can do about it.
If you have to use that design, there is nothing you can do about it.
ASKER
Thanks to all participants,
At this point I am still reserving some points for the expert who can answer
1- what happens to jet connections when killing a sql transaction that is half way merged with an access table, (like select accesstable.id, sqltable.id from accesstable inner join accesstable on sqltable.id = accesstable.id) ?
2- when users connect to a server using remote desktop, and they leave the connection in a disconnected state (by just closing the x), is it safe to log them off using terminal server manager? (when I mean safe, I am referring mainly to access tables that are more prone to corruption).
At this point I am still reserving some points for the expert who can answer
1- what happens to jet connections when killing a sql transaction that is half way merged with an access table, (like select accesstable.id, sqltable.id from accesstable inner join accesstable on sqltable.id = accesstable.id) ?
2- when users connect to a server using remote desktop, and they leave the connection in a disconnected state (by just closing the x), is it safe to log them off using terminal server manager? (when I mean safe, I am referring mainly to access tables that are more prone to corruption).
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@fyed,
1- Do you have a sample of code that I can see how all that works?
2-would just a docmd.quit do the job? isn't it the same as the users exiting the program from the exit menu or just the outer X?
3- I am concerned of using the timer event all the time, its would not have bad impact on the programs performance or affect cpu usage etc..?
@ JDettman,
1- the sql monitor is showing a wait type of async_network_io for this process, do you still say for this that it cant lead to corruption?
2- since the form is bound to different type of tables and recordsets, what happens when I close one sql connection, does it also closes the access/jet connection therefore leading to access corruption?