[dba-SQLServer] Who Has adp/ade open?

There used to be a free utility called Jet4Admin from FMS
(www.fmsinc.com), but they removed it from their site.  It displayed a
list of users / machines that were currently connected and you could
send a message to users / restrict new connections to the mdb / etc.

Note sure how it woulr work on an adp though, as I assume it also worked
on the .ldb file.

I'll see if I can find my copy anyway.

Cheers,
A

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of David
Emerson
Sent: Tuesday, 29 November 2005 10:19 AM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer] Who Has adp/ade open?

Thanks Marty.  Perhaps some background will help.

The program is run as a runtime via terminal server.  There can be up to
10-15 concurrent users at a time.
Part of my program uses the following line to copy a spreadsheet into a
table:

DoCmd.TransferSpreadsheet acImport, , "dbo.ttmpTemporary",
Me!txtDataFile

Sometimes this causes Access to crash (Access has encountered a problem
and needs to close ...).

In the past it has seemed to be caused by users having several copies of
the program open at once (they only need one copy but try to tell them
not to keep opening a new instance of the program but to use the one
they already have open :'( )  and then closing their terminal server
session down but not closing the databases first.

When the server is rebooted then the problem line works fine until
suddenly it decides to spit out the dummy again.

I do have Enterprise Manager and can see what users have the connections
to the database.  Part of the problem is that there are three versions
of the front end all connecting to the same SQL database.  I was hoping
to try to identify somehow what copies of the front end were open to see
if there are any patterns.

I am trying to a) find out if the user's work haboits are causing the
problem, or b) if it is some other problem, trying to find out what.

David


At 29/11/2005, you wrote:
>Is this because you don't have a version of SQL EM?
>
>Maybe a call to DBCC in SQL to get user connection.
>Another way that might be more exact.
>You could use WMI and check through each machine name in the domain and

>check for running adp task processes. You could then maybe and I stress

>maybe also find out if a particular adp file is open on that machine 
>via some similar WMI method.
>
>WMI can also give you all machine names in a domain.
>
>Look at various sample scripts here
>http://www.activexperts.com/activmonitor/windowsmanagement/adminscripts
>/
>
>This might be time consuming.
>Something like this
>
>'Reports the account name under which each process on a computer is
running.
>'you would have to run this for every machine name.
>Dim strComputer As String
>Dim strUserDomain As String
>Dim strNameofUser As String
>Dim colProcessLiust As Object
>Dim objProcess As Object
>Dim objWMIService As Object
>Dim colproperties as variant 'or is it object
>
>strComputer = "."  'This is the machine name in the domain.  "."
>indicates "local"
>Set objWMIService = GetObject("winmgmts:" _
>     & "{impersonationLevel=impersonate}!\\" & strComputer & 
>"\root\cimv2") Set colProcessList = objWMIService.ExecQuery _
>     ("Select * from Win32_Process")
>For Each objProcess in colProcessList
>     colProperties = objProcess.GetOwner(strNameOfUser,strUserDomain)
>     debug.print  "Process " & objProcess.Name & " is owned by " _
>         & strUserDomain & "\" & strNameOfUser & "."
>Next
>
>
>David Emerson wrote:
>
> >I am trying to identify what users have a database open.  adp's don't

> >seem to have an ldb file (presumably because they use SQL and not 
> >Jet).  Apart from looking at the current activity in SQL management, 
> >is there any other way of finding out who has an adp or ade open?
> >
> >Regards
> >
> >David Emerson
> >Dalyn Software Ltd
> >999 Moonshine Rd, RD 1
> >Judgeford, Porirua
> >New Zealand 6006
> >Phone    0064 4 235-6782
> >Fax      0064 4 235-6783
> >
> >__
> >
> >
> >
> >
>
>--
>Marty Connelly
>Victoria, B.C.
>Canada
>
>
>
>_______________________________________________
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>http://www.databaseadvisors.com

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com



IMPORTANT - PLEASE READ *** This email any any files transmitted with it are confidential and may contain information protected by law from disclosure. If you have received this message in error, please notify the sender immediately and delete this email from your system. No warranty is given that this email or files, if attached to this email, are free from computer viruses or other defects. They are provided on the basis the user assumes all responsibility for loss, damage or consequence resulting directly or indirectly from their use, whether caused by the negligence of the sender or not.