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:
- 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
- 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:
- 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.
- 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
File Created: 2001:11:08:12:40:56
Last Updated: 2001:11:08:12:40:56