Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2009

    Unanswered: Alternatives to xp_cmdshell

    I'm well aware of the security implications of enabling and using xp_cmdshell but for various reasons (mainly simplicity) I've used it in numerous places within my application (14 in production to be precise and a few dozen more sitting solely in our test environment) for various purposes.

    One of the main purposes I use it for is to check if a file exists on the operating system, eg.:
    Declare @v_DirTable Table (FileName nvarchar(128))
    Declare @v_XPString nvarchar(512)	
    Set @v_XPString = 'DIR /B/S "'+@BaseSearchDirectory+@FileSpecifier+'"'
    Into	@v_DirTable
    exec xp_cmdshell @v_XPString
    Select Top 1 @v_FileName = FileName From @v_DirTable Where FileName Like Replace(@FileSpecifier,'*','%')
    Where @BaseSearchDirectory and @FileSpecifier are parameters containing the directory and a file string - eg "Campaign*.xls" and are dynamic.

    I know that using CmdExec within a SQL agent job that you start from within your procedure is one of the recommended alternatives to xp_cmdshell, but I'm not aware of
    1.) How to pass the dynamic parameters - i.e. filename and directory - to the process and
    2.) How to get the return result - i.e. the full filename that has been located.

    Any thoughts on how I can best achieve removing xp_cmdshell from general usage given the above conditions?

    I suspect the knowledgeable amongst us are going to tell me SSIS is the answer...

  2. #2
    Join Date
    Sep 2011
    Hello ,I'm not sure but you can try below method

    Exec xp_fileexist 'D:\data'

    Result is:
    File Exists File is a Directory Parent Directory Exists
    0 1 1

    Note: 0 -Not exists ,1 -Exists

  3. #3
    Join Date
    Mar 2009
    The xp_fileexist extended proc just tells me if a specific file exists, I'm dealing with non-specifics. It doesn't solve the problem of which subdirectory the file is in and what the full filename is.

    I'm passing through a root directory for data imports... eg c:\DataImports\

    This directory might have a number of subdirectories depending on how the customer wants to arrange the information.

    I'm also passing through a wildcard filename - eg Campaign*.xls - because a lot of the files have additional information in the name such as customer number, a date/timestamp etc. so I want to search for the first .xls file starting with Campaign in the root directory or any of its sub-directories.

  4. #4
    Join Date
    Sep 2011
    Hi EngadaSql ,
    Yes Now i understand you well :try methods below

    Here is user function source code of sample sql functions to extract file name from path.
    @fullpath nvarchar(max),
    @delimiter nvarchar(100)
    ) RETURNS nvarchar(max)

    declare @split as table (
    id int identity(1,1),
    fragment nvarchar(max)
    declare @filename nvarchar(max)
    declare @xml xml

    SET @xml =
    N'<root><r>' +
    REPLACE(@fullpath, @delimiter,'</r><r>') +

    INSERT INTO @split(fragment)
    r.value('.','nvarchar(max)') as item
    FROM @xml.nodes('//root/r') as records(r)

    SELECT @filename = fragment
    FROM @split
    WHERE id = (SELECT MAX(id) FROM @split)

    RETURN LTRIM(RTRIM(@filename))

    Here is a sample sql function call which parse file name from a given file path and returns the file name to the sql user.
    SELECT dbo.GetFileName(N'C:\SQLDatabases\SQLBackup.mdf',N '\') as [File Name]
    Now to show full link ,here we are;==>>
    How to Extract Filename from Path using SQL Functions

  5. #5
    Join Date
    Mar 2009
    Again not even coming close to answering the question I have asked. I suggest you read my post in full again before making another attempt.

  6. #6
    Join Date
    Feb 2004
    In front of the computer
    Provided Answers: 54
    Quote Originally Posted by EngadaSQL View Post
    I suspect the knowledgeable amongst us are going to tell me SSIS is the answer...
    I can't speak for the knowledgeable, but SSIS or PowerShell would be my first choices.

    I'd also turn the problem upside down... Instead of having the Transact-SQL pass parameters for what to search for and where to look for it, I'd harvest the whole fam-damily from the filesystem every so often via a job and put that into a table or group of tables. Then I could allow the SQL side of the house to rollick through that data as necessary.

    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Sep 2001
    Chicago, Illinois, USA
    If you prefer to use a SQL Agent job (for security reasons) but are having difficulty (because of the dynamic nature of your problem), why not create and execute the SQL Agent job dynamically?

    I do this for a very dynamic process that I require and it allows me tremendous flexibility. It takes a bit to accomplish 'cause setting-up a job, with it's steps and schedules, involves a number of processes, but once you get it done, it works well.

    The general construct of how you dynamically create an SQL Agent job is:

    1) delete previous instances of the job using msdb.dbo.sp_delete_job.
    2) add the new job using msdb.dbo.sp_add_job.
    3) add the job steps to the new job using msdb.dbo.sp_add_jobstep.
    4) add the schedule to the new job using msdb.dbo.sp_add_jobschedule.
    5) add the job sever assocatied with the new job using msdb.dbo.sp_add_jobserver
    Last edited by PracticalProgram; 10-31-11 at 08:42.

    Maverick Software Design

    (847) 864-3600 x2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts