Alternatives to xp_cmdshell for Folder Directoy
-
I need to retire our usage of xp_cmdshell. I use this for 3 different purposes, the most prevalent being using the DIR command in order to retrieve a list of files in a particular folder. The need is to import text files, or xml, with a dynamic name.
Is there any alternative to this without losing this type of access or functionality from within T-SQL?
Thanks!
-
You should perform that from outside of SQL Server if possible.
* Noel -
Thank you. This may be appropriate on another board, but what options are available? I have a stored proc that relies on a list of files within a folder. Therefore, what direction should I be looking at, and how would that interact with a stored proc in order to retrieve that list of files?
Thanks again!
-
If you are on SQL Server 2005/2008 then you can consider using SQL CLR procedures to replace the XP_Cmdshell functionality.
The book "Professional SQL Server 2005 CLR Programming" has an entire chapter devoted to replacing extended stored procedures with SQL CLR procedures. On actual example involves the replacement of xp_cmdshell
Regards
Patrick
-
You might also look at the SSIS, once you get the grip of it, it is pretty flexible.
Regards
Piotr
...and your only reply is slàinte mhath
-
JuanBob (1/5/2009)
I need to retire our usage of xp_cmdshell. I use this for 3 different purposes, the most prevalent being using the DIR command in order to retrieve a list of files in a particular folder. The need is to import text files, or xml, with a dynamic name.Is there any alternative to this without losing this type of access or functionality from within T-SQL?
Thanks!
If ALL you want is a list of file names for a given directory, this is really simple and is available in 2k, 2k5, and 2k8. You'll get lot's of folks warning you about using undocumented stuff because it could go away at any time. I've had documented stuff suffer the same problem. For example, like when they very suddenly changed the privs on the very well documented sp_MakeWebTask...
DECLARE @Path VARCHAR(300)SET @Path = 'C:\'
CREATE TABLE #DirListing
(
ListingName VARCHAR(300),
Depth INT,
IsFile INT
)
INSERT INTO #DirListing (ListingName,Depth,IsFile)
EXEC Master.dbo.xp_DirTree @Path,1,1
SELECT RowNum = IDENTITY(INT,1,1),
FileName = ListingName,
Processed = 'N'
INTO #FileInfo
FROM #DirListing
WHERE IsFile = 1
ORDER BY ListingName
SELECT * FROM #FileInfo
--add your file processing code here...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
If the intention is to reduce or remove the use of extended stored procedures then replacing the xp_cmdshell with xp_DirTree is not really gaining much. However the xp_DirTree will have less security concerns than xp_cmdshell.
One other possibility that is availiable in 2008 is use of Powershell scripts. However both this and SSIS will require SQL Agent jobs or some other external activation process.
Regards
Patrick
-
This is straightforward as a CLR, the C# code is roughly this
[SqlFunction(FillRowMethodName = "DirListingFillRow", Name = "DirListing", TableDefinition = "filepath nvarchar(max)")]public static IEnumerable DirListingInit(SqlString path)
{
return Directory.GetFileSystemEntries(path.Value);
}
private static void DirListingFillRow(Object obj, out SqlString filepath)
{
filepath = new SqlString((String)obj);
}
select * from dbo.DirListing('c:\')
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
I was referring to call something from PowerShell, Perl, FTP or anything else out of SQL Server Engine to optain the File List, Then you should pass it somehow to your stored procedure.
* Noel -
Thank you very much for all the suggestions and recommendations here! Much more information than I had hoped for so this is great!
-
Patrick Flynn (1/6/2009)
If the intention is to reduce or remove the use of extended stored procedures then replacing the xp_cmdshell with xp_DirTree is not really gaining much. However the xp_DirTree will have less security concerns than xp_cmdshell.One other possibility that is availiable in 2008 is use of Powershell scripts. However both this and SSIS will require SQL Agent jobs or some other external activation process.
Regards
Patrick
I don't use it, so I don't know... what kind of privs do you need to use PowerShell?
And, no... the purpose of using xp_DirTree is because the public can use it... much much less security concerns.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply