SQL Server 2005 - Job that writes query results to a text file

[okiiyama]

How do I create a Job that runs a query and writes the query results to a text file in SQL Server 2005?

The query is simple: Select * from ....
The text file resides on a different server, but I've mapped the drive on the server that will be executing the job.
The text file is overwritten each time.

The Job right now is running on a 2000 server as a DTS Package.


Thanks.

 

[philhege]

2005 can run the 2K DTS package. Do you have a requirement to upgrade to SSIS? If not, just import the DTS package.

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
Pity the insomniac dyslexic agnostic. He stays up all night, wondering if there really is a dog.

 

[onpnt]

You can do that but there is a catch. DTS will not be supported in 2008 (unless they changed it) so if its possible to go with SSIS (and it usually is) I would.

For the task like yours other than using BIDS to create the solution over DTS's manager thing it will be almost identical.


[sub]____________ signature below ______________
I am Tedward Keyboardhands!!!
You are a amateur developer until you realize all your code sucks.
Jeff Atwood[/sub]

 

[okiiyama]

Could I write to a text file from a stored procedure in 2005? More to the point.... How can I write to a text file from a stored procedure. I'm thinking executing an SP from a job would be easy.

Thanks again.

 

[nice95gle]

To write to a text file from a SP you would need to use the BCP Utility.

Well Done is better than well said
- Ben Franklin

 

[okiiyama]

Sweetness... Ive gotten the BCP Utility to work premliminarily... Is there a way without writing out a query to include the column headers in the text file automatically?

 

[nice95gle]

Yes, read up on using a format file with BCP.

Well Done is better than well said
- Ben Franklin

 

[nice95gle]

I was thinking of something else.Sorry, You can't write the column names using BCP. But there are many examples on the internet that shows how to do it.

Well Done is better than well said
- Ben Franklin

 

[okiiyama]

Yeah, I'm not sure if its the best approach, but I created a view that unions the column names with the records, and then querying the view. That has solved that problem.

Thanks for the help.