Solved

SQL Server Activity monitor issue

Posted on 2013-05-22
12
369 Views
Last Modified: 2013-05-28
Hi All, I have been using activity monitor for a while, to monitor the users who are posing a lock to the server, and causing blockage to other users request.
Recently I realized that users that have access 2003, their host name are displayed something like "d7m6rnxx", is there any way to fix that?
0
Comment
Question by:bfuchs
  • 5
  • 4
12 Comments
 
LVL 84
ID: 39189558
How should they be displayed? In other words, what would you expect to see instead of "d7m6rnxx"?
0
 
LVL 3

Author Comment

by:bfuchs
ID: 39189704
Hi, all users with other version of office (referring to access2k) have their hostname listed, which is what you get when using dos command prompt by typing hostname then hit enter, for example mine is listed as BFUCHS1
Just to add some peace of info, this issue is only happening to our ADP project, while our second app which is an MDB, they are listed correctly.
To boil it down, this only happens to office 2003 in an ADP front end.
0
 
LVL 14

Expert Comment

by:Bill Ross
ID: 39199840
Hi,

In your connection string set the Application Name (APP) parameter to whatever you want.  Sometimes I even put in the user or computer name so it shows up in SQL Activity monitor.

="ODBC;....;Application Name=My Stuff;..."

or

="ODBC...;APP=Fred;..."

Regards,

Bill
0
 
LVL 3

Author Comment

by:bfuchs
ID: 39199858
Hi thanks for replying,
This is an MS Access ADP project connected to SQL server back end, we use the access interface to connect to the database, which each user takes their own copy, we dont supply manually a connection string, and by the application we just refer in code as "objRec.ActiveConnection = CurrentProject.Connection"
when i looked into immediate window what i contains, i got the following:
"Provider=MSDataShape.1;Persist Security Info=True;Data Source=sql\SQLEXPRESS;User ID=accessusers;Password=MYPWD;Initial Catalog=MYDB;Data Provider=SQLOLEDB.1
", were exactly do i need to set the user name?
0
 
LVL 14

Expert Comment

by:Bill Ross
ID: 39199887
Hi,

Change your connection string as follows:

"Provider=MSDataShape.1;Persist Security Info=True;Data Source=sql\SQLEXPRESS;User ID=accessusers;Password=MYPWD;Initial Catalog=MYDB;Data Provider=SQLOLEDB.1;APP=YourNameHere
",

Give that a try and see what is shown on Activity Monitor.

To dynamically pass the user's name or computer into the connection string you will need to write a procedure/api call to get the name and then create a string variable and concatenate it.

Private Declare Function GetUserNameA Lib "advapi32" (ByVal lpBuffer As String, nSize As Long) As Long
Public Function GetUserName() As String
   
    Dim lpBuf As String
    Dim ret As Long
   
    lpBuf = String(255, 0)
    ret = GetUserNameA(lpBuf, Len(lpBuf))
    GetUserName = UCase(Left(lpBuf, InStr(lpBuf, Chr(0)) - 1))
   
End Function

The GetUserName() function should return the current user.

strConnect="Provider=MSDataShape.1;Persist Security Info=True;Data Source=sql\SQLEXPRESS;User ID=accessusers;Password=MYPWD;Initial Catalog=MYDB;Data Provider=SQLOLEDB.1;APP=" & GetUserName() & ";"


Regards,

Bill
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 3

Author Comment

by:bfuchs
ID: 39200197
Hi Bill, Although I am not currently supplying the connection string, its being auto saved as described above, I tried following your suggestion by placing the following on the Main form's opening event "    CurrentProject.Connection = CurrentProject.Connection & ";APP=" & GetUserName() & ";"
however i am getting the error below:
"Operation not allowed when object is open"
Please let me know how to proceed,
Thanks
Ben
0
 
LVL 14

Expert Comment

by:Bill Ross
ID: 39200212
Hi,

The connection is being established prior to the form opening.  You cannot change the connection string when the connection is open.

Regards,

Bill
0
 
LVL 3

Author Comment

by:bfuchs
ID: 39202366
hi, tried the below but still getting the above error
    DoCmd.Close acForm, "Main"
    CurrentProject.Connection = CurrentProject.Connection & ";APP=" & GetUserName() & ";"
    DoCmd.OpenForm "Main"
would you know how can i set this connection property without opening the Main form, which is setup to open at startup?
0
 
LVL 14

Accepted Solution

by:
Bill Ross earned 500 total points
ID: 39202879
Hi,

I do not know how to modify connection properties using VBA in an ADP as the connection is instantiated when the db opens.  

You can do the following:

Open the ADP to the db window.
Click File, Connection
Click All
Click on Application Name
Click Edit Value
Enter a friendlyname.
Close and save your db.  

When in Activity Monitor this friendlyname will show.

Regards,

Bill
0
 
LVL 3

Author Closing Comment

by:bfuchs
ID: 39203063
Hi, that really helps, Thank you!
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

932 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

16 Experts available now in Live!

Get 1:1 Help Now