Use xp_cmdshell results in T_SQL

We were working on a requirement where we needed to use the result of xp_cmdshell in a T-SQL query.

Note: The users running this commands are members of the sysadmin fixed server.If you want others to be able to execute this command, you will have to explicitly grant other users permission to execute this stored procedure

Here’s how to do store the results of xp_cmdshell for later use. The query shown below returns a directory listing of the current directory and stores it in a Temp table to be used later:

CREATE TABLE #Tmp
(
fileattr nvarchar(255)
)

INSERT INTO #Tmp
EXEC xp_cmdshell 'dir *.exe'

SELECT * FROM #Tmp
--DROP TABLE #Tmp

OUTPUT

You can now use a bunch of SUBSTRING commands and extract the date, filesize and filename results, as you want.

About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook