SQL Server 2005 sp_send_dbmail

• • Reply

  13 years ago

  by Earl

  Earl Collins

  , United Kingdom

  Joined 13 years ago

  Hi
  I'm experiencing problems with the @query parameter in the sp_send_dbmail SP (SQL Server 2005).
  
  Using the following code:
  exec msdb.dbo.sp_send_dbmaill
  @profile_name='SQL2005Profile',
  @recipients='[email protected]',
  @body='Test Message',
  @subject='URGENT!!!!',
  @query='Select * From Table_1'
  
  If i run the above code as a SQL Query it works fine, but when I add it into a trigger it always crashes causing the process to be suspended, showing the last statement as "Select * From table_1'. In order to regain control of program the process has to be killed.
  
  Code for Trigger:
  Create triger dbo.update_table_1
  On dbo.table_1
  After Update
  As
  If Update(col_3)
  Begin
  Set NOCount ON;
  exec msdb.dbo.sp_send_dbmaill
  @profile_name='SQL2005Profile',
  @recipients='[email protected]',
  @body='Test Message',
  @subject='URGENT!!!!',
  @query='Select * From Table_1'
  End
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  

  There is no record in any of msdb.dbo.sysmail_....... views to show that mail has failed.

  
  Does anyone know why this is happening? (Login Account has full administrative permissions)
  
  Thanks in advance
  
  
  

  Report abuse