Unable to view all current database sessions

KusMoG

Hi all,

Our company just upgraded our users' Navision client from Navision 4.0 SP3 to the Navision Classic Client from Dynamics NAV 2009 R2, both using the client with SQL Server option. Our users have been able to resume their usual functions. However ever since the conversion, super users that were able to view all the currently logged in sessions via File--> Database--> Information --> Sessions tab--> Current Sessions (F6 or drill down to open Database Sessions table) on the old Navision 4.0 SP3 client, using the Navision Classic Client from NAV 2009 all we are able to see is one User ID, despite it listing the correct amount of current sessions (which is more than one user).

Unlike others who have posted this similar problem, the User ID that I see on my current database sessions isn't my own session. Rather, it's apparently the first entry on our list of database users. I don't have any filters on the table. We have the traceflag 4616 set already as it was required for us to even log into Navision using the SQL server.

Could any please help?

My thanks in advance!

ara3n

I suggest to check the sql view session on the db and compare it with Cronus 2009 R2 database. See if they are the same.

Also I don't think you need Trace flag 4616 any longer for NAV 2009. There was a blog about that. You should remove it.

rmpatel22

If you login with user id which is having "sysowner" rights in SQL server then you will be view all sessions.

Not sure it will resolve your problem, but just check it.

KusMoG

Thanks for your suggestions thus far.

@ara3n: It seems that I don't need the trace flag 4616 anymore, although removing it doesn't resolve the issue with viewing all the current database sessions under SQL.

@rmpatel22: while I don't specifically see a "sysowner" under Permissions, I've tried logging in with a user that was granted serveradmin and sysadmin rights, and I'm still unable to view all current sessions.

As a matter of fact, during the process of removing the trace flag 4616, I was forced to restart the SQL service to complete the change so it comes into effect. Naturally, it forced all users that were logged into the SQL database off of it. I still see the correct number of users currently logged in to the database under current session as I did before, but now I don't see ANY users listed on Database Sessions table when trying to view current database sessions. Before I was able to see one (and it wasn't my own session).

Any other ideas?

FDickschat

It seems to be client build no. dependent what exactly you have to configure. For the last DB where we had this issue we needed to set trace flag 4616 although the client 2009 SP1 does not require it anymore. "GRANT VIEW SERVER STATE TO [public]" did not help.

Search the forum for other tips. It has been discussed several times..

KusMoG

@FDickschat I actually have searched the forum before starting my own thread and that's when I realized my situation differs from the ones posted before.

What's weird is that users with db_owner permissions are able to view the current sessions of our test SQL database from the NAV 2009 R2 Classic client. This test database hasn't been converted for use with the NAV 2009 client, but rather our older Navision 4.0 SP3.

However, as I mentioned before, those same users can't view the current sessions for the production database, which was converted for NAV 2009.

I noticed on Navision 4.0 SP3, "Microsoft Business Solutions-Navision" is displayed on the Title Bar when you use that version. However, on NAV 2009 R2 (using the classic client), it's "Microsoft Dynamics NAV Classic". This may seem kind of silly to suggest but does anyone think that may play a role into the problem I'm having? Looking at the code for the the Session view from the production and test database in SQL Server 2005 Management Studio, I see the program_name referencing 'Microsoft Business Solutions-Navision client' on both.

Any thoughts?

FDickschat

KusMoG wrote:

Looking at the code for the the Session view from the production and test database in SQL Server 2005 Management Studio, I see the program_name referencing 'Microsoft Business Solutions-Navision client' on both.

That's odd. The session view will just read data from master.sysprocesses. You can see the result from sysprocesses in the Activity Monitor in SSMS. At least 2009 SP1 is displayed as "Microsoft Dynamics NAV Classic client". I currently have no R2 client to check.
This is the part in session view which does this:

CAST (RTRIM(SP.[program_name]) AS NVARCHAR(64)) COLLATE Latin1_General_CI_AS AS "Application Name"

KusMoG wrote:

What's weird is that users with db_owner permissions are able to view the current sessions of our test SQL database from the NAV 2009 R2 Classic client. This test database hasn't been converted for use with the NAV 2009 client, but rather our older Navision 4.0 SP3.

??? If you can access the DB with the R2 client then it was already converted to 2009 R2.

Why db_owner should help I have no idea. You coud try to add read permissions to table Session in NAV and synchronize security.
Btw, which security model are you running?

stryk

Hmmm ... and does this help:

GRANT SELECT ON [Session] TO [PUBLIC]

... ?

KusMoG

Thanks to everyone for their input. I think I've finally managed to solve my little dilemma.

@FDickschat: Thanks for bringing up the Activity Monitor. It was a helpful hint. For the record, NAV 2009 R2 also displays "Microsoft Dynamics NAV Classic client".

