Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2005
    Posts
    58

    Unanswered: TSQL Text File Output

    How do I output a table as a txt file using tsql?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Is that the Sybase or the Microsoft flavor of Transact-SQL? What kind of text file (CSV, Tab delimited, other)? What do you plan to do with the file once it is created? There are often much simpler and safer ways to do things than explicitly creating a file.

    -PatP

  3. #3
    Join Date
    Oct 2005
    Posts
    58
    Microsoft SQL Server. I want to export a comma delimited .txt file from a table. I've done this before and can't remember how I did it. Any suggestions on simplifying this?

  4. #4
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    From command prompt:
    osql.exe -S YourServerName -U sa -P yourpassword -Q "EXEC sp_who2" -o "E:\output.txt"
    From T-SQL:
    EXEC master..xp_cmdshell 'osql.exe -S YourServerName -U sa -P yourpassword -Q "EXEC sp_who2" -o "E:\output.txt"'
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  5. #5
    Join Date
    Oct 2005
    Posts
    58
    Cool rudra! What is the syntax for the second line if I want to use window authentification?

    Thanks

  6. #6
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Try this ...

    EXEC master..xp_cmdshell 'osql.exe -S YourServerName -E -Q "EXEC sp_who2" -o "E:\output.txt"'
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  7. #7
    Join Date
    Oct 2005
    Posts
    58
    Thanks, perfect!!!!!

  8. #8
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    Also, check out bcp.exe. It's designed just for this, and can also put the data back for you. used for bulk copying into/out of sql server.

  9. #9
    Join Date
    Oct 2005
    Posts
    58
    Hey, can you give me an example syntax to utilize for "bcp.exe"?

  10. #10
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by asahi
    Hey, can you give me an example syntax to utilize for "bcp.exe"?
    Check this ...Link
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

Posting Permissions

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