Only send DBMail when query results are present

• ZZartin

  SSC-Dedicated

  Points: 30920

  More actions

  October 29, 2014 at 1:02 pm

  #1755626

  Not that I know of, but if you're cool with losing data from the result set you can just pick a top X row count you know won't exceed the limit.

  Or export the file to someplace on a shared drive and just distribute a link to it.

• Rudyx - the Doctor

  SSC-Forever

  Points: 43695

  More actions

  October 29, 2014 at 1:29 pm

  #1755632

  Try this:

  declare@max_attachment_sizeint,-- in bytes

  @average_rowsizeint,-- in bytes

  @number_of_rowsint,-- used for the TOP in the dynamic select

  @dynamic_selectvarchar(8000)-- dynamic select statement

  select @max_attachment_size=paramvalue

  from msdb.dbo.sysmail_configuration

  where paramname = 'MaxFileSize'

  select@average_rowsize=used_page_count*1024/row_count

  from sys.dm_db_partition_stats

  where object_id=object_id('sysmaintplan_log')

  select @number_of_rows=@max_attachment_size/@average_rowsize

  IF EXISTS(SELECT TOP 1 1 FROM [e009])

  begin

  select @dynamic_select='select top ' + convert(varchar(16),@number_of_rows) + ' * from [e009]'

  exec msdb.dbo.sp_send_dbmail

  @profile_name = 'Default',

  @recipients = 'me@example.com',

  @subject = 'warning',

  @query = @dynamic_select,

  @attach_query_result_as_file = 1,

  @query_attachment_filename = 'warning.csv'

  end

  RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."