Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-Server-2008
Hi,
 
I have created a trigger to sent mail.
CREATE TRIGGER Trg_log ON [dbo].[log]
  AFTER INSERT
   AS
 
DECLARE @event  NVARCHAR(128)
DECLARE @starttime datetime
DECLARE @endtime datetime
DECLARE @message NVARCHAR(2048)
DECLARE @source NVARCHAR(1024)
 
SET @event  = (SELECT [event] FROM inserted)
SET @starttime = (SELECT [starttime] FROM inserted)
SET @endtime = (SELECT [endtime] FROM inserted )
SET @message = (SELECT [message] FROM inserted )
SET @source = (SELECT [source] FROM inserted )
IF @event = 'OnError'
BEGIN
DECLARE @msg varchar(500)
SET @msg = 'Job Started - "' +CONVERT(varchar, @starttime )+ '" job ended at ' + convert(varchar,@endtime )+ @message
Declare @sub nvarchar(1024)
set @sub = 'Job Failed  - ' + @source
exec usp_send_cdosysmail 'test@gmail.com;,'test@gmail.com,@sub,@msg,'gmail.igiusa.com','textbody'
END
By writing the code, mail is going in the below format
Job Started - "Dec  7 2012  7:33PM" job ended at Dec  7 2012  7:33PMSSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "localhost" failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.
But my expected output is
Job Started - 2012-11-27 13:35:23.000
Job Ended - 2012-11-27 13:35:23.000
Error Message:
SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  
The AcquireConnection method call to the connection manager 
"localhost" failed with error code 0xC0202009.  
There may be error messages posted before this with more information on why the AcquireConnection method call failed.
To generate the above output how can I rewrite my code?
Posted 7-Dec-12 3:54am
Edited 7-Dec-12 4:00am
v2
Comments
djj55 at 7-Dec-12 15:06pm
   
Make sure @message is not NULL or it will NULL @msg, same with @source.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

In your
CONVERT( varchar, @starttime )
specify the correct style[^] you want, in your case style 121 should be used
CONVERT(varchar, @starttime, 121 )
 
You also need to added line feed to the message, use CHAR( 13 ) for this.
 
So for the code you supplied use:
SET @msg = 'Job Started - ' + CONVERT( varchar, @starttime, 121 ) + CHAR( 13 ) + 'Job ended at ' + CONVERT( varchar, @endtime, 121 ) + CHAR ( 13 ) + @message.
To get the line feeds in the @message you should change the code were the value is inserted into the database.
  Permalink  
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Append CHAR(13)+ CHAR(10) wherver you want line breaks...
 
BEGIN
DECLARE @msg varchar(500)
SET @msg = 'Job Started - "' +CONVERT(varchar, @starttime )+CHAR(13)+ CHAR(10)+ '" job ended at ' + convert(varchar,@endtime )+CHAR(13)+ CHAR(10)+ REPLACE(@message, ".", CHAR(13)+ CHAR(10))
Declare @sub nvarchar(1024)
set @sub = 'Job Failed  - ' + @source
exec usp_send_cdosysmail 'test@gmail.com;,'test@gmail.com,@sub,@msg,'gmail.igiusa.com','textbody'
END
  Permalink  
v3
Comments
FranklinRemo at 7-Dec-12 10:31am
   
Kuthuparakkal , its working fine but my requirement is Job Started - 2012-11-27 13:35:23.000 Job Ended - 2012-11-27 13:35:23.000 Error Message: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "localhost" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
FranklinRemo at 7-Dec-12 10:32am
   
but it is displaying like below i.e error message have to be display in separate line Job Started - Dec 7 2012 8:49PM job ended at Dec 7 2012 8:49PM Error Message SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "192.168.6.38.CMSREPORTDB.tuqa" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
Kuthuparakkal at 7-Dec-12 10:38am
   
try now, soln updated, idea is replace . with CHAR(13)+ CHAR(10)
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

Hi!!

Instead of using
DECLARE @msg varchar(500)
SET @msg = 'Job Started - "' +CONVERT(varchar, @starttime )+ '" job ended at ' + convert(varchar,@endtime )+ @message
Declare @sub nvarchar(1024)
set @sub = 'Job Failed  - ' + @source
lines of block, modify it by
DECLARE @msg varchar(500)
SET @msg = 'Job Started - "' +CONVERT(varchar, @starttime )+ '<br/>" job ended at ' + convert(varchar,@endtime )+'<br/>Error Message:<br/>'+ @message
Declare @sub nvarchar(1024)
set @sub = 'Job Failed  - ' + @source
block.

You need to append line break in sql and it will display in email as per your requirement.
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Your Filters
Interested
Ignored
     
0 Shai Vashdi 1,518
1 Manas Bhardwaj 319
2 Tadit Dash 285
3 OriginalGriff 273
4 Peter Leow 205
0 Sergey Alexandrovich Kryukov 9,530
1 OriginalGriff 5,716
2 Peter Leow 4,345
3 Maciej Los 3,540
4 Abhinav S 3,373


Advertise | Privacy | Mobile
Web02 | 2.8.140415.2 | Last Updated 7 Dec 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Use
Layout: fixed | fluid