asked on

Alternative to using xp_cmdshell

Hi,
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'

Thanks

ASKER CERTIFIED SOLUTION

chapmandew

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Aneesh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

netcmh

Have you seen this link - http://www.sql-server-pro.com/xp_cmdshell.html ?

crompnk

ASKER

Thanks,

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)
RETURNS TABLE
AS
RETURN (EXEC master..xp_dirtree 'C:\Temp')

dbidba

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.