Detecting file size before sp_send_dbmail

Question

We're using the following with a 10MB limit for attachments.

EXEC msdb..sp_send_dbmail
    @recipients         = '[e-mail]',  
    @subject            = 'foo bar',
    @file_attachments   = 'C:\xxx\yyy\myFile.xlsx,
    @body_format        = 'html',
    @body               = 'norf';

Is it possible in the SQL Server scripts to check the size of myFile.xlsx before running this script?

Answer 1

I have a solution by using Batch Files:

[C:\FileSize.cmd]

@echo %~z1

Now you can get size of <your_file> by using that file:

DECLARE @t table(filesize int)

INSERT INTO @t
EXEC [xp_cmdshell] @command_string = 'C:\filesize.cmd <your_file>'

SELECT TOP(1) * FROM @t