• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 218
  • Last Modified:

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.
0
bfuchs
Asked:
bfuchs
  • 6
  • 5
  • 3
  • +2
9 Solutions
 
Dale FyeCommented:
Generally, I build a feature into my multi-user applications that allows the application administratore (or me) to set a flag (Yes/No field) in my tbl_App_Properties.  
When that field (InMaintenance) is set to true, the Timer Event in my splash form will check the status of that field every 5 minutes.  If it is true, it will popup a message telling the user that the application will shut down in 5 minutes.  From that popup form, the user can elect to shut down the application immediately or snooze.  If the user elects to snooze, the application will remind him/her in 3 minutes and at 4 minutes that it is about to shut down.  If the user has not chosen to exit during one of those snooze sessions, the application will work backwards through all open forms, elegantly undoing the most recent unsaved changes and closing each form.

If a user tries to log on while this flag is set, the application popups up a message that says the application is undergoing maintenance and advises them when to try again.

HTH
Dale
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Yes and no.

In regards to SQL, maybe.   While the data base will roll back, if you weren't careful with development and you have a multi-table update not wrapped in a transaction, then you could have a problem.

 The database won't corrupt, but your data might not be consistent.

In regards to JET, no.   You don't even want to disconnect a user.   No rollback will occur if their in the middle of writing records and you can end up with the DB in a corrupt state.

Jim.
0
 
bfuchsAuthor Commented:
Hi, Thanks for replying,

@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?
0
The Lifecycle Approach to Managing Security Policy

Managing application connectivity and security policies can be achieved more effectively when following a framework that automates repeatable processes and ensures that the right activities are performed in the right order.

 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<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?>>

  Yes.  SQL automatically rolls back any transaction in process on a disconnect.

<<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? >>

  No, it doesn't close the JET connections, but your error handling better be good<g>

Jim.
0
 
Anthony PerkinsCommented:
Why do you have to disconnect the users?  There is  no need to do that  to add a column to a table.  It is a simple process of doing:
ALTER TABLE MyTableName ADD MyNewColumn integer
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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
0
 
Anthony PerkinsCommented:
Pretty sure you will get an error that you cannot make design changes because table is in use.
I am afraid not.  Not in SQL Server at least.  It is just a meta change.  Of course I am making the assumption the new column allows NULLs or has a DEFAULT value, but that is a different matter entirely.

And I should have added that I am also making the assumption that the application is not doing something such as locking the table or has a Transaction open for a very long time (which would have the same effect as using TABLOCKX).  Either one of those situations would cause the attempt to change the schema to eventually timeout as it would be unable to modify the structure.

But if you are using SQL Server in a typical client/server topology there is no reason to close the connection to the database in order to do a simple task of adding a column.

Notice that I did not say inserting a column.  That is quite a different story as it requires the table to be rebuilt.
0
 
Dale FyeCommented:
ac,

Care to explain the difference between 'adding a column' and 'inserting a column'.  I use SQL Server as a back-end for many of my Access applications, but have never heard or seen a statement stated quite that way.
0
 
Anthony PerkinsCommented:
Sure.

If you add a column to the end using ALTER TABLE tablename ADD COLUMN this is just a meta change and you do not have to rebuild the table.  As you can imagine this makes a tremendous difference when modifying Production data where the table can contain millions of rows and of course foreign keys.

If on the other hand, you from a purely "purist" point of view you rather see your columns in a logical grouping and need to "insert" your column not at the end but in the middle, then you have no recourse but to rebuild the table, drop and create the foreign key constraints .  This can be prohibitive to accomplish online on a large table.  This is exactly the way SSMS table Designer and (I believe) MS Access work behind the scene.

The SSMS table Designer is actually not very smart, in that it will attempt to rebuild the table if you add a column anywhere including at the end.  This is one of the many failings of the table Designer and where possible you should avoid it like the plague.
0
 
Dale FyeCommented:
Thanks, AC.

I love learning something new every day!

Dale
0
 
bfuchsAuthor Commented:
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
0
 
Anthony PerkinsCommented:
async_network_io mentioned above, which are all caused by select statements
That is not entirely true.  async_network_io is an indication that SQL Server is waiting on the client application to complete.  The typical reasons is that the developers are inadvertently returning a very large result set and/or are processing a few rows at a time or worse still they are returning a resultset that is updateable.  This is why I specifically mentioned that you could use this in a client/server topology where the table was not currently being used.  Obviously if you are loading or retrieving a million rows into a table you would have to wait until it completes.

I was not able to add a column by the alter table either..
I suspect that the ALTER TABLE is simply blocked by some other process and until that completes you will not be able to do this (you can confirm this quite easily using a script such as sp_whoisactive).  It does not mean you have to close the database in any shape way or form, that would put many shops out of business.

Let me know if you need a SQL script example of how ALTER TABLE works and how instantaneous it is regardless of the size of the table.
0
 
bfuchsAuthor Commented:
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
0
 
Anthony PerkinsCommented:
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.
0
 
bfuchsAuthor Commented:
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,
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?
0
 
Anthony PerkinsCommented:
What is the best way to avoid that?
If you have to use that design, there is nothing you can do about it.
0
 
bfuchsAuthor Commented:
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).
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
on #1, you'll get an error on the recordset or query.  DB's will be fine since it's a read only operation at that point.  You may end up with a phantom user at that point.

on #2, No unless you can ensure that they are idle and not processing anything.

With JET Db's, all the processing is done client side.  There is no server side process running that controls access to the DB.

 So anytime your using a JET DB, no matter how that is being done, cutting the connection runs a risk of corrupting the DB.   In general you don't, but can easily if writes were in progress.

 And even if the DB doesn't corrupt while writing, you may end up in a inconsistent state as far as the app is concerned (part of the DB may be updated and part not).

 Cutting a connection to a JET DB is never a good idea.

The simple solution is to start the app with a hidden form, run a timer event once every x minutes, and if the flag is true, do a couple un-do's and quit the app.

  When you want everyone out, set the flag and wait x minutes.

Jim.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Protect Your Employees from Wi-Fi Threats

As Wi-Fi growth and popularity continues to climb, not everyone understands the risks that come with connecting to public Wi-Fi or even offering Wi-Fi to employees, visitors and guests. Download the resource kit to make sure your safe wherever business takes you!

  • 6
  • 5
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now