September 27, 2010

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:

fileattr nvarchar(255)

EXEC xp_cmdshell 'dir *.exe'




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, ASP.NET Architecture MVP works as an Architect Consultant and provides consultancy on how to design and develop Web applications.

Suprotim is also the founder and primary contributor to DevCurry, DotNetCurry and SQLServerCurry. He has also written an EBook 51 Recipes using jQuery with ASP.NET Controls.

Follow him on twitter @suprotimagarwal

1 comment:

Andrew Bitl said...

the approach looks good although i am unable to determine how to use it in a real scenario. Is it possible to read a environment variable and then ues it later?