• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 594
  • Last Modified:

Find database name via ODBC connection

I am wondering how to retreive what database is in use in ODBC connections

I know the ODBC connection name
I need to use this name to retreive the database name from ODBC in a powershell script.
I have found that i can retreive ODBC connections with the get-childitem.

But i need to use this to only retreive the database name the ODBC connection uses, and output it in a file or something similar. This is so i can detach these databases using a script afterwards.


Get-childItem -path "hklm:\software\odbc\odbc.ini\"

Open in new window

0
frankEQS
Asked:
frankEQS
1 Solution
 
DavidMorrisonCommented:
if you want to return it from the database itself try:

SELECT DB_NAME()
0
 
marek1712Commented:
Something like this?
$in = Read-Host
$pt = "HKLM:\software\ODBC\ODBC.INI\$in"
$db = (Get-ItemProperty -Path $pt).Database
"$in, $db" >> c:\db_list.txt

Open in new window

0
 
Anthony PerkinsCommented:
>>I am wondering how to retreive what database is in use in ODBC connections<<
Have you considered that the database name may not even be in the connection string.  It may be using the default database for that login..
0
Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

 
frankEQSAuthor Commented:
we have a program, that uses ODBC to connect to IIS. in the ODBC connection we always refer to a database created by us. This database usualy is the same name as the ODBC connection, but there are deviation.

These databases also always starts with the characters "eqs_"

This way I should be able to get the databases out of the ODBC config file and automaticaly detach these, and move them to another server.

Marek1712: I will try your solution soon.
0
 
frankEQSAuthor Commented:
Im not getting this code to work how i want.

In the foreach loop i want to pick out each line of the $dbnavn variabel, and place it in the $db variabel.

The code before is for getting the ODBC names out of the ODBC info which has the EQS characters in it. Ofcourse there may be a better way of doing this. ( i am a noob :P)

anyway when i reach the foreach loop im getting a error:

Get-ItemProperty : Cannot find path 'HKLM:\software\odbc\odbc.ini\eqs_hemit                                                          
                         hklm:\software\odbc\odbc.ini\eqs_test                                                                        
            ' because it does not exist.
At C:\MyScripts\finn_odbc_ini.ps1:32 char:28
+     $db = (Get-ItemProperty <<<<  -Path "$dbnavn").Database
    + CategoryInfo          : ObjectNotFound: (HKLM:\software\...               :String) [Get-ItemProperty], ItemNotFoundException
    + FullyQualifiedErrorId : PathNotFound,Microsoft.PowerShell.Commands.GetItemPropertyCommand
 

Seems like it reads the whole $dbnavn variabel, not only line by line?


Get-childitem -path "hklm:\software\odbc\odbc.ini\" | select-object Name | out-file dbsticache.txt
Get-Content dbsticache.txt | where { $_ -match "eqs"}  | set-Content -path dbfullsticache.txt

Get-Content dbfullsticache.txt | Foreach-Object { $_ -replace "HKEY_LOCAL_MACHINE", "hklm:"} | Set-Content dbfullsti.txt

# remove cache files

remove-item dbsticache.txt
remove-item dbfullsticache.txt

$dbnavn = get-content dbfullsti.txt

foreach ($dblinje in $dbnavn) {

    $db = (Get-ItemProperty -Path "$dbnavn").Database
       
    }

Open in new window

0
 
frankEQSAuthor Commented:
Nevermind

I found out i needed to trim the end of each line before trying to use get-itemproperty
foreach ($dblinje in $dbnavn) {
    
    $dblinje = $dblinje.trimEnd()
    $db = (Get-ItemProperty -Path "$dblinje").Database

}

Open in new window

0
 
marek1712Commented:
I'm glad you've managed to solve the issue.
I couldn't help you earlier as I don't have that much free time in my job :)
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now