Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2005
    Avon Lake, OH

    Unanswered: Creating a SQL script file from a stored procedure

    I'd like to create a SQL script file from within a stored procedure. How do I:

    1. Open the text file
    2. Write to it
    3. Close it when done

    Thanks in advance,


  2. #2
    Join Date
    Nov 2004
    on the wrong server
    Provided Answers: 6
    sounds like a job better accomplished through application code. filesystem object in vbscript perhaps.

    but if you insist, perhaps I would write my sql string to a table and then fire a dts package from my sp to export the field to a text file.

    what are you trying to accomplish? I can't imagine how this might be particularly useful.

    more details would be helpful.

    are you trying to open an application on the server side. Not a good idea. Saw someone trigger Access to open on the server side from the web once. If the the user closed the browser or something that instance of Access just stayed open on server and the mess this caused until I fixed it.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Oct 2002
    BC, Canada
    You can use master..xp_cmdshell extended stored proc to run DOS command. It should look like

    Exec master..xp_cmdshell 'Echo Whatever > MylogFile.Txt'

    Marin Kostadinovic
    DIMM Info Systems Inc.

  4. #4
    Join Date
    Jan 2005
    Avon Lake, OH
    Thrasymachus and mkostadinovic, thank you for your replies.

    I ended up using osql to output the text from the SQL code:

    osql -U xx -P xxxxxx -S (local) -d xxxx -h-1 -w 4000 -n -i "C:\...\ExtractUpdatePlateData.sql" -o "C:\...\Insert plate data.sql"

    In case you are still wondering why, the reason for doing this was to script changes made to the contents of a number of database tables from ~20 updates that had built up over the last 18 months or so. I needed to be able to extract out the data, but had a constraint that I couldn't just use a file containing the data, and a script to import it. Here is part of the SQL from ExtractUpdatePlateData.sql:

    SELECT InsertScript = 'INSERT INTO tblUPDsecttype (vv_secttype, vi_proctype, vi_numwells, vv_descrip) VALUES (' + '''' + vv_secttype + '''' + ', ' + CAST(vi_proctype AS NVARCHAR) + ', ' + CAST(vi_numwells AS NVARCHAR) + ', ' + '''' + vv_descrip + '''' + ')'
    FROM tblUPDsecttype

    This is how it appears in Insert plate data.sql:

    INSERT INTO tblUPDsecttype (vv_secttype, vi_proctype, vi_numwells, vv_descrip) VALUES ('ANO2B', 1, 96, 'ANAEROBE SYSTEMIC Man MiC')

    It all seems to work fine (and has saved me a week's tedious work already).

    Best regards,

    Last edited by bcummings; 03-03-05 at 07:48. Reason: Typos

Posting Permissions

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