Query Analyzer - Results to File

[be1s]

I'm running a set of SQL statements where the results get saved to a file. I've set this up in Query analyzer under Tools|Options|General. This works great. However, This will become a scheduled process. I am not doing a straight export because I need to group data and do some other formatting stuff, NOT in the way that SQL groups but in the way that Crystal Reports does it where group appears once then the details. So my question is, is there a way to do what QA does programmatically?

 

[SQLBill]

You say it's going to be a scheduled process. That means it will be a JOB. (Enterprise Manager>Management>Jobs). When you create a job, when you create the individual Step, there is a tab that lets you do advanced options. There you can set how the results will be outputted (Results). You can choose to have it save to a file. It can be tab, space, etc separated columns.

Check it out.

-SQLBill

 

[sodakotahusker]

Query Analyzer is just a tool to expedite working with SQL server for development purposes. You'll need to save your query(s) into a stored procedure to create a job as SQLBill has pointed out. You can also do the same thing by writing a program to invoke the stored procedure and use the Windows scheduler to run that job. In that program you could actually make the call to Crystal and do the whole ball of wax.

 

[be1s]

Thanks to both.

Yes i will scheduling this task as a job in Enterprise Manager. Under the "Steps" tab does the Command field accept xp_cmdshell?

Thanks you sodakotahusker, your suggestion is definitely another solution, though I'd have to install Cystal Report.

 

[be1s]

I was able to run xp_cmdshell from the "command" field. But realized I didn't need to. So I'm running the store proc and requesting the results to be placed into a file. That works, however, when I view the file, there's extra code that SQL places in the file. Anyway to get around this?

 

[SQLBill]

What 'extra code'??? Do you mean the number of rows affected? You can stop that by starting the script with:
SET NOCOUNT ON

-SQLBill

 

[be1s]

So I've set this up as a job with the results placed in a text file. I have the "SET NOCOUNT ON" included in my code so that it doesn't return the number of rows affected.

This is the 'extra stuff' it adds to top of my text file:

Job 'PendingClaimsAlertMD_pcm_claimsnet' : Step 1, 'Execute usp_ccm_PendingClaimAlertMD' : Began Executing 2004-11-19 13:07:00

 

[jdgonzalez]

From what you describe, that date looks like the output file you can create within the job and not the result file you can generate from within the DTS package or T-sql.

 

[SQLBill]

That 'header' information shows up on all jobs. You could create a script that opens that file and deletes that line. But you would have to do that from the OS and not from SQL Server.

I have never found a way to 'turn off' that header information.

-SQLBill

 

[be1s]

Thank you, for all your responses. I've got it working fine.