Get Command-Line Output from SQL Server 2005

Sometimes, from SQL Server you need to execute a command line application and retrieve its output in a variable.

When you want to to this, you have to consider:

  1. Enabling the execution of the xp_cmdshell stored procedure
  2. Saving the output of the command (maybe more lines) in a temporary table
  3. Taking the first row a putting it in a variable

Finally, here is a complete example. In this case I take the first user in Active Directory whose distinguished name starts with Damiano:

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO
CREATE TABLE #TEMP (
    LINE nvarchar(4000)
)
INSERT INTO #TEMP EXECUTE master..xp_cmdshell 'dsquery user -name Damiano*'
GO
EXEC sp_configure 'xp_cmdshell', 0
GO
RECONFIGURE
GO
EXEC sp_configure 'show advanced options', 0
GO
RECONFIGURE
GO
DECLARE @MYVAR nvarchar(512)
SELECT TOP 1 @MYVAR = LINE FROM #TEMP
DROP TABLE #TEMP

Add comment

Loading