how to display line by line code in sqlserver?

Question

2.00/5 (1 vote)

See more:

SQL-Server-2008

Hi,

I have created a trigger to sent mail.

SQL

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

FranklinRemo

Updated 7-Dec-12 4:00am

André Kraak

v2

Add a Solution

Comments

Corporal Agarn 7-Dec-12 15:06pm

Make sure @message is not NULL or it will NULL @msg, same with @source.

3 solutions

Add a Solution

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

André Kraak · Answer 1 · 2012-12-07T04:11:00

In your

SQL

CONVERT( varchar, @starttime )

specify the correct style[^] you want, in your case style 121 should be used

SQL

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:

SQL

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.

Kuthuparakkal · Answer 2 · 2012-12-07T04:16:00

Solution 2

Append CHAR(13)+ CHAR(10) wherver you want line breaks...

SQL

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

Posted 7-Dec-12 4:16am

Kuthuparakkal

Updated 7-Dec-12 4:37am

v3

Comments

FranklinRemo 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 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 7-Dec-12 10:38am

try now, soln updated, idea is replace . with CHAR(13)+ CHAR(10)

Kaushikhalvadiya · Answer 3 · 2012-12-07T04:22:00

Hi!!

Instead of using

SQL

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

SQL

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.

how to display line by line code in sqlserver?

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

how to display line by line code in sqlserver?

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us