Developing Matt

My Technical Journal

How to view sql servers on my network

with 2 comments

There are lots of ways to skin a network.  Here are a few of them.  The first three are in order of preference.  After that it’s a crapshoot.  But I give you a hint on which one you should use below.

isql –L (uses DB-Library and acts like a 6.5 client and will expire)

osql –L  (uses odbc and will expire)

sqlcmd –L   *******   (this will give you the instances and fully supports 2005.  I also found a nice post about this utility here)

sqlPing  (I know nothing about this approach)

SQL-DMO (for 2000)

SQL-DMO object reference can be found in the binn directory of your sql server installation.
Dim i As Integer
Dim oNames As SQLDMO.NameList
Dim oSQLApp As SQLDMO.Application
Set oSQLApp = New SQLDMO.Application
Set oNames = oSQLApp.ListAvailableSQLServers()
List1.Clear
For i = 1 To oNames.Count
    List1.AddItem oNames.Item(i)
Next i

I’m not going to tell you the best one to use, but I’ll give you a hint.  Use the one with the stars behind it.

An aside, if you are running within sql server and not the command line you might need to turn on xp_mdshell.  But don’t do it on my watch.  The security implications are nasty:

–Turn on Advanced features
EXEC sp_configure ‘show advanced options’, 1
reconfigure
go
–Turn on xp_cmdshell
EXEC sp_configure ‘xp_cmdshell’, 1
reconfigure
go

–example: EXEC master..xp_CMDShell ‘OSQL -L’

Advertisements

Written by matt

August 3, 2009 at 12:47 pm

Posted in T-Sql

2 Responses

Subscribe to comments with RSS.

  1. I would also toss in Quest Discovery Wizard – it’s a totally free utility that scans your network and builds reports for you:

    http://www.quest.com/discovery-wizard-for-sql-server/

    Brent Ozar

    August 3, 2009 at 2:23 pm

    • thanks brent for stopping by and commenting!

      I will check it out.

      matt

      August 3, 2009 at 2:41 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: