Solved

SQL Server Activity monitor issue

Posted on 2013-05-22
12
367 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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

In this article I will describe the Backup & Restore 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.
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.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how the fundamental information of how to create a table.

708 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

18 Experts available now in Live!

Get 1:1 Help Now