CODEWITHSUNDEEP
sql-servert-sql
Lordy
Lordy

Reputation: 81

How to check if file is empty in SQL script?

Below are my jobs created for my stored procedure "ListDailyInbound855-InputScript.sql. First job (Generate File) creates the ouput "D:\DailyReports\Inbound855.txt"

sqlcmd -i D:\InputSQLScripts\ListDailyInbound855-InputScript.sql  
-S localhost -E -s " " -o D:\DailyReports\Inbound855.txt

Second job (Email File) creates an email to be sent out to [email protected].

Declare @ExeFilePath varchar(255)
Set  @ExeFilePath = 'D:\Applications\EmailUtility\Mailer '

Declare @cmd varchar(500)
Set @cmd = @ExeFilePath + ' "Host:smtp.emailserver.com"  "To:EDISupport@ 
company.com"   "Cc:support@ company.com" "From:support@ company.com" 
"Disp:EDI Support" "Sub: Daily Inbound 855 Report" "Msg:Please find 
report attached." "Atch:D:\DailyReports\Inbound855.txt"'

Declare @output int
Exec    @output = master..xp_cmdshell @cmd,no_output

If  @output <> 0
Begin
    RaisError('Error while sending email.', 16, 1)
    Return
End

Problem: I need to check first if the output "D:\DailyReports\Inbound855.txt" is non-empty before using it as attachment to the email and therefore not receiving it as empty. My goal is only to receive email alerts if attachment has content or non-empty. Any help is appreciated.

Upvotes: 3

Views: 3847

Answers (2)

valex
valex

Reputation: 24144

You can use the following CMD line to find out the file size (in this example "c:\test.txt"):

for %I in ("c:\test.txt") do echo %~zI;

We need to get result into the MSSQL variable. So you should use EXIT instead of ECHO to return result of a command as an exit code. In this case you can assign it to MSSQL variable.

Another issue when the file doesn't exist. In this case you will get an error because %~zI is empty. To avoid this just add 0 before %~zI. So we get 00,01,02,.. instead of 1,2,.. and when it is output as an EXIT code it will be converted to the int value 0,1,2,3,... If file doesn't exist you get 0.

Here is the test script:

DECLARE @output int
EXEC   @output = master..xp_cmdshell 'for %I in ("c:\test.txt") do EXIT 0%~zI;',no_output;
SELECT @output;

If @output = 0 then file doesn't exist or size of the file is 0.

Upvotes: 0

Peter B
Peter B

Reputation: 24147

Since you already are using an executable, the best approach may be to modify it to include the check for empty; assuming you have the sources of course.

As an alternative you can try queries such as the following to load the file into a recordset:

SELECT * FROM OPENROWSET(BULK N'<filename>', SINGLE_CLOB ) AS Contents -- for ASCII/ANSI
SELECT * FROM OPENROWSET(BULK N'<filename>', SINGLE_NCLOB) AS Contents -- for UNICODE

And then check the result using additional statements:

DECLARE @LEN INT
SELECT @LEN = Len(Contents.BulkColumn) FROM
    OPENROWSET(BULK N'<filename>', SINGLE_CLOB) AS Contents

IF @LEN > 0
BEGIN
    -- rest of code
END

Upvotes: 3

Related Questions