Dynamically Find SQL Instance Name for VBScript

sdmgill1
sdmgill1 used Ask the Experts™
on
Hello,
We have a Master Server environment and would like to push out an ActiveX job that gathers data about mountpoints on the server. We have all the code to gather the information but we are running into problems when we try to dynamically find the SQL instance name.  Our environment is cluster heavy with a myriad of 2+ node, 2+ instances, Active/Active, Active/Passive, etc.  Since the vbscript fires inside of a SQL Job I thought this would be easier.  So for example, I would have a generic piece of code on the Master server that pushes the job to the target servers.  When run, the job determines which SQL instance it is running on, gathers the required information and logs it to a table.  All the pieces are done except the "which server am I on" piece.  Any help would be appreciated.  Thanks.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
aikimark
Top Expert 2014

Commented:
If these are VBscript tasks, you should be able to use WMI calls to get the system name.
Marten RuneSQL Expert/Infrastructure Architect

Commented:
How about running this code on each instance you find with your VBScript

Regards Marten
DECLARE @nameVarchar VARCHAR(16)
	DECLARE @bigVarchar as varchar(500)
	
		IF serverproperty('IsClustered') = 1
	BEGIN
		EXEC master..xp_regread N'HKEY_LOCAL_MACHINE' , 'Cluster' , N'ClusterName', @nameVarchar OUT
		PRINT 'Klustrad =          ''Yes''' + ' Klustrets namn: ' + '''' + @nameVarchar + ''''
		PRINT 'Servernamn:         ' + '''' + Cast(serverproperty('MachineName') as varchar) + ''''
		PRINT 'Instans:            ' + '''' + Cast(serverproperty('InstanceName') as varchar) + ''''

			-- Find IP THROUGH SQLCMD
			SET @bigVarchar = 'PING ' + Cast(serverproperty('MachineName') as varchar) + ' -n 1'
			INSERT INTO #TempIP (RegKey)
			EXEC XP_CMDSHELL @bigVarchar 
			SELECT @bigVarchar = REGKEY
			FROM #TempIP
			WHERE REGKEY LIKE 'Pinging%'
			SET @bigVarchar = RIGHT(@bigVarchar,LEN(@bigVarchar)-CHARINDEX('[',@bigVarchar))
			SET @bigVarchar = LEFT(@bigVarchar,CHARINDEX(']',@bigVarchar) -1)

		PRINT 'Klustrets IP:       ' + '''' + @bigVarchar + ''''

		EXEC master..xp_instance_regread 'HKEY_LOCAL_MACHINE',N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\Tcp\IPAll','TcpDynamicPorts', @nameVarchar OUT
		PRINT 'TcpDynamicPorts:    ' + '''' + @nameVarchar + ''''

	END
	ELSE
	BEGIN
		PRINT 'Klustrad =          ''No'''
		PRINT 'Servernamn:         ' + '''' + Cast(serverproperty('MachineName') as varchar) + ''''
	END

Open in new window

sdmgill1

Author

Commented:
Sorry for the delay in getting back to this.  Thanks to everyone for their input.  Let me add some more info for clarification.  I do not want to use .net or C# as I do not want to enable CLR on the servers.  Also, the machine name is rarely the SQL instance name.  Given the environment a single node of a cluster could be running 2 or more instance of SQL.  Granted, we would have bigger fish to fry at that point but we should account for that just in case.  Here is an example.  We have a 4 node W2K8 cluster (N1 - N4) with Windows cluster name WC1.  On top of that we have 3 SQL clustered instances (SQL1, SQL2\Named1, SQL3\Named2).  At any given point, any SQL instance could reside on any node of WC1.  The VBScript would need to pulll the relevant information for the SQL instance it is running on only, regardless of the node or how many other SQL instances are running on the same machine.  In particular I just need the instance name (SQL1, SQL2\Named1 or SQL3\Named2) not all three.  Even if all three were running on the same node I would just want the name of the SQL instance running the code.  Once we have the instance name, then we connect to it and run TSQL after the remaining VBScript (which we already have working) runs to completion.  The VBScript essentially finds all the moint point information for the SQL Instance because xp_fixeddrives and sys.dm_io_cluster_shared_drives do not give us the level of detail we need.  Thanks again.
SQL Expert/Infrastructure Architect
Commented:
If you use a VBScript, let it query the services, and find all running instances on this host (i e service is running, clustered instance is on this host).

This should get you started:

strComputer = "."
strServices = ""
Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set colRunningServices = objWMIService.ExecQuery _
("Select * from Win32_Service WHERE DisplayName Like '%SQL Server (%'")
For Each objService in colRunningServices
if objService.State = "Running" then
Wscript.Echo objService.DisplayName & VbTab & objService.State
end if
Next


//Marten

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial