Kicking SAS users out programmatically

2
votes

Is it possible to cut users' connections to a SAS server programmatically from the server? I know I can kill individual SAS processes by using standard Windows tools such as Task Manager when I have a remote desktop connection to the server, but can this be done programmatically, and moreover, can we prevent any users from connecting?

The situation is as follows:

We have a SAS 9.4 installation on a Windows Server 2008 R2 server. There's a bunch of folders on the server with SAS tables in them, and a bunch of end users who use these SAS tables through SAS Enterprise Guide (that has been installed on their desktops). Now, there's also a large SAS batch run, that we run every day to update all SAS tables. We would like to make sure that no users have any SAS tables opened through EG when the batch run is running; otherwise it might fail because a table is locked. Of course, the batch run is usually done at nighttime, but as its schedule depends on many other things we cannot be 100% certain it is completed before people come to work.

What we want is some kind of a script or SAS setting that would allow us to automatically cut all users' SAS connections before the batch run starts, keep them out during the run, and then allow them to reconnect when the batch run is complete.

Any hints would be appreciated!

Edit: Would it be possible to write a cmd-script using taskkill that would stop all sas.exe processes running under any other user than the current one? Does SAS actually even create a sas.exe process on the server for each user in this case?

3
windowssas
This is precisely why I avoid using SAS tables whenever possible! Could you migrate those tables into a database instead? That way you could run the updates without having to kick off your users. Alternatively you could change those tables into a series of views (pointing at the original tables), which could then be renamed / removed during the ETL phase.. - Allan Bowe
We're stuck with SAS tables for now I'm afraid. Personally I too prefer "proper" databases for storing data. Using views is a good idea; then again, the users would still have access to the real tables too (e.g. with a libname) so we couldn't be certain no tables are locked. - Juha K
In 9.4 you can have metadata bound libraries.. That would prevent direct libname connections - Allan Bowe
@RawFocus That's typically why you license SAS/Share if you want to do these types of things. Unfortunately that's additional $$$ and not always an option. IMO it should be part of Base/SAS because that's pretty standard functionality... - Robert Penridge
SAS/Share won't stop a table getting locked if it's open in EG (to be sure, I just tested this). Agreed that concurrent read/write should be basic functionality! - Allan Bowe

3 Answers

3
votes

You can use the SYSTASK or X commands in (Windows) or (UNIX) . My extract, transform and load (ETL) process runs in the UNIX environment and SYSTASK is a big help for just the reason being asked about. I create the data sets with SAS then use the mv (UNIX) command to replace the existing data set. The file system will override the SAS lock.

2
votes

As you are on 9.4, I put forward the suggestion that your library should be converted to a read-only, metadata bound library. This should prevent table locks, and negate the need to close user sessions (with potential impact on their productivity through loss of work tables / macro variables etc)

1
votes

Look into SAS/Share. If that's not an option, then two ugly ideas are:

  1. If the users are connecting using libname statements to a windows share you could just disable the share. Then re-enable it. This could easily be done using command line instructions and the x command.

  2. If they are connecting through a SAS Service, stop the service and restart it. Again this can be done via the command line and x.

After doing either (or both) of the above. Make sure the first thing that happens is that you issue a lock <tablename>; statement to make sure no-one can use those tables again until your jobs are done. You can then restart the service/re-enable the shares.

Once you are finished, make sure that the SAS session that issued the lock command is either closed or clears the lock.