Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
How to get SQL server short name used in Registry?

已答复 How to get SQL server short name used in Registry?

  • Tuesday, August 09, 2011 1:37 AM
     
     
    Sign In to Vote
    0

    I installed a SQL server 2008, and the registry key is HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER

    then I installed a SQL server 2008 R2, and the registry key is HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MYINST 

     

    Please pay attention to the last key, it is combined by {SQL server version name}.{instance name}, here SQL server 2008 is MSSQL10 and R2 is MSSQL10_50.

    My application will support SQL server 2008 and plus, so how can I get this short name(e.g. MSSQL10 or MSSQL10_50)? Where can I get this "name" so that I can use this information to combine the registry key string and access the registry to get what I need. 

     

All Replies

  • Tuesday, August 09, 2011 4:57 AM
     
      Has Code
    Sign In to Vote
    0

    Hi,

    So if i understand your question properly what you want is values without instance name, as you mentioned above:

    For SQL 2008 Default Inatnce it should be MSSQL10

    For SQL 2008 Named Instance it should be MSSQL10_50

    What you need to do is play with te registry key like below.

     

    		DECLARE @IMAGE_PATH NVARCHAR(100)            
		DECLARE @INSTANCE_NAME NVARCHAR(100)            
		DECLARE @INSTANCE_NAME1 NVARCHAR(100)  
		DECLARE @SYSTEM_INSTANCE_NAME NVARCHAR(100)            
		
		SET @INSTANCE_NAME = COALESCE(CONVERT(NVARCHAR(100), SERVERPROPERTY('INSTANCENAME')),'MSSQLSERVER');            
		IF @INSTANCE_NAME!='MSSQLSERVER'            
		SET @INSTANCE_NAME=@INSTANCE_NAME            
	 
  	SET @INSTANCE_NAME1= COALESCE(CONVERT(NVARCHAR(100), SERVERPROPERTY('INSTANCENAME')),'MSSQLSERVER');            
		IF @INSTANCE_NAME1!='MSSQLSERVER'            
		SET @INSTANCE_NAME1='MSSQL$'+@INSTANCE_NAME1            
			
		EXEC MASTER.DBO.XP_REGREAD N'HKEY_LOCAL_MACHINE', N'SOFTWARE\MICROSOFT\MICROSOFT SQL SERVER\INSTANCE NAMES\SQL', @INSTANCE_NAME, @SYSTEM_INSTANCE_NAME OUTPUT;
		SET @SYSTEM_INSTANCE_NAME=SUBSTRING(@SYSTEM_INSTANCE_NAME,1,CHARINDEX('.',@SYSTEM_INSTANCE_NAME))
		PRINT @SYSTEM_INSTANCE_NAME

    Hope this is what you were looking for, if not let me know.

    Regards Gursethi Blog: http://gursethi.blogspot.com/ ++++ Please mark "Propose As Answer" if my answer helped ++++
     
  • Tuesday, August 09, 2011 7:29 AM
     
      Has Code
    Sign In to Vote
    0

    Hi,

    So if i understand your question properly what you want is values without instance name, as you mentioned above:

    For SQL 2008 Default Inatnce it should be MSSQL10

    For SQL 2008 Named Instance it should be MSSQL10_50

    What you need to do is play with te registry key like below.

     

    		DECLARE @IMAGE_PATH NVARCHAR(100)      
		DECLARE @INSTANCE_NAME NVARCHAR(100)      
		DECLARE @INSTANCE_NAME1 NVARCHAR(100) 
		DECLARE @SYSTEM_INSTANCE_NAME NVARCHAR(100)      
		
		SET @INSTANCE_NAME = COALESCE(CONVERT(NVARCHAR(100), SERVERPROPERTY('INSTANCENAME')),'MSSQLSERVER');      
		IF @INSTANCE_NAME!='MSSQLSERVER'      
		SET @INSTANCE_NAME=@INSTANCE_NAME      
	 
 	SET @INSTANCE_NAME1= COALESCE(CONVERT(NVARCHAR(100), SERVERPROPERTY('INSTANCENAME')),'MSSQLSERVER');      
		IF @INSTANCE_NAME1!='MSSQLSERVER'      
		SET @INSTANCE_NAME1='MSSQL$'+@INSTANCE_NAME1      
			
		EXEC MASTER.DBO.XP_REGREAD N'HKEY_LOCAL_MACHINE', N'SOFTWARE\MICROSOFT\MICROSOFT SQL SERVER\INSTANCE NAMES\SQL', @INSTANCE_NAME, @SYSTEM_INSTANCE_NAME OUTPUT;
		SET @SYSTEM_INSTANCE_NAME=SUBSTRING(@SYSTEM_INSTANCE_NAME,1,CHARINDEX('.',@SYSTEM_INSTANCE_NAME))
		PRINT @SYSTEM_INSTANCE_NAME

    Hope this is what you were looking for, if not let me know.

    Regards Gursethi Blog: http://gursethi.blogspot.com/ ++++ Please mark "Propose As Answer" if my answer helped ++++

    Gursethi , Thanks a lot for your reply. 

    What's language do you use? If it is T-SQL, I think this period of code need to be run in SQL server environment, and use it to get the registry key. 

    However, my requirement is to query registry key first, and get port number, then combine the connection string to connect to SQL server database.

    So, I need a way to know which version the target SQL server use, "MSSQL10", "MSSQL10_50", or may be "MSSQL10_*" in later R3 release if it has. 

     
  • Tuesday, August 09, 2011 2:06 PM
    Moderator
     
     
    Sign In to Vote
    0

    Why do you need to do this?  You can use the SQL Server Configuration Manager and set fixed ports for the two instances so that they are always listening on the same TCP Port.

    http://msdn.microsoft.com/en-us/library/ms177440.aspx

    This is the recommended setup for doing what you are trying to do.  Alternately you can turn the SQL Browser service on and open TCP/UDP 1433 and 1434 through the firewall.  Then in your connection string you just specify SERVERNAME\INSTANCENAME in the connection string and the Browser service will direct the connection to the correct port automatically.  This is the reason the Browser Service exists.

    Jonathan Kehayias | Senior Consultant, SQLSkills.com
    SQL Server MVP | Microsoft Certified Master: SQL Server 2008
    Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
    Please click the Mark as Answer button if a post solves your problem!


     
  • Tuesday, August 09, 2011 2:10 PM
    Moderator
     
     Answered
    Sign In to Vote
    0

    What exactly are you trying to use these registry entries for?  There should be no reason to examine these registry entries.  And you should not be modifying these registry entries from your program.

    But, if you really must know, the registry key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL contains the mapping of instance names to registry subkeys.

     


    • Marked As Answer by yazoox Wednesday, August 10, 2011 3:45 AM
    •  
     
  • Wednesday, August 10, 2011 3:44 AM
     
     
    Sign In to Vote
    0

    What exactly are you trying to use these registry entries for?  There should be no reason to examine these registry entries.  And you should not be modifying these registry entries from your program.

    But, if you really must know, the registry key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL contains the mapping of instance names to registry subkeys.

     


    Thanks, Tom, that is what I need. 
     
  • Wednesday, May 02, 2012 1:30 PM
     
     
    Sign In to Vote
    0

    I can think of a reason to READ from this point, but not update of course.

    We have an in-house app (SSIS, T-SQL, etc.) that monitors our SQL instances, and gathers much information on a recurring "monitor" basis as well as nightly data "collection".

    We have found we are lacking on collection of SSAS, SSIS, & SSRS information from these same machines. 

    I have found that I can read the REG for instances installed on the hardware.  After that point, I have code to check the "service" status to see if it is OK, and report accordingly.