Here's what I did to solve my problem... (at least I hope it's the solution...):
To provide some background we are using the following:
-NAV 2009 R2's Classic client with Microsoft SQL Server (not native)
-Microsoft SQL Server 2005

We were previously using:
-Navision 4.0 SP3 with Microsoft SQL Server Option (again not native) using the same SQL Sever 2005

1. Open SQL Server Management Studio and navigate to the Session table
SQL Server-->Database-->Database name-->Views-->dbo.Session
2. Right-click on dbo.Session, go to Script View as--> ALTER To-->New Query Editor Window
3. Find the following line:
WHERE (SP.ecid = 0) AND (SP.program_name = 'Microsoft Business Solutions-Navision client')

and change 'Microsoft Business Solutions-Navision client' to 'Microsoft Dynamics NAV Classic client'.

That should solve why the Session virtual table in Navision wasn't populating the list of current users. But I ran into another error. If I just run this query, after restarting Navision I'll get the following error:

The Session table contains a Microsoft Dynamics NAV Classic field data type that is not compatible with the SQL Server data type:

Field: Wait Time (ms)
Type: Integer
SQL type: BIGINT

This where NavIndia (Sandeep) from this forum's advice came in handy (see his thread here: http://www.mibuso.com/forum/viewtopic.php?f=23&t=15451&start=0)... On to step 4!

4. Change the code
SP.[waittime]
to
CAST(SP.[waittime] AS INT)

5. Press F5 to execute the query.

and that should be it. I was able to view the current logged in database sessions without coming across any errors. Hopes this helps anyone else having this problem.

Thanks again everyone for your suggestions and time. I really appreciate it.

DenSter

What you can also do is copy the Session view from a standard Cronus 2009 SP1 database, and replace the one in your database.

ara3n

DenSter wrote:

What you can also do is copy the Session view from a standard Cronus 2009 SP1 database, and replace the one in your database.

That was my first reply but I guess he didn't understand it.

KusMoG

@DenSter and ara3n: Yeah, thanks for that suggestion you two. You're right, I didn't understand it. But I went ahead and ran the installation from the DVD today though and I noticed the Cronus demo database you were referring to under the SQL Server Option install. So I went and attached the Cronus mdf file to our SQL Server database folder and compared the Session view as per your suggestion. They're virtually the same except for two lines:

Our database (prior to any fixes):
CASE WHEN SP.[blocked] <> 0 THEN SP.[waittime] ELSE 0 END AS [Wait Time (ms)], CAST(SP.blocked AS INTEGER) AS [Blocking Connection ID],

Cronus NAV 2009 R2:
CAST(CASE WHEN SP.[blocked] <> 0 THEN SP.[waittime] ELSE 0 END AS INTEGER) AS [Wait Time (ms)], CAST(SP.blocked AS INTEGER) AS [Blocking Connection ID],

and...

Our database (prior to any fixes):
WHERE (SP.ecid = 0) AND (SP.program_name = 'Microsoft Business Solutions-Navision client')

Cronus NAV 2009 R2:
WHERE (SP.ecid = 0) AND (SP.last_batch >= CONVERT(DATETIME, '2000-01-01 00:00:00', 120))

There's probably no harm in trying the Cronus database coding on our test database to see what the effects are... but it's Super Bowl time. lol... maybe later.
I am curious as to why we even refer to the program name specifically like that to begin with. It makes us prone to these silly NAV problems with viewing sessions.

Also, would anyone care to explain what the code (SP.last_batch >= CONVERT(DATETIME, '2000-01-01 00:00:00', 120) means?

Thanks everyone.

Doomhammer

please help

we upgraded our customer from native to SQL database
database version is 4.0, client is 2009R2 classic, SQL 2008R2
users are unable to see session list - they can see only their session.
please, help me, what should I set up to enable them to view all sessions. I do not want to give them SYSADMIN
I tried to give permission to see server state to public, public can select from view session, I created role, which contains permission to Session virtual table in NAV and performed synchronization.
trace flag 4616 is off. I copied the Session view from cronus 2009 R2. nothing did help, unless user is sysadmin, he cannot see sessions.

thanks in advance.

ara3n

run this on sql

GRANT SELECT ON [Session] TO [PUBLIC]

Doomhammer

I tried it, I checked permissions on session view and public has select enabled

ara3n

run this code and restart sql server

GRANT VIEW SERVER STATE TO [public]

or

GRANT VIEW SERVER STATE TO [Mydomain\Myuser] with grant option;
GO

Doomhammer

there is neccessary to restart SQL service after granting view server state? maybe this is what I omited because I executed grant command but did not restart service. Iľl try it. thanks.

Doomhammer

restart of SQL service did not help.

ara3n

Did you try the second option?

Doomhammer

yes, I did. and it did not work.

ara3n

i suggest to get a sql person to look at your server. i cant think of anything else.