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:

CREATE TABLE #Tmp
(
fileattr nvarchar(255)
)

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

SELECT * FROM #Tmp
--DROP TABLE #Tmp

OUTPUT

image

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


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter


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

 
  Feedback:

comments

1 Response to "Use xp_cmdshell results in T_SQL"
  1. Andrew Bitl said...
    September 28, 2010 at 10:43 PM

    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?

 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions