Manipulating Dos Files from SQL

Posted on 2005-04-12
Last Modified: 2006-11-18
Is it possible to manipulate files using SQL commands, for example get a file name from a directory?
What I need to do is poll a directory and if any files are there I would like to get the file name and details, size, datestamp and so on and enter these details into a database. Then maybe do some thing wit the file, move itmaybe.
Question by:AxfordA
    LVL 29

    Accepted Solution

    Look into xp_cmdshell, which you can use to execute an operating system commands.


    Using xp_cmdshell in a stored procedure

    You can use xp_cmdshell in stored procedures to perform a variety of tasks
    in the server environment. In this example, the last chance threshold stored
    procedure sp_lct_action first prints a warning message in the error log,
    then alerts the system administrator via e-mail:

    create procedure sp_lct_action
    @db_name varchar (30),
    @seg_name varchar (30),
    @freespace int,
    @status int
    dump transaction @dbname to tmpdevice
    print "Alert! Last Chance Threshold reached for Adaptive Server"
    print "in Database %1!", @database
    xp_cmdshell "mail sybasesa < $HOME/lct_mailtext"

    Using xp_cmdshell in a trigger

    You can use xp_cmdshell in triggers to gain more flexibility in responding
    to changes in data. In this example, the trigger pricetrig fires when a
    stock price is updated; it launches the stockaction application on a remote
    server whenever the price exceeds a predefined threshold:

    create trigger pricetrig
    on stocks
    for update
    if update (price)
          if (select stocks.price + inserted.price - deleted.price
            from stocks, inserted, deleted
            where stocks.stockname = inserted.stockname
            and inserted.stockname = deleted.stockname) >100
            exec xp_cmdshell "rsh app_server /usr/bin/stock_action"
    LVL 24

    Expert Comment

    by:Joe Woodhouse
    Leonstryker's answer is a good one because xp_cmdshell is available in all versions of ASE from 11.5 onwards, and is included in the base product.

    There are some specific file-handling features in ASE 12.5.x - the XFS (for eXtended File System) premium option. Note however that this is *not* included in the base product and must be separately licensed. (Usually the premium features aren't super expensive though.) You could almost certainly get your current Sybase account manager to generate you a 30- or 60-day eval key to have a look at it.

    It extends the basic "proxy table" functionality (where you build a local proxy or shadow table that is like a UNIX symbolic link or Windows shortcut to data somewhere else).

    You can build a proxy table and point it at a directory. Now when you query this "table" you will see one row for each file in the directory. The columns of the "table" include all the file attributes - name, creator, date created, etc, including a column for the file's contents. Deleting a row deletes the file. Inserting a row will create a file.

    I know a manufacturing company who does exactly what you're talking about using this feature - polling a directory and doing things with the files.

    So, the xp_cmdshell method is free, more widely available and very generic - but you'll have to go to a bit of trouble coding your own scripts etc to deal with the files. The XFS option gives you that functionality up-front, but you have to pay for it and it's only for ASE 12.5.x.

    Good luck!
    LVL 29

    Expert Comment

    I would like to add one more thing.  Due to the nature of xp_cmdshell, realize that anyone who has access to it in effect has admin right on the server machine.  Its an important thing to keep in mind.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    Suggested Solutions

    Not sure what the best email signature size is? Are you worried about email signature image size? Follow this best practice guide.
    For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now