Dynamically Find SQL Instance Name for VBScript

take alook at this : http://devpinoy.org/blogs/keithrull/archive/2006/06/08/Programmatically-List-All-SQL-Servers-And-Their-Databases-Using-SMO-.aspx

If these are VBscript tasks, you should be able to use WMI calls to get the system name.

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

Select allOpen in new window

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.