Using xp_cmdshell to pass parameters to vbs

For an existing client running SQL Server 2005 Standard there was a need to take values from a result set and pass them to a vbs that resided on the server. In my post “Formating Date and Time” I ran through the steps of formatting a datetime data type to meet my needs.

I chose to pursue a pure tsql based option but there are other avenues which I could have followed such as using a pure VBS approach recommended by Robert Davis (blog | @sqlsoldier) which I have done in the past and worked quite well. Not sure why it didn’t come to mind as it is a viable solution first. Then Dave Levy (blog | @Dave_Levy) suggested powerShell. Which is something I actually haven’t worked with yet. Interesting as #powerShell sounds I had to produce something ASAP, but in the process I would research #powerShell nonetheless.

So I chose a TSQL path using the extended stored procedure xp_cmdshell to accomplish the task at hand. To get started xp_cmdshell needs to be enabled, but first you might want to turn on advanced options to see if its is already on.

TSQL

EXECUTE SP_CONFIGURE

Outcome
StandardOptions

As you can see the results set are limited so in order to see information about xp_cmdshell we need to switch on the advanced options… so let’s get to it.

EXECUTE SP_CONFIGURE 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO
EXECUTE SP_CONFIGURE
GO

Being in alphabetic order you’ll need to scroll all the way down to see if the config_value is set to one. If it is set to zero then it’s disabled. In that event we need to enable it.

Outcome
02.AdvOptions_v0.01

That is done by setting the value to one. This is simply done by running the following:

EXECUTE SP_CONFIGURE 'xp_cmdshell', '1'
RECONFIGURE WITH OVERRIDE
GO
EXECUTE SP_CONFIGURE
GO

Now that xp_cmdshell is enabled we will see a value of one in the config_value field.

Outcome
03.xp_cmdshell_enabled

To test it let’s run the following:

DECLARE @results INT
DECLARE @cmd nvarchar(255)
SET @cmd = 'ping sqlsamson8' + ' -n 1 | find /i "reply"'

EXEC @results = master..xp_cmdshell @cmd
IF (@results = 0)
	SELECT 'ONLINE'
ELSE
	SELECT 'OFFLINE'

I don’t have a system called sqlsamson8 so ‘OFFLINE’ is returned.

Outcome
04.TestRun

Now let’s test this with a system that does exist.

DECLARE @results INT
DECLARE @cmd nvarchar(255)
SET @cmd = 'ping sqlsam7' + ' -n 1 | find /i "reply"'

EXEC @results = master..xp_cmdshell @cmd
IF (@results = 0)
	SELECT 'ONLINE'
ELSE
	SELECT 'OFFLINE'

Sure enough it’s ‘ONLINE’.

Outcome
05.TestRun

This is cool but I need some form of log to be generated. In short a file needs to be created and written to if the device was found on or offline.

DECLARE @results INT, @onLog NVARCHAR(50),@offLog NVARCHAR(50)
DECLARE @cmd NVARCHAR(255), @mn NVARCHAR(50), @cmd1 NVARCHAR(255)

-- Output results to file
SET @onLog = 'D:\xp_cmdshell\Online.txt'
SET @offLog = 'D:\xp_cmdshell\Offline.txt'
-- set target computer name
SET @mn = 'sqlsamson8'
-- set print process
SET @cmd = 'ping ' + @mn + ' -n 1 | find /i "reply"'
-- Execute ping process
EXEC @results = master..xp_cmdshell @cmd
IF (@results = 0)
	BEGIN
		-- Display online message to grid results
		SELECT @mn + ' IS ONLINE!'
		-- If device is online log computer name to output file
		SET @cmd1 =  'echo ' + @mn + ' >> ' + @onLog
		-- Execute statement
		EXEC master..xp_cmdshell @cmd1
	END
ELSE
	BEGIN
		-- Display offline message to grid results
		SELECT @mn + ' IS OFFLINE'
		-- If device is offline log computer name to output file
		SET @cmd1 =  'echo ' + @mn + ' >> ' + @offLog
		-- Execute statement
		EXEC master..xp_cmdshell @cmd1
	END	

Outcome
06.TestRun_WithOutputLog

Perfect. Exactly what I needed. However I need to run through a series of devices and having to manually specify a computer name is out of the question. I could use a CURSOR, but today I feel in a WHILE loop kind of mode…

DECLARE @Cnt INT, @mn NVARCHAR(50), @newmn NVARCHAR(50)
DECLARE @cmd NVARCHAR(255), @results INT, @cmd1 NVARCHAR(255)
DECLARE @offLog NVARCHAR(50), @onLog NVARCHAR(50)

SET @onLog = 'D:\xp_cmdshell\ONLINE.txt'
SET @offLog = 'D:\xp_cmdshell\OFFLINE.txt'
SET @mn = 'sqlsam'
SET @Cnt = 0

WHILE @Cnt < 10
BEGIN
	SET @Cnt = @Cnt + 1
	-- take @mn and append @Cnt (i.e. sqlsam + 1 = sqlsam1)
	SET @newmn = @mn + CONVERT(VARCHAR,@Cnt)
	SET @cmd = 'ping ' + @newmn + ' -n 1 | find /i "reply"'
	EXEC @results = master..xp_cmdshell @cmd
		IF (@results = 0)
			BEGIN
				-- Display online message to grid results
				PRINT @newmn + ' IS ONLINE!'
				-- If device is online log computer name to output file
				SET @cmd1 =  'echo ' + @newmn + ' >> ' + @onLog
				-- Execute statement
				EXEC master..xp_cmdshell  @cmd1
			END
		ELSE
			BEGIN
				-- Display offline message to grid results
				PRINT @newmn + ' IS OFFLINE'
				-- If device is offline log computer name to output file
				SET @cmd1 =  'echo ' + @newmn + ' >> ' + @offLog
				-- Execute statement
				EXEC master..xp_cmdshell @cmd1
			END
