Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

NO. of instances Expand / Collapse
Author
Message
Posted Tuesday, April 23, 2013 5:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 13, 2013 11:16 PM
Points: 7, Visits: 20
can any one suggest me that how to find out the No. of instances in a server using query
Post #1445358
Posted Tuesday, April 23, 2013 7:58 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:32 PM
Points: 13,067, Visits: 11,903
saptapavank (4/23/2013)
can any one suggest me that how to find out the No. of instances in a server using query


Interview question?


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1445439
Posted Tuesday, April 23, 2013 8:44 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 12:24 PM
Points: 514, Visits: 1,717
DECLARE @GetInstances TABLE
( Value nvarchar(100),
InstanceNames nvarchar(100),
Data nvarchar(100))

Insert into @GetInstances
EXECUTE xp_regread
@rootkey = 'HKEY_LOCAL_MACHINE',
@key = 'SOFTWARE\Microsoft\Microsoft SQL Server',
@value_name = 'InstalledInstances'

Select InstanceNames from @GetInstances
Post #1445487
Posted Tuesday, April 23, 2013 9:21 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:50 AM
Points: 12,877, Visits: 31,792
Geoff A code example worked fairly well on my machine.

My dev machine has four instances installed:
2008 standard (default).
2005 Express.
2008R2 Developer.
2012 Developer.

The above query seems to have missed my 2005 SQL Express instance, which i clearly see did not place a key in the SOFTWARE\Microsoft\Microsoft SQL Server\InstalledInstances folder of the registry.

do you think it's caused by 2005 not putting a key there, or that Express instances don't put a key there?

i tried Netstat -a -b on a command line, and i see multiple sqlserver.exe's running and listing to various ports, but can't map them directly to their instances.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1445517
Posted Tuesday, April 23, 2013 9:40 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:50 AM
Points: 12,877, Visits: 31,792
ok by looking at a different registry key and looking for Service names, i think this finds them all on my sample machine:
/*
ResultsId ResultsText
233 MSSQL$SQL2005
234 MSSQL$SQL2008R2
235 MSSQL$SQL2012
237 MSSQLSERVER
*/
declare @Results TABLE (ResultsId int identity(1,1) not null primary key,
ResultsText varchar(200) )

insert into @Results(ResultsText)
EXECUTE master..xp_regenumkeys 'HKEY_LOCAL_MACHINE' , 'SYSTEM\CURRENTCONTROLSET\SERVICES\'

select * from @Results
WHERE ResultsText = 'MSSQLSERVER'
OR ResultsText LIKE 'MSSQL$%'



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1445529
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse