July 2, 2002 at 8:36 am
This is an easy one:
I have a very simple strored proc that selects data from a particular table. I want this sproc to execute and send the output to a text file. I cannot find the correct syntax to out put the recordset. I'd like to do this in TSQL. Any recommendations?
Christine
Aurora
July 2, 2002 at 9:28 am
You'd have to use xp_cmdshell and call isql/osql to pipe to a text file, use DTS, or bcp.
Steve Jones
July 2, 2002 at 11:20 am
Another possibility would be to define the text file as a linked server using Jet. However, this does require you to go into ODBC and create the schema for the text file.
From MSDN: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_adda_8gqa.asp
Example H shows how to create a linked server to a text file.
An example schema.ini (must exist in same directory of the text file) for exporting the authors table is:
[schema.ini]
ColNameHeader=False
Format=CSVDelimited
MaxScanRows=0
CharacterSet=OEM
[dump.txt]
ColNameHeader=True
Format=TabDelimited
MaxScanRows=0
CharacterSet=ANSI
Col1=AU_ID Char Width 11
Col2=AU_LNAME Char Width 40
Col3=AU_FNAME Char Width 20
Col4=PHONE Char Width 12
Col5=ADDRESS Char Width 40
Col6=CITY Char Width 20
Col7=STATE Char Width 2
Col8=ZIP Char Width 5
Col9=CONTRACT Bit
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply