?
Solved

SQL Server Activity monitor issue

Posted on 2013-05-22
12
Medium Priority
?
377 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
12 Comments
 
LVL 85
ID: 39189558
How should they be displayed? In other words, what would you expect to see instead of "d7m6rnxx"?
0
 
LVL 4

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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 4

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
 
LVL 4

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 4

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 2000 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 4

Author Closing Comment

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

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

764 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