Alternative to using xp_cmdshell

Posted on 2010-01-05
Last Modified: 2012-05-08
I would like to know if there is an alternative to using xp_cmdshell in T-SQL.

I would like to return the file names from a folder in windows XP and use the names to populate a table in an sql server 2005 database.

I found a t-sql procedure that does this but uses 'xp_cmdshell'

Question by:crompnk
    LVL 60

    Accepted Solution

    In SQL 2005 and 2008 you can access external files through the use of the SQL CLR:
    LVL 75

    Assisted Solution

    by:Aneesh Retnakaran
    there is no alternative, but you can use this
    EXEC master..xp_dirtree 'C:\'
    LVL 20

    Expert Comment


    Author Comment


    I was looking at using the xp_dirtree stored proc.

    Is it possible to call a this sp in a table function, so I can pass a file path to it.

    for example:

    CREATE FUNCTION dbo.FN_SUBDIRECTORY(subdirectory varchar(200),depth float)
    RETURN (EXEC master..xp_dirtree 'C:\Temp')
    LVL 5

    Expert Comment

    There is another option if it fits your situation. That is SQL Agent jobs, which can be set to run OS level commands. It has the added benefit of executing out-of-process rather then in-process as xp_cmdshell does. In some cases, I have wrappered dynamic job creation, execution, sleep until completion, and harvest of results to allow non-privileged accounts to run commands through Agent jobs.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
    In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now