Can you specify Query results file name in query itself?

[MarkLafferty]

I am running MS SQL Server 2008.

I frequently need to manually run a large quantity of query files at a time. I need to log the results of the queries to a file without having to enter a new file name with each query executed.

I wanted to automate the running of the batch of queries through the OSQL command specifying an output file, but
each query builds on the query executed prior to itself so if the query has errors, I need to fix the errors before proceeding to the next query.

If I could include the results file name within the queries themselves, it would allow me to run through the batch much quicker.

Is there a command to specify the results file name within the query itself? Or is there a way to specify a static results file name within the Management studio?

Any help is appreciated!

Thanks,

MarkLafferty

[ck9663]

There are a couple of solutions that I can think of:
1. SSIS. I have not tried to make it to spit out to a dynamic file name, but I heard it's possible.
2. Create a short SP that dynamically build your query and export using BCP.

I believe either one can do error handling.

Good Luck!!!

~~ CK

[MarkLafferty]

Thanks for the suggestion CK.

After running through a number of different scenarios, I ended up with the following batch file process:

echo Release <Job Name> SQL Scritps Executed> <Location of Log Files>\<Log File Name>.txt
echo ------------------------------------->> <Location of Log Files>\<Log File Name>.txt

call osql -E -i <Location of Script>\<Script1 Name>.SQL
if %errorlevel% neq 0 echo SQL Error occurred - close window to cancel or hit any key to continue - %errorlevel% 1>&2 & pause
if %errorlevel% neq 0 echo <Script1 Name>.SQL had errors that were resolved >> <Location of Log Files>\<Log File Name>.txt
if %errorlevel% equ 0 echo <Script1 Name>.SQL - Query executed Successfully.>> <Location of Log Files>\<Log File Name>.txt

call osql -E -i <Location of Script>\<Script2 Name>.SQL
if %errorlevel% neq 0 echo SQL Error occurred - close window to cancel or hit any key to continue - %errorlevel% 1>&2 & pause
if %errorlevel% neq 0 echo <Script2 Name>.SQL had errors that were resolved >> <Location of Log Files>\<Log File Name>.txt
if %errorlevel% equ 0 echo <Script2 Name>.SQL - Query executed Successfully.>> <Location of Log Files>\<Log File Name>.txt

|
|
\/
call osql -E -i <Location of Script>\<Script100 Name>.SQL
if %errorlevel% neq 0 echo SQL Error occurred - close window to cancel or hit any key to continue - %errorlevel% 1>&2 & pause
if %errorlevel% neq 0 echo <Script100 Name>.SQL had errors that were resolved >> <Location of Log Files>\<Log File Name>.txt
if %errorlevel% equ 0 echo <Script100 Name>.SQL - Query executed Successfully.>> <Location of Log Files>\<Log File Name>.txt

The pause allows me to check the error encountered and kill the process if necessary or continue to the next script.