SQL Server Performance Forum – Threads Archive
Write to File…I need to do the following and would like to hear some recommendations on how to do it: 1. Write the results of a query (via a stored procedure) to a file for use by a COBOL program.
2. I will format the results using the LEFT function since the results need to be a specific length and the datatype on most of my fields is nvarchar. Can I write the contents to a file using T-SQL? Thanks… [8D] ———-
MS, MCDBA, OCA, CIW
tkelley, See if this helps:
http://sql-server-performance.com/forum/topic.asp?TOPIC_ID=2578 Jon M
In a nutshell: Use osql with the /o redirection switch. Nathan H.O.
IMO dont let people persuade you into using DTS designer for this task, its a headache to maintain and often its a huge amount of work to customise the output to how you want it. As vbkenya says, osql is a very nice solution, but depending on your requirements, you may want more control over your output without having to manually start coding logic into your routines. My requirements were to output many tables (one ascii file per table), no headers or footers, and fixed column (ie, space delimited). I wrote user defined functions to take any data type, and return a fixed format string from them (ie, ints came back as an 12 character string, dates as a set format 10 char string etc).
Then I wrote a stored procedure to whip through each table.. evaluate each column and build a stored procedure for that specific table, using the UDFs for each field, which returned the data as a series of 1 column rows (strings). Then… I write a stored procedure which, given a table name, calls the relevant stored proc, and uses osql to dump the results of that proc to the given filename. The benefit? Already Ive had to change a few file formats several times. Since each table has its own stored proc, its been a simple case of commenting out one field or another, or adding more fields. 30 second jobs!
The downside? You end up having a stored proc for each table you want to output. Might not be relevant to you but if youre outputting lots id wholeheartedly recommend this way for simplicity and maintenance. If you think theyll be useful, youre welcome to have the set of procs/UDFs I wrote which will do all the hard work for you.