[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