Search code examples
excelvbaodbcdatabase-connectionnetezza

Find all database names on a Netezza server

I want to find out the names of all DB instances on a Netezza DB server. I am using the following code:

Set objConn = CreateObject("ADODB.Connection")
Set objRec = CreateObject("ADODB.Recordset")

objConn.open "Driver={NetezzaSQL};servername=xx.xx.xx.xx;port=5480;database=_v_database;username=pankaj;password=xxx_333"
sqlQuery="select database from _v_database"
objRec.open sqlQuery, objConn

value = objRec.fields.item(0)
msgbox Value

I am able to connect to individual DBs and able to fetch data. However, when I try to run the above code I am getting "Database _V_DATABASE does not exist" error. Can anyone help me in finding all the instances on a particular server - doesn't have to be strictly in VBA.

Solution

  • Found the solution - I used database=SYSTEM instead of database=_v_database in the connection string.

    Set objConn = CreateObject("ADODB.Connection")
Set objRec = CreateObject("ADODB.Recordset")

objConn.open "Driver={NetezzaSQL};servername=xx.xx.xx.xx;port=5480;database=SYSTEM;username=pankaj;password=xxx_333"
sqlQuery="select database from _v_database"
objRec.open sqlQuery, objConn

value = objRec.fields.item(0)
msgbox Value