How to handle attachement file size

• Pulivarthi Sasidhar

  SSCertifiable

  Points: 6706

  More actions

  February 8, 2011 at 2:48 am

  #148342

  Hi,

  I am using the following code. In both ways i was failed to send mail.

  So please help me to send a mail without attachment even the file size is big.

  BEGIN TRY

  EXEC msdb.dbo.sp_send_dbmail

  @recipients=@ToMail,

  @profile_name ='XXX_YYY',

  @subject ='Subject',

  @body='Please see attachment for details',

  @file_attachments=@FilePath

  END TRY

  BEGIN CATCH

  EXEC msdb.dbo.sp_send_dbmail

  @recipients=@ToMail,

  @profile_name ='XXX_YYY',

  @subject ='Subject',

  @body='File attachment or query results size exceeds allowable value of 1000000 bytes.Please contact Admin/Support for more information'

  END CATCH

  OR

  Declare @rtn INT

  EXEC @rtn=msdb.dbo.sp_send_dbmail

  @recipients=@ToMail,

  @profile_name ='XXX_YYY',

  @subject ='Subject',

  @body='Please see attachment for details',

  @file_attachments=@FilePath

  IF @rtn=1

  EXEC msdb.dbo.sp_send_dbmail

  @recipients=@ToMail,

  @profile_name ='XXX_YYY',

  @subject ='Subject',

  @body='File attachment or query results size exceeds allowable value of 1000000 bytes.Please contact Admin/Support for more information'

  Thanks,

  Pulivarthi Sasidhar

• avinashily

  Say Hey Kid

  Points: 661

  More actions

  February 8, 2011 at 10:00 am

  #1283835

  hi sasidhar,

  your code is correct.

  have you configured database mail in sql server.

  EXEC msdb.dbo.sp_send_dbmail

  @recipients='avinash@abc.com', ---where abc is the domain

  @profile_name ='avinash', --avinash is the profile created while configuring the database mail

  @subject ='Subject', --subject

  @body='Please see attachment for details', --body

  @file_attachments='c:\a.txt' --attachment

  all the things are correct in your code, but have created a profile in database mail?

  if not go to management folder in object explorer from ssms and right click the database mail and select configure database mail.

  or else execute the following script.

  -- Create a Database Mail profile

  ---suppose abc.com is the domain name

  EXECUTE msdb.dbo.sysmail_add_profile_sp

  @profile_name = 'avinash',

  @description = 'Notification service for SQL Server' ;

  -- Create a Database Mail account

  EXECUTE msdb.dbo.sysmail_add_account_sp

  @account_name = 'SQL Server Notification Service',

  @description = 'SQL Server Notification Service',

  @email_address = 'avinashily@abc.com',

  @replyto_address = 'avinashily@abc.com',

  @display_name = 'SQL Server Notification Service',

  @mailserver_name = 'smtpserver.domain_name' ;

  -- Add the account to the profile

  EXECUTE msdb.dbo.sysmail_add_profileaccount_sp

  @profile_name = 'avinash',

  @account_name = 'SQL Server Notification Service',

  @sequence_number =1 ;

  and then right click on sql server agent and go to properties

  now go to alert system and there you will find the check box 'enable mail profile'

  enable it. i hope it should work.

  even then if it is not working then see in the surface area configurations

  surface area configuration for features-->database mail----> enable database mail procedures

  then try it again, it should work.