I had recently posted on Use xp_cmdshell results in T_SQL. One of the blog readers Andrew got back asking if it was possible to read Environment Variables using this method and store the value in a variable for later use.
Here’s the query. The variable @windir holds the value and you can use it as you want: (Thanks to my colleague Karthi for simplying the query further)
DECLARE @windir nvarchar(255)
CREATE TABLE #Tmp
INSERT INTO #Tmp exec xp_cmdshell 'echo %windir%'
SET @windir = (SELECT TOP 1 EnvVar from #Tmp)
SELECT @windir as 'Windows Directory'
-- DROP TABLE #Tmp
Note: Observe I am using ‘TOP 1’ in the query since the subquery returned more than one row, one of them being a blank row. I am not sure why is the blank row returned and till I find it out, I will stick with the ‘TOP 1’ command.