SQL Server 2008 - Output Query Results to Pipe Delimited Text File

Posted on 2012-09-04
Last Modified: 2014-01-25
There is a lot out there about this, but I admit I am having a hard time following and some of it does not work with SQL Server.  Can someone assist?

I have queries that need to be executed and output to pipe-delimited text.  The text file is linked to an Access db.  


SQL Server 2008 - many simple queries are executed in order and result sets saved as text files to local c-drive.

1.  Execute query "Select * from AAA"
2.  Results appear as pipe-delimited
3.  Save as "C:/extracted_data/aaa.txt"
4.  Access Db already has a linked connection to aaa.txt

Step 1 automation......

1.  Automate the "write to text" part of eace query
2.  Run all queries automatically in order
3.  All output is sitting out there waiting for the Access user to connect.
Question by:MarxChuck
    LVL 75

    Expert Comment

    by:Aneesh Retnakaran
    Is this part of a home work / test ?

    Author Comment

    No.  I'm just not too bright with "advanced" SQL and need to automate some very labor intensive queries.  I have already executed all of the output from these queries as mentioned (1 at a time with a manual save-as) and my client has changed the base criteria and they all have to be executed again.  The plan was to learn how to automate this over time, and that time has come faster than expected.
    LVL 75

    Expert Comment

    by:Aneesh Retnakaran
    In sql server,you can do this by creating a sql job..

    Right click under sql server agent -> jobs

    and you can put these scripts as individual job steps or one sql job steps.

    Author Comment

    Is SQL Server Agent just for 2012?  I don't see a reference for this in 2008.
    LVL 67

    Accepted Solution

    All SQL Server releases (with exception of the "Express"-type editions) install an SQL Server Agent service which will process scheduled tasks (Backups, DB reorg and many more). If you allow the Agent service to run, and then schedule SQL tasks, you can automate the job.

    On the other hand you can also use CMD batch files, like
    sqlcmd -E -S YourServer\YourInstance -d YourDB -Q "YourSQL" -o "TargetFile.txt"

    Open in new window

    Using -s ^| you can define a pipe as column delimiter, and provide -h -1 to remove headers, aso.
    LVL 38

    Assisted Solution

    by:Jim P.
    You can use a SQL Agent job to execute and put the results table and then link the Access table in the DB and pull data directly, skipping the text file.

    There are numerous solutions. Maybe if you describe the process some more, we can get you a great solution.

    Author Closing Comment

    Thanks folks.  Sorry for the delay in response!

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    Via a live example, show how to shrink a transaction log file down to a reasonable size.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

    730 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now