Solved

SQL Server Activity monitor issue

Posted on 2013-05-22
12
372 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 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

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

Author Closing Comment

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

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
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…

809 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