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,


Barrysounds 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.|||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'

Cheers.|||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,


Barry