How to find the databases on a server

[Dom]

I'm trying to write an all-purpose program that will:

1. list all the servers on the network.
2. list all the databases on any one server once it is clicked.
3. list all the tables in a database once it is clicked.
4. list all the columns in a table once it is clicked.

The first step I can do with SqlDataSourceEnumerator. But I get only
the names of the servers, not the instance names.

My intention was to call SqlConnection.Open() on each server returned
by the Enumerator, but this fails, apparently because the enumerator
returns the name "CASSIUS" and not "CASSIUS\SQLEXPRESS".

How can I do what I want?

 

[Dom]

I'm trying to write an all-purpose program that will:

1. list all the servers on the network.
2. list all the databases on any one server once it is clicked.
3. list all the tables in a database once it is clicked.
4. list all the columns in a table once it is clicked.

The first step I can do with SqlDataSourceEnumerator. But I get only
the names of the servers, not the instance names.

My intention was to call SqlConnection.Open() on each server returned
by the Enumerator, but this fails, apparently because the enumerator
returns the name "CASSIUS" and not "CASSIUS\SQLEXPRESS".

How can I do what I want?

Just replying to myself to get this post pushed to the top again. See
if I get any responses this time. Then I'll give up.

 

[William Vaughn]

You're on the right track with the Factory Classes. I have a full-blown
example in the book but the part you might find useful is here:

Dim factory As DbProviderFactory =
DbProviderFactories.GetFactory("System.Data.SqlClient")
Dim dsE As DbDataSourceEnumerator =
factory.CreateDataSourceEnumerator
Dim dtInstances As DataTable = dsE.GetDataSources
DataGridView1.DataSource = AddServiceName(dtInstances)

The examples also illustrate use of the GetSchema method off the Connection
class. It can enumerate vitrually anything except the day the table was last
screwed up by the developer down the hall.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)

 

[Dom]

You're on the right track with the Factory Classes. I have a full-blown
example in the book but the part you might find useful is here:

Dim factory As DbProviderFactory =
DbProviderFactories.GetFactory("System.Data.SqlClient")
Dim dsE As DbDataSourceEnumerator =
factory.CreateDataSourceEnumerator
Dim dtInstances As DataTable = dsE.GetDataSources
DataGridView1.DataSource = AddServiceName(dtInstances)

The examples also illustrate use of the GetSchema method off the Connection
class. It can enumerate vitrually anything except the day the table was last
screwed up by the developer down the hall.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speakerwww.betav.comwww.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visitwww.hitchhikerguides.netto get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)

Thanks for this, but I think it is what I have already done. In
CSharp, this becomes.

DataTable d = SqlDataSourceEnumerator.Instance.GetDataSources();

The problem is that "d" only gives me the ServerName. It does not
give me the InstanceName. Now, when I use "d" to create the
Connection String so that I can use GetSchema, I get an exception.

Is there any way around that?

 

[William Vaughn]

Ah, I just re-ran the example and it the dbDataSourceEnumerator
GetDataSources returns the system name and instance name of each provider.
Note that I'm using the SqlClient provider. No, I don' think the other
providers fully support this functionality.


--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

 

[Dom]

Are you sure it's the SqlClient provider, and not just
System.Data.Sql? BTW, thanks for all your help, no one else is even
answering.

Dom

 

[William Vaughn]

Yup. The entire sample is on my book's DVD. It also has an application that
demonstrates how to use the GetSchema method.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)