Output file

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Monica - 30 Jul 2008 23:13 GMT

I have an osql statement that queries a database.

If the query returns any rows I want them written to a file, so I redirected
the output of my osql statement to a file (results.txt). But if there are no
rows that comply with the condition of the query I do not want any files
created. The way I have it now an empty file is created.

This is my osql statement:
osql -U sa -S myserver -d db_Where -n -i c:\SQLquery.txt -o c:\results.txt

Is there a way I can write this so that no output file is created when no
rows comply with the query condition?

I was thinking of checking the file size and if it is zero then delete the
file, but I do not know how to do that either from a .bat file. Can it be
done?

Reply to this Message

Jonathan Psaila-Depasquale - 31 Jul 2008 16:23 GMT

Hi Monica,

I've taken the course of action you suggested in the last paragraph and
wrote you a small dos script that carries this out for you. Note that you
should append the osql command to the beginning of this script. The script
assumes that you send your data to a text file called tempresults.txt. You
use that name to check for file size. If the file size = 0 bytes you delete
the file. If the file size > 0 bytes you rename the file to results.txt.
Note that you have to run the script from the directory in which the file
tempresults.txt resides. Otherwise please adapt the script to include the
necessary directory paths.

--------------------

echo off

if not exist tempresults.txt GOTO EXITTO

set "filename=tempresults.txt"

for %%A in (%filename%) do set "filesize=%%~zA"

if "%filesize%"=="0" GOTO ZEROFILESIZE
rename tempresults.txt results.txt
echo File was greater than 0 bytes
GOTO EXITTO

:ZEROFILESIZE

del tempresults.txt
echo File was 0 bytes

:EXITTO

echo on

--------------------

Any questions please let me know.

Regards,
Jonathan

>I have an osql statement that queries a database.
>
[quoted text clipped - 14 lines]
> file, but I do not know how to do that either from a .bat file. Can it be
> done?

Reply to this Message

Monica - 31 Jul 2008 17:48 GMT

Thanks Jonathan,

This worked OK. Though I do not understand what the command for %%A in
(%filename%) do set "filesize=%%~zA" does. Could you explain?

Regards,

Monica

> Hi Monica,
>
[quoted text clipped - 56 lines]
> > file, but I do not know how to do that either from a .bat file. Can it be
> > done?

Reply to this Message

Jonathan Psaila-Depasquale - 31 Jul 2008 18:42 GMT

Hi Monica,

Basically this line could be translated into the following pseudocode:

For every file in the array represented by the variable filename set the
variable filesize to the size of the file.

Two things to note here:
1. Even though you only entered one filename, the script is treating the
variable as an array of filenames, hence the "for every file do a command"
syntax.
2. %%~zA returns the size of the file.

I hope this explains it better.

Regards,
Jonathan

> Thanks Jonathan,
>
[quoted text clipped - 76 lines]
>> > be
>> > done?

Reply to this Message