November 17, 2004 at 2:56 pm
I know there's gotta be a way to do this: how can I programatically (in a script, for e.g.) detect all the SQL Servers on my network?
Thanks.
-- Mark K.
November 18, 2004 at 1:44 am
ONe way could be to pipe the result of
EXEC master.dbo.xp_cmdshell 'osql -L'
to a table and analyze it thereafter.
AFAIK, this will work as long as the servers are not hidden, listen on the default port and are running at all.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
November 18, 2004 at 2:55 am
Check out SQL Scan which is one of three SQL Server 2000 Security Tools available from Microsoft:
Paul Micklethwaite
November 18, 2004 at 10:09 pm
Just whipped this out, Try this.....Returns a rowset of servers.
--- Retrieve SQL Server List Via SQL-DMO Object Method call
CREATE PROCEDURE GetSQLServerList ()
AS
DECLARE @objSQLDMO INT
DECLARE @objList INT
DECLARE @ServerCount INT
DECLARE @objMethod VarChar(20)
DECLARE @ServerName VarChar(30)
DECLARE @rc INT
DECLARE @serverTable Table ( Server VarChar(30) )
EXEC sp_OACreate 'SQLDMO.Application', @objSQLDMO OUTPUT
EXEC sp_OAMethod @objSQLDMO, 'ListAvailableSQLServers', @objList OUTPUT
EXEC sp_OAMethod @objList, 'Count', @ServerCount OUTPUT
WHILE @ServerCount > 0
BEGIN
SELECT @objMethod = 'Item(' + CAST(@ServerCount AS VarChar) + ')'
EXEC sp_OAMethod @objList, @objMethod, @ServerName OUTPUT
INSERT INTO @serverTable (Server) VALUES(@ServerName)
SELECT @ServerCount = @ServerCount - 1
END
EXEC sp_OADestroy @objList
EXEC sp_OADestroy @objSQLDMO
SELECT Server FROM @serverTable
GO
November 19, 2004 at 10:07 am
Nether osql -l or SQL DMO will properly detect a cluster instance name. It will pick up the active node name but not the actual cluster name.
wes
November 19, 2004 at 12:14 pm
OSQL -L and ListAvailableSQLServers list only servers that advertise on the subnet, not all of them. I usually do a 2-step thing.:
1. Creating a short script that dynamically composes a set of statements each one for each Windows server. It could be a long list of statements but it is OK and quick. If you don't have a list of names of all your servers, use range of IP in a loop. The resulting statements should look like this, depending on your design. Because those are individual batches the failure to connect for one will not stop the next one from being processed. You will be able to get things that are running on 1433. Let me know if you need help composing those statements dynamically. Do not forget that to insert the single quote in the resulting statement as a string you need to have 4 single quotes in the script like ''''
Results of running a step one should look:
Update dbo.[MyTableToStoreResults] Set SQLversion = (SELECT * FROM OPENROWSET('SQLOLEDB','Provider=SQLOLEDB;Server=MyServerName1;Trusted_Connection=yes','select @@version')) WHERE NBName = 'MyServerName1'
GO
Update dbo.[MyTableToStoreResults] Set SQLversion = (SELECT * FROM OPENROWSET('SQLOLEDB','Provider=SQLOLEDB;Server=MyServerName2;Trusted_Connection=yes','select @@version')) WHERE NBName = 'MyServerName2'
GO
2. Copy those resulting batches from Results Window of Query Analyzer to Query Window. Run when logged to the computer as a domain administrator. Leave overnight. Come in the morning and harvest results.
3. Optional: you may store and analyze error messages. For example, if it says "Login Failed" then the SQL server exist on the machine but Domain Admins don't have rights. "Does not exist or access denied" may mean No SQL Server or Server is Off the Network or Running on another port or port is not open
Regards,Yelena Varsha
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply