listing SQL Server databases on a network

[Steve]

I would like to provide users of a client program a list of databases on the
network that match a certain pattern, like "%frp%". I have seen a list of
databases in the Windows XP ODBC configuration tool. What I would like to
know is can I get that information through a query to one of the databases,
or is there a control or application on the client machine that I can call
to get the list?

Does the thread "Databases not showing up in Enterprise Manager" refer to
what I am trying to do?

Best regards,
Steve Caldwell

[Jens]

Re: listing SQL Server databases on a network

Hi Steve,

what about this:


Create Table #Serverinformat ion
(
Information VARCHAR(500)
)

INSERT INTO #Serverinformat ion
EXEC('master..X P_cmdshell ''OSQL.EXE -L''')

Select * from #Serverinformat ion
Where Information like 'Somepattern'


HTH, jens Suessmeyer.

[Steve]

Re: listing SQL Server databases on a network

Jens wrote:
[color=blue]
> Hi Steve,
>
> what about this:
>
>
> Create Table #Serverinformat ion
> (
> Information VARCHAR(500)
> )
>
> INSERT INTO #Serverinformat ion
> EXEC('master..X P_cmdshell ''OSQL.EXE -L''')
>
> Select * from #Serverinformat ion
> Where Information like 'Somepattern'
>
>
> HTH, jens Suessmeyer.
>[/color]
Jens,

Looks promising. I presume that "OSQL.EXE" is an either an application or an
executable ActiveX object. If "OSQL.EXE" is not on our standard
workstations, then I will look on the server and use a query. Hopefully,
"OSQL.EXE" has an exposed interface to VBA; that would be ideal.

Best regards,
Steve Caldwell

[Steve]

Re: listing SQL Server databases on a network

Steve wrote:
[color=blue]
> Jens wrote:
>[color=green]
>> Hi Steve,
>>
>> what about this:
>>
>>
>> Create Table #Serverinformat ion
>> (
>> Information VARCHAR(500)
>> )
>>
>> INSERT INTO #Serverinformat ion
>> EXEC('master..X P_cmdshell ''OSQL.EXE -L''')
>>
>> Select * from #Serverinformat ion
>> Where Information like 'Somepattern'
>>
>>
>> HTH, jens Suessmeyer.
>>[/color]
> Jens,
>
> Looks promising. I presume that "OSQL.EXE" is an either an application or
> an executable ActiveX object. If "OSQL.EXE" is not on our standard
> workstations, then I will look on the server and use a query. Hopefully,
> "OSQL.EXE" has an exposed interface to VBA; that would be ideal.
>
> Best regards,
> Steve Caldwell[/color]

I looked here: <http://www.sqlteam.com/item.asp?ItemID =5403>. Looks like
between the help in the newsgroup and this, I should be successful.
Steve

[Jens]

Re: listing SQL Server databases on a network

Didn´t know that you were aware of using SQl DMO (the link you
posted). I thought you are just able to use the TSQL options for that,
therefore the OSQL was the most appropiate solution for me.

Jens Suessmeyer.

[Steve]

Re: listing SQL Server databases on a network

Jens wrote:
[color=blue]
> Didn´t know that you were aware of using SQl DMO (the link you
> posted). I thought you are just able to use the TSQL options for that,
> therefore the OSQL was the most appropiate solution for me.
>
> Jens Suessmeyer.
>[/color]
I know that I had seen SQLDMO sometime past, but didn't really know anything
about it until you made your post. That started me looking. I did a Google
search and the link I posted was one of the results.

As it turns out, I was able to pull the whole thing together and now have an
nicely encapsulated form class that I can use in just about any VB/VBA
application to display a list of servers. The way it works now, it loads
the list of servers on loading, then the list is filtered using a regular
expression to show a specialized subset of server names. The reg.exp. is
set as a property of the form. When the user selects a server from the list
box, that is exposed as another property of the form. I went ahead and made
the entire list another property so that the list could be retrieved
without ever actually raising the form. It turned out nicely.

Thanks for the lead.

Best regards,
Steve