0

Apologizing in advance if i am posting this in the wrong place. I have a windows batch script that calls a sql query to produce an xml file which gets transferred to a 3rd party. It is scheduled to run every 15 min. My issue is that there are times when there are no records and it creates an empty file. This causes issues when it reaches the 3rd party.

This is the section that is in my .sql file (which runs fine) but I cant figure out how to only produce the xml if there are records in it. 

select @cmd = ' bcp "select * from dbo.WelcomeEmail  CustomerDetail for xml auto, root(''CustomerDetails''), elements" ' + 'queryout "d:\sample.xml"

I tried this but it gave me an error bcp failed:

select @cmd = ' bcp "select * from dbo.WelcomeEmail where email is not null CustomerDetail for xml auto, root(''CustomerDetails''), elements" ' + 'queryout "d:\sample.xml"

if you need me to provide additional info, please let me know. Thanks!

AHiggins
  • 7,029
  • 6
  • 36
  • 54
Rachael
  • 1
  • 1

1 Answers1

0

See this answer: How can I check the size of a file in a Windows batch script?

You could check to see if the file contains zero bytes (or is whatever the size of the file is when there's no data) and then just delete the file.

Community
  • 1
  • 1
Mike Feltman
  • 5,160
  • 1
  • 17
  • 38
  • I am open to that option. I had actually tried it earlier but ran into an issue with that as well. This only works if i run it in the directory where the output file resides. But i want to include it in my batch script which is in another directory. Here is what i was using: for %%F in (D:/WelcomeEmail/CopyWelcomeTest-*.xml) do if %%~zF equ 0 del "%%F" – Rachael Sep 22 '15 at 18:38
  • Do you know how i can get it to work when executing it from a different directory? – Rachael Sep 22 '15 at 18:56