Post reply

Problem exporting SQL Data to a text file

  • February 5, 2006 at 9:29 pm

    #170878

    I need help to export sql data to a text file. The text file must be a fixed length (data is going to be read by a legacy Cobol system, yes ...)

    I'm using the following code:

    INSERT INTO

    OpenRowSet('Microsoft.Jet.OLEDB.4.0'

    ,'Text; Database=D:\External'

    ,'select * from authors2.txt')

    select *

    from dbo.authors

    The contents of the schema.ini file is:

    [authors2.txt]

    ColNameHeader=False

    Format=FixedLength

    Col1=au_idchar Width 10

    Col2=au_lnamechar width 20

    Col3=au_fnamechar width 20

    Col4=phonechar width 15

    Col5=addresschar width 20

    Col6=citychar width 10

    Col7=statechar width 10

    Col8=zipchar width 5

    Col9=contractchar width 10

    it work's if the output is defined as delimited (ColNameHeader=True with Format=TabDelimited or Format=CSVDelimited)

    Help!!

  • February 5, 2006 at 10:41 pm

    #618898

    You can also consider using bcp to do this.

  • February 6, 2006 at 5:08 am

    #618923

    Use bcp or DTS.

  • February 6, 2006 at 7:18 am

    #618935

    Yes, I consider bcp and DTS, but the problem is that the process need's to be started by the user. There isn't a fixed time to schedule a export process, and I think if I could solve in this way it is more clean and transparent

    Thanks,

  • February 6, 2006 at 7:55 am

    #618946

    Create a DTS package..and create a batch file with a dtsrun acommand. Have the user run the batch file as needed.

    HTH

    Mathew J Kulangara
    sqladventures.blogspot.com

  • February 7, 2006 at 2:31 am

    #619185

    Agree with Mathew, slight variation would be to use a DTS package then schedule it as a job under SQL agent and either you or if end user has access just manually run this job.

  • February 7, 2006 at 10:04 am

    #619345

    For What its worth have you tried.

    INSERT INTO

    INSERT INTO

    OpenRowSet('Microsoft.Jet.OLEDB.4.0'

    ,'Text; Database=D:\External'

    select

    Convert(Char(10),au_id),

    Convert(Char(20),au_lname),

    Convert(Char(20),au_fname),

    Convert(Char(15),phone),

    Convert(char(20),address),

    Convert(Char(10),city),

    Convert(Char(10),state),

    Convert(Char(5),zip),

    Convert(Char(10),contract)

    from dbo.authors

    When I tried the example I experianced truncation issues because field defs between pubs.authors and authors2.txt did not match and the query failed.  This seemed to solve the problem.

     

Viewing 7 posts - 1 through 6 (of 6 total)

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