March 31, 2011 at 9:26 am
Hi I have the list of Hostnames in a table and i need to loop to all Host names and get the info of IPAddress,OS_version,Instancenames,TCP Portnumbers of all the instances.. can some one guide me with step by step with Query to get this task complete.... Thanks in advance...
April 5, 2011 at 12:04 pm
It's a chicken/egg problem...how will you run a query against an instance of SQL Server on a given hostname if you do not know what instances exist on that host? If you're planning on assuming you can log into a default instance on that host...what if only named instances exist on that host?
It would be best to do this using WMI.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 15, 2011 at 9:32 am
This will work just perfectly for you.
I have tested it in 2008 and 2005.
-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO
CREATE TABLE #table([OUTPUT] VARCHAR(256));
INSERT INTO #table
( [OUTPUT] )
EXEC xp_cmdshell 'sc \\HOSTNAME query state= all';
SELECT * FROM #table WHERE [OUTPUT] LIKE '%MSSQLSERVER%'
ORDER BY [OUTPUT] ASC
DROP TABLE#table
PLEASE DISABLE [xp_cmdshell] ONCE YOU ARE DONE..IT COULD BE DANGEROUS TO LEAVE IT ON UNLESS YOU REALLY NEED TO HAVE IT TURNED ON.
QUIGROUP- Need a Certified experienced DBA for a project or troubleshooting? Need help with programming, database recovery, performance tuning, ETL, SSRS or developing new databases? Contact us.. 1-786-273-9809
April 15, 2011 at 9:36 am
Ysaias Portes-483038, your solution implies he knows an instance he can log into...all he has are host names.
I agree with your comment about xp_cmdshell though...avoid it!
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 15, 2011 at 9:44 am
Thanks for your input, but "SC.EXE" does not query instances, it queries Servers and enumerates all its services.
My Script lists all the services installed on a particular computer or server and stores them into a TEMP TABLE, then it filters them on SQL Server, that way he will know what SQL services are installed, therefore it will allow him to determine the instances.
QUIGROUP- Need a Certified experienced DBA for a project or troubleshooting? Need help with programming, database recovery, performance tuning, ETL, SSRS or developing new databases? Contact us.. 1-786-273-9809
April 15, 2011 at 10:06 am
Very nice, I completely missed that you were using sc 😀
I fixed up your results query to have it give the instance names instead of just rows with MSSQLSERVER which will only find SQL 2000 default instances:
CREATE TABLE #table([OUTPUT] VARCHAR(256));
INSERT INTO #table
( [OUTPUT] )
EXEC xp_cmdshell 'sc \\HOSTNAME query state= all';
WITH cte
AS (SELECT SUBSTRING(OUTPUT, CHARINDEX('(', OUTPUT) + 1, 255) AS name
FROM #table
WHERE [OUTPUT] LIKE 'DISPLAY_NAME: SQL Server (%'
)
SELECT SUBSTRING(name, 1, LEN(name) - 1) AS instance_name
FROM cte
DROP TABLE #table
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 15, 2011 at 10:09 am
I use the SQLPing 3.0 tool to look for server instances.
http://www.sqlsecurity.com/Tools/FreeTools/tabid/65/Default.aspx
April 15, 2011 at 10:12 am
PS for the record I still think this task would be better off done in PowerShell using WMI (or sc) to avoid having to turn on xp_cmdshell.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply