sqlcmd - how to skip empty files?

Question

Post reply

sqlcmd - how to skip empty files?

toni_nenova

SSC Veteran

Points: 243
More actions
February 19, 2010 at 1:19 am

#386344

Dear All,
I am using sqlcmd to export *.txt files from DB with batch.
When no data I generate empty file-0 KB.
How to skip it?
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 1

Why you want to skip empty file

i dont think there is any point to keep them ?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

toni_nenova SSC Veteran Points: 243 More actions · Answer 2

Dear Bhuvnesh,

I use batch file in Scheduled Tasks that call sqlcmd every 10 minutes.

This generate many unnecessary empty files.

Thanks,

BrainDonor SSCoach Points: 19252 More actions · Answer 3

Can we have a look at that batch file? I assume your SQL is extracting directly from some tables and creating the export, but would like to see the code.

What you could do is write the data you want to extract to a table (temporary or otherwise) and then check how many rows there are before exporting them.

BrainDonor.

Steve Hall
Linkedin
Blog Site

toni_nenova SSC Veteran Points: 243 More actions · Answer 4

This is a part of batch file with sqlcmd:

SET filenameToni_To_Vladi_PO=Toni_To_Vladi_PO-20%yy%%mm%%dd%-%SortTime%.txt

sqlcmd -S SERVER17 -U sa -P aaaa -d Toni -l 60 -t 180 -s $ -h-1 -W -Q "SET NOCOUNT ON BEGIN TRANSACTION SELECT [Toni Order No],[Order No],[Line No],CONVERT(VARCHAR(20),[Date],23),[Ship-To Code],[Ship-To Name],[Remarks],[Item Code],[Description], CAST ([Quantity] AS INT),[Serial Number],[Toni Document No] FROM dbo.[Toni LTD_$Vladi Reports] WHERE [File Name] = '' AND [Type of Operation] = 0 AND [File Name] = ''UPDATE dbo.[Toni LTD_$Vladi Reports] SET [File Name] = '%filenameToni_To_Vladi_PO%' WHERE [Type of Operation] = '0' AND [File Name] = '' COMMIT SET NOCOUNT OFF" -o "C:\Vladi\%filenameToni_To_Vladi_PO%" -R

Ray Dai SSCommitted Points: 1548 More actions · Answer 5

1. Like BrainDonor said, you can put this in a stored procedure and have your scheduled task call that stored procedure. Inside the SP, you would select information into a temp table and only call

your cmd if there are rows in the table, inside stored procedure you want to use

EXEC master..xp_cmdshell @cmd

2. If you don't like that approach or if you don't have the necessary security settign to use xp_cmdshell, you can always write another batch file and have it delete size=0 files. Add that batch file to your scheduled task after this one.

http://www.techreplies.com/ms-dos-65/bat-file-delete-txt-files-size-590330/[/url]