Sample needed original name of DB on MS SQL Server

[MSchmidt]

in case of ODBC connection to a MS SQL Server we have following scenario:

Original name of the database on SQL Server: TestDB
name of the ODBC System DSN: TestDB_ODBC
Mapping in SQL.INI: remotedbname=test,dsn=TestDB_ODBC

and now the problem:
we need the original name of the DB on SQL Server inside a TD app, to make some queries in master DB .
(in sample scenario "TestDB")

f.e. SELECT compatibility_level FROM sys.databases where name = 'TestDB'

any idea?

regards and thanks in advance
M.Schmidt

[UVS]

in case of ODBC connection to a MS SQL Server we have following scenario:

Original name of the database on SQL Server: TestDB
name of the ODBC System DSN: TestDB_ODBC
Mapping in SQL.INI: remotedbname=test,dsn=TestDB_ODBC

and now the problem:
we need the original name of the DB on SQL Server inside a TD app, to make some queries in master DB .
(in sample scenario "TestDB")

f.e. SELECT compatibility_level FROM sys.databases where name = 'TestDB'

any idea?

regards and thanks in advance
M.Schmidt

Well my first thought is why name the remotedbname differently than the actual name on the server? But I'm sure you have a good reason.

you could query all the database names with their compatibility_level and walk through that..

SELECT name, compatibility_level FROM sys.databases
where name != 'master'
and name != 'tempdb'
and name != 'model'
and name != 'msdb'

HTH

[MSchmidt]

thank you for the answer.

of course a solution is to use the same name in the DSN Mapping in the sql.ini file.

this can be done, if we install our software the 1rst time at our customers.
but after some years the customer has changed the sql server from maybe 2005 to 2008.
now the adminstrator changes the ODBC DSN but not the name of the DSN.
so the enduser can use the same sql.ini with the "virtual" (remotedbname) name of the Database, he used since years.

and then the virtual name of the SQLDATABASE inside a TD App is not the original name of the DB.

Of course we could make a mapping and can save it in a SQL Table: virtual name=original name
but this has to be done manually

maybe someone knows a way or has a good idea.

my first idea was to use the gupta.ini file
but i don't know if this file has a future
and in my file i miss a DSN info of a DB i daily use.

i've searched with REGEDIT, but i cannot find any information in the registry, where the OS (WIN7 / 64) is saving the DSN informations,
especially the linked DB.

so in principle question is: where windows is saving DSN informations and how can i catch the DSN Data from inside a TD APP if i only have the remotedbname.

again thank you
M.Schmidt

[MSchmidt]

now i've found an entry in the registry:

in section
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBC.INI
there are all my 32 bit ODBC connections i've configured on my system.
the missing one as described above, too (missing in gupta.ini)

ok
so via SQL.ini i will try to catch the data

greetings
M.Schmidt

[MSchmidt]

here is the simple solution:

SELECT DB_NAME() ;

this delivers the original name of the database on SQL Server independ on the remotedbname defined in SQL.INI

regards
M.Schmidt

[UVS]

That is even better. In such that you are asking the object on the server what is it names, instead of trying to deduce it through configuration files.

MartinD

[Jeff Luther]

Thanks from me too, M.Schmidt! I didn't know you didn't need a FROM clause with MS SS (I was looking for something in MS SS like Oracle's "DUAL" to select from) but as M. wrote "select DB_NAME()" works fine as is, as it does in TD:

Call SqlPrepareAndExecute( hSql, 'select DB_NAME() INTO :s' )
Call SqlFetchNext( hSql, nInd )