Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1368
  • Last Modified:

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
0
crompnk
Asked:
crompnk
2 Solutions
 
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
 
crompnkAuthor 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

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now