List Databases on a Server

Keywords:  list databases on a server sql odbc ole

---

Question:

I request help from the winbatch user world : I would like to know the name of all the databases on an server (the sql statement is simple "select name from sysdatabase", on the master). I would like to execute it knowing only the userid/password and no DSN. In the ODBC extender I have to specify a DSN to connect to a database. Is there a solution to do this (with OLE or ...) ?

A great thank for your help

Answer:

1. Use OLE:

   target = "C:\TEMP\tables.xml
   ;To Get at Tables/Links/Views
   adSchemaTables=20
   
   ;establish a connection string to the database (the example below is ACCESS)
   cConn = "Provider=MicroSoft.Jet.OLEDB.4.0; Data Source=%source%"
   
   DB = ObjectOpen("ADODB.Connection")
   DB.Open(cConn)
   RS = DB.OpenSchema(adSchemaTables)
   RS.Save("%Target%",1)
   ObjectClose(RS)
   DB.close()
   ObjectClose(DB)
   
   ; you then have an XML file with all tables/views
   

2. The ODBC extender has the function qDriverCon, which can connect to a datasource without a DSN.

   qDriverCon is used to connect to a data source using a connection string. qDriverCon is used instead of qConnect for the following reasons:

   • To let the application use driver-specific connection information.
   • To request that the driver prompt the user for connection information.
   • To connect without specifying a data source.
   The connection string may include any number of driver-defined keywords. The driver defines which keywords are required to connect to the data source.

Question (cont'd):

Thanks, I would like to use the qDriverCon function, I am building the connect string like this : cConnect="DRIVER=SQL Server;server=(local);user=sa;Pasw= ;database=opconxps" The server and database parameters are correct, but I should be happy if someone could give me the names of the keywords for login Id and password (I tryied Login ID, Login, ID, password, ...) when I execute ret=qDriverCon(hdbc, cConnect, 2) I can see that server and database fields are well filled but not login id and password

PS with OLE what do it have to put for "source" ?

Answer:

1. In OLE source is the same as datebase= in ODBC, also pw and user.

   The advantage of OLE and OPenSchema are:

   • little or no overhead
   • data is written to a file can be re-seldted, sorted and output to a text file or any other data source.
   • OLE/ADO gives the data type i.e. Table or View, which I do not believe is suppoted in straight ODBC (unless specifically requested)
   If you have MDAC installed there is an ADO help file which gives several examples of connecting with SQL server.

2. Also here is a link to the SQL Server documentation, for the connection strings....

   http://msdn.microsoft.com/library/psdk/sql/od_odbc_c_99yd.htm 
   

---

Article ID:   W14908