Solved

SQL Server Activity monitor issue

Posted on 2013-05-22
12
375 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
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.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

695 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