END

Great… loop and ping though the devices and record to logs accordingly. Bingo!

Outcome
07.TestRunLoop_WithOutputLog

Well there is one problem… if I run this again the results will be appended to the existing log essentially doubling the results with the same data. This is completely avoidable if I append a date-time stamp to the log file names!

DECLARE @Cnt INT, @mn NVARCHAR(50), @newmn NVARCHAR(50)
DECLARE @cmd NVARCHAR(255), @results INT, @cmd1 NVARCHAR(255)
DECLARE @offLog NVARCHAR(50), @onLog NVARCHAR(50),@dtStamp NVARCHAR(50)

SET @dtStamp =	CONVERT(varchar, GETDATE(), 112) + '_' + 
								REPLACE(CONVERT(varchar(5), GETDATE(), 108),':','')

SET @onLog = 'D:\xp_cmdshell\ONLINE_' + @dtStamp +'.txt'
SET @offLog = 'D:\xp_cmdshell\OFFLINE_' + @dtStamp +'.txt'

SET @mn = 'sqlsam'
SET @Cnt = 0

WHILE @Cnt < 10
BEGIN
	SET @Cnt = @Cnt + 1
	-- take @mn and append @Cnt (i.e. sqlsam + 1 = sqlsam1)
	SET @newmn = @mn + CONVERT(VARCHAR,@Cnt)
	SET @cmd = 'ping ' + @newmn + ' -n 1 | find /i "reply"'
	EXEC @results = master..xp_cmdshell @cmd
		IF (@results = 0)
			BEGIN
				-- Display online message to grid results
				PRINT @newmn + ' IS ONLINE!'
				-- If device is online log computer name to output file
				SET @cmd1 =  'echo ' + @newmn + ' >> ' + @onLog
				-- Execute statement
				EXEC master..xp_cmdshell  @cmd1
			END
		ELSE
			BEGIN
				-- Display offline message to grid results
				PRINT @newmn + ' IS OFFLINE'
				-- If device is offline log computer name to output file
				SET @cmd1 =  'echo ' + @newmn + ' >> ' + @offLog
				-- Execute statement
				EXEC master..xp_cmdshell @cmd1
			END
END

Outcome
08.TestRunLoop_WithOutputLog_TimeStamp

Now I only need to add the VBS path, execute it and then we are golden!

DECLARE @Cnt INT, @mn NVARCHAR(50), @newmn NVARCHAR(50), @vbscmd NVARCHAR(50)
DECLARE @cmd NVARCHAR(255), @results INT, @cmd1 NVARCHAR(255)
DECLARE @offLog NVARCHAR(50), @onLog NVARCHAR(50),@dtStamp NVARCHAR(50)

SET @dtStamp =	CONVERT(varchar, GETDATE(), 112) + '_' + 
								REPLACE(CONVERT(varchar(5), GETDATE(), 108),':','')

SET @onLog =  'D:\xp_cmdshell\ONLINE_' + @dtStamp +'.txt'
SET @offLog =  'D:\xp_cmdshell\OFFLINE_' + @dtStamp +'.txt'


SET @mn = 'sqlsamson'
SET @Cnt = 0

WHILE @Cnt < 10
BEGIN
	SET @Cnt = @Cnt + 1
	-- take @mn and append @Cnt (i.e. sqlsam + 1 = sqlsam1)
	SET @newmn = @mn + CONVERT(VARCHAR,@Cnt)
	SET @cmd = 'ping ' + @newmn + ' -n 1 | find /i "reply"'
	EXEC @results = master..xp_cmdshell @cmd
		IF (@results = 0)
			BEGIN
			SET @vbscmd = 'cscript /nologo D:\test.vbs ' + '"' + @newmn + '"'
				-- Display online message to grid results
				PRINT @newmn + ' IS ONLINE!'
				-- If device is online log computer name to output file
				SET @cmd1 =  'echo ' + @newmn + ' >> ' + @onLog
				-- Execute write statement
				EXEC master..xp_cmdshell @cmd1
				-- Execute vbs statement
				PRINT @vbscmd
				EXEC master..xp_cmdshell @vbscmd
			END
		ELSE
			BEGIN
				-- Display offline message to grid results
				PRINT @newmn + ' IS OFFLINE'
				-- If device is offline log computer name to output file
				SET @cmd1 =  'echo ' + @newmn + ' >> ' + @offLog
				-- Execute statement
				EXEC master..xp_cmdshell @cmd1
			END
END

Outcome
09.ParameterPassedToVBS

Of course this only works when the naming convention is standardized and the numbers are sequentially sound. I could create a temp table to pull in all the device names and add a ID field that auto increments by a value of one. Then run through the loop selecting the data from temp table where ID = @Cnt. That is a feasible option.

Advertisements

One thought on “Using xp_cmdshell to pass parameters to vbs

  1. Darryl says:

    Hi,
    Great stuff. However where is the vbs file D:\test.vbs itself in all this?
    I’d really like to see how you have declared the variable in the vbs you are passing @newmn into:
    SET @vbscmd = ‘cscript /nologo D:\test.vbs ‘ + ‘”‘ + @newmn + ‘”‘
    Maybe I am dumb….

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s