Avatar of sdmgill1
sdmgill1Flag for United States of America

asked on 

Dynamically Find SQL Instance Name for VBScript

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.
DatabasesMicrosoft SQL Server 2005Microsoft SQL Server 2008

Avatar of undefined
Last Comment
Marten Rune
Avatar of Aaron Shilo
Aaron Shilo
Flag of Israel image

Avatar of aikimark
aikimark
Flag of United States of America image

If these are VBscript tasks, you should be able to use WMI calls to get the system name.
Avatar of Marten Rune
Marten Rune
Flag of Sweden image

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

Avatar of sdmgill1
sdmgill1
Flag of United States of America image

ASKER

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.
ASKER CERTIFIED SOLUTION
Avatar of Marten Rune
Marten Rune
Flag of Sweden image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Microsoft SQL Server 2005
Microsoft SQL Server 2005

Microsoft SQL Server 2005 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. It includes support for managing XML data and allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests.

72K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo