如何在sqlserver中逐行显示？

问题描述：

我创建了一个发送邮件的触发器。

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?

答

在

CONVERT( varchar, @starttime )

指定正确的风格 [ ^ ]你应该使用样式121

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

CONVERT(varchar, @starttime, 121 )

您还需要为消息添加换行符，为此使用 CHAR（13）。

所以您提供的代码使用：

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.

获取换行符 @message 如果将值插入数据库，则应更改代码。

To get the line feeds in the @message you should change the code were the value is inserted into the database.

附加CHAR（13）+ CHAR（10）你想要换行的东西...

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

嗨!!

而不是使用

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

阻止。

您需要在sql中附加换行符，它将根据您的要求显示在电子邮件中。

block.

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

如何在sqlserver中逐行显示？

相关推荐