Database Virtual Table in SQL

I have a routine thats trying to find the name of the database that the user is currently logged into. In the Database table, I am trying to find a record with the “My Database” field having a TRUE value. This works fine for all client sessions except for one new client where this “My Database” field is always returning FALSE. Can someone tell me why this is field has a FALSE value just for this one client? or Can someone tell me how the virtual table 2000000048 “Database” gets populated and how the “My Database” field gets ticked? Thanks P.S. This particular problem is on Navision 3.60 SQL

First I’ll take a stab at why that particular client is not getting a TRUE in any of the databases. If you have a case-insensitive server AND that user opens the database by typing the database name not exactly but matching case-insensitive, then this situation will occur I think. E.g. If the database name is ‘Database’ and the user opens as ‘database’, a case-insensitive server (but not a case-sensitive server) will allow that to be used in the connection and will match ‘Database’ case-insensitive, successfully opening the database. But Navision when enumerating the database names for the VT does an exact comparison of the databases on the server with the name used and they will not match; therefore there will be no ‘My Database’ checked. So try re-opening the database on that client, using the exact name. As for populating this VT with the database list, it is done with a main query on master.sysdatabases and for each database there is a check that it is a Navision database and is accessible by the current user. (Note: from 3.70A Navision always reads the actual database name back from the server so in the above example, Navision would store ‘Database’ not ‘database’. So the problem would not occur.)

Hi Ralph (maybe I can help you for a change…), Why don’t you use the CONTEXTURL function to retrieve the current database, you will need to do a bit of string manipulation to strip out the irrelevant bits, but I know that is no problem for a man of your talents. Otherwise give me a call, I am sure we have the code floating around somewhere, or you could even have a look in our form 330 from the backup you have where we have used this functionality. Meint

Ah, found it somewhere in an old thread, can’t claim credit for the code though. We found this method to work a lot quicker as well… Context := CONTEXTURL; ServerName := COPYSTR(Context,STRPOS(Context,‘servername=’)+11); // Delete the “prefix” ServerName := DELSTR(ServerName,STRPOS(ServerName,’&’)); // Delete trailing p. DatabaseName := COPYSTR(Context,STRPOS(Context,‘database=’)+9); // -do- DatabaseName := DELSTR(DatabaseName,STRPOS(DatabaseName,’&’)); // -do- Meint

Thanks guys for your answers : Robert’s suggestion did the trick - the user had written the database name in lower case letters. But thanks Meint for the CONTEXTURL suggestion, useful bit of code to remember! Many thanks again.