Solved

Is it safe to shutdown users?

Posted on 2014-03-12
18
205 Views
Last Modified: 2014-03-24
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
Comment
Question by:bfuchs
  • 6
  • 5
  • 3
  • +2
18 Comments
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 120 total points
ID: 39925168
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
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 220 total points
ID: 39925302
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
 
LVL 3

Author Comment

by:bfuchs
ID: 39927540
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
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 220 total points
ID: 39927854
<<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
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 160 total points
ID: 39930731
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
 
LVL 75
ID: 39930824
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
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 160 total points
ID: 39931761
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
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 120 total points
ID: 39931838
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
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 160 total points
ID: 39932714
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
Superior storage. Superior surveillance.

WD Purple drives are built for 24/7, always-on, high-definition security systems. With support for up to 8 hard drives and 32 cameras, WD Purple drives are optimized for surveillance.

 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39932758
Thanks, AC.

I love learning something new every day!

Dale
0
 
LVL 3

Author Comment

by:bfuchs
ID: 39935523
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
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 160 total points
ID: 39935727
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
 
LVL 3

Author Comment

by:bfuchs
ID: 39935826
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39938506
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
 
LVL 3

Author Comment

by:bfuchs
ID: 39938718
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39940312
What is the best way to avoid that?
If you have to use that design, there is nothing you can do about it.
0
 
LVL 3

Author Comment

by:bfuchs
ID: 39948948
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
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 220 total points
ID: 39949944
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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
This article explains in simple steps how to renew expiring Exchange Server Internal Transport Certificate.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

708 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now