Post reply

Text file i/o from within T-SQL

  • February 13, 2011 at 1:11 pm

    #245459

    Does anyone know of a book that has a detailed and decent section on file i/o from within t-sql? I'm involved in a project that needs to both read and write to text files that I receive (or transmit) to external entities. If it was just limited to that, I think I could find and learn some tricks using the command shell, but it would be nice if I could also move processed files to an archive location. I've done all this before, but only by building a front-end using other programming languages, like VFP or C#. I'd like to do all of this within T-SQL so I don't need to deal with any other platform.

    Thanks in advance.

    David

  • February 13, 2011 at 2:09 pm

    #1286195

    File IO and file system operations are not T-SQL's strong points. I would suggest doing them in a front end language, or at least something like SSIS. T-SQL is pretty much limited (without using extended procedures or CLR) to bulk insert, openrowset and not much else.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • February 14, 2011 at 4:00 am

    #1286370

    As GilaMonster says:

    File IO and file system operations are not T-SQL's strong points.

    I suppose you also want to logg what is happening and send messages.

    Why not continue to use C# in combination with ADO and T-SQL.

    I use VB.net as an "old" programmer in stead of SSIS.

    Let us know how you continue.

  • February 14, 2011 at 6:01 pm

    #1286806

    Gosta Munktell (2/14/2011)

    Why not continue to use C# in combination with ADO and T-SQL.

    I use VB.net as an "old" programmer in stead of SSIS.

    Let us know how you continue.

    Heh... lemme quote the OP... 😉

    I'd like to do all of this within T-SQL so I don't need to deal with any other platform.

    --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)

  • February 14, 2011 at 6:06 pm

    #1286807

    RaiderX (2/13/2011)

    Does anyone know of a book that has a detailed and decent section on file i/o from within t-sql? I'm involved in a project that needs to both read and write to text files that I receive (or transmit) to external entities. If it was just limited to that, I think I could find and learn some tricks using the command shell, but it would be nice if I could also move processed files to an archive location. I've done all this before, but only by building a front-end using other programming languages, like VFP or C#. I'd like to do all of this within T-SQL so I don't need to deal with any other platform.

    Thanks in advance.

    David

    David,

    Reading, writing, moving, and deleting WHOLE files isn't difficult from T-SQL. My question would be, is it ever necessary to update an existing file? That's not so much fun.

    For the moving of files to an archive location, you can use the DOS MOVE command. You just need to make sure that whatever user the SQL Server Service starts up as has the correct privs to both the source and destination whether they be Shares, UNCs, or attached drives.

    --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 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply