Return MS-SQL database names to excel?

[Guest]

I have a workbook to which I want to return the database names residing in a
specific SQL database. I need the names in order to allow a user to choose a
database name. This name will then be inserted into a connection string
designed to import the data from the indicated database.

I am pasting in the basic connection string used once the database name is
known.
Also, if anyone knows how to translate this string into one that doesn't use
the array of arrays format...

Thanks in advance,
Mike


ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DRIVER=SQL
Server;SERVER=CONTROLSERV001\WINCC;UID=;APP=Microsoft Office
XP;WSID=THE_MAN;DATABASE=CONTROLSERV001_H2gen#pc1_TLG_S" _
), Array("_200409171428;Trusted_Connection=Yes")),
Destination:=Range("A1"))
.CommandText = Array( _
"SELECT Archive.ValueName, TagUncompressed.TimeStamp,
TagUncompressed.RealValue" & Chr(13) & "" & Chr(10) & "FROM
""CONTROLSERV001_H2gen#pc1_TLG_S_200409171428"".dbo.Archive Archive,
""CONTROLSERV001_H2gen#pc1_TLG_S_200409171428"".db" _
, _
"o.TagUncompressed TagUncompressed" & Chr(13) & "" & Chr(10) &
"WHERE Archive.ValueID = TagUncompressed.ValueID" & Chr(13) & "" & Chr(10) &
"ORDER BY Archive.ValueName, TagUncompressed.TimeStamp" _
)

 

[Jamie Collins]

I have a workbook to which I want to return the database names residing in a
specific SQL database. I need the names in order to allow a user to choose a
database name.

I assume you mean, 'return the database names residing on a specific
SQL Server.'

You can use ADO's OpenSchema method to do this e.g.

Option Explicit

Sub TestLateBound()

Const CONN_STRING As String = "" & _
"Provider=SQLOLEDB.1;" & _
"Data Source=MYSERVER;" & _
"User Id=MYLOGON;password=MYPASSWORD;"

Dim oConn As Object
Dim oRs As Object

Set oConn = CreateObject("ADODB.Connection")

With oConn
.CursorLocation = 3 ' adUseClient
.ConnectionString = CONN_STRING
.Open
Set oRs = .OpenSchema(1) ' adSchemaCatalogs
End With

Dim lngRows As Long
Dim lngCounter As Long
With oRs
lngRows = .RecordCount
For lngCounter = 0 To lngRows - 1
Debug.Print !CATALOG_NAME
.MoveNext
Next
.Close
End With
oConn.Close

End Sub

If you meant, 'return the table names residing in a specific SQL
Server database', you can also use OpenSchema (using adSchemaTables).
For details, see:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdcstschemaenum.asp

If you additionally need to enumerate SQL Servers, take a look at this
thread from the archive:

http://groups.google.com/[email protected]

Jamie.

--

 

[Salma]

Hi

Just wondering, why don't you use the WinCC OLEDB Provider to get the
data out? That way you wouldn't have to worry about the database
names. Makes my life a lot easier when reporting on WinCC systems.

cheers
Salma