bfuchs
asked on
SQL Server Activity monitor issue
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?
Recently I realized that users that have access 2003, their host name are displayed something like "d7m6rnxx", is there any way to fix that?
How should they be displayed? In other words, what would you expect to see instead of "d7m6rnxx"?
ASKER
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.
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.
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
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
ASKER
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;Pe rsist Security Info=True;Data Source=sql\SQLEXPRESS;User ID=accessusers;Password=MY PWD;Initia l Catalog=MYDB;Data Provider=SQLOLEDB.1
", were exactly do i need to set the user name?
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;Pe
", were exactly do i need to set the user name?
Hi,
Change your connection string as follows:
"Provider=MSDataShape.1;Pe rsist Security Info=True;Data Source=sql\SQLEXPRESS;User ID=accessusers;Password=MY PWD;Initia l Catalog=MYDB;Data Provider=SQLOLEDB.1;APP=Yo urNameHere
",
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=MSDat aShape.1;P ersist Security Info=True;Data Source=sql\SQLEXPRESS;User ID=accessusers;Password=MY PWD;Initia l Catalog=MYDB;Data Provider=SQLOLEDB.1;APP=" & GetUserName() & ";"
Regards,
Bill
Change your connection string as follows:
"Provider=MSDataShape.1;Pe
",
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=MSDat
Regards,
Bill
ASKER
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
however i am getting the error below:
"Operation not allowed when object is open"
Please let me know how to proceed,
Thanks
Ben
Hi,
The connection is being established prior to the form opening. You cannot change the connection string when the connection is open.
Regards,
Bill
The connection is being established prior to the form opening. You cannot change the connection string when the connection is open.
Regards,
Bill
ASKER
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi, that really helps, Thank you!