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
crompnkData Management SpecialistAsked:
Who is Participating?
 
chapmandewCommented:
In SQL 2005 and 2008 you can access external files through the use of the SQL CLR:

http://www.mssqltips.com/tip.asp?tip=1662
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
there is no alternative, but you can use this
EXEC master..xp_dirtree 'C:\'
0
 
netcmhCommented:
Have you seen this link - http://www.sql-server-pro.com/xp_cmdshell.html ?
0
 
crompnkData Management SpecialistAuthor Commented:
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')
0
 
dbidbaCommented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.