[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2219
  • Last Modified:

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

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.  

Today.....

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.
0
Chuck Morrison
Asked:
Chuck Morrison
2 Solutions
 
Aneesh RetnakaranDatabase AdministratorCommented:
Is this part of a home work / test ?
0
 
Chuck MorrisonIndependent Consultant (Healthcare)Author Commented:
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.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
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.
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
Chuck MorrisonIndependent Consultant (Healthcare)Author Commented:
Is SQL Server Agent just for 2012?  I don't see a reference for this in 2008.
0
 
QlemoC++ DeveloperCommented:
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.
0
 
Jim P.Commented:
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.
0
 
Chuck MorrisonIndependent Consultant (Healthcare)Author Commented:
Thanks folks.  Sorry for the delay in response!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now