Click here to Skip to main content
11,584,288 members (62,710 online)
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
0 OriginalGriff 430
1 Sergey Alexandrovich Kryukov 369
2 Dave Kreskowiak 260
3 Richard Deeming 220
4 virusstorm 194
0 OriginalGriff 1,663
1 Sergey Alexandrovich Kryukov 1,229
2 Abhinav S 701
3 Dave Kreskowiak 667
4 Suvendu Shekhar Giri 568


Advertise | Privacy | Mobile
Web04 | 2.8.150603.1 | Last Updated 7 Dec 2012
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100