数据库邮件查询问题 [英] Database Mail Query Problem

问题描述

我以html格式创建数据库邮件，这是我的查询，我没有收到邮件。你可以打电话给我，我在QUERY中做错了。配置很好。问题在于查询。请帮助我

Hi,

I Created Database mail in html format, Here is my query, Iam not getting mail. Could u please tel me where i done mistake in QUERY. Configuration are fine.Problem is in query. Kindly help me

USE [msdb]
GO
/****** Object:  StoredProcedure [dbo].[UspEsRairexclusiveRpt]    Script Date: 12/23/2013 10:53:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[UspEsRairexclusiveRpt]

AS
EXEC msdb.dbo.sp_send_dbmail   
@profile_name = 'Stage_Dbserver',
@recipients = '[e-mail]',
@subject = 'Cbazaar orders assigned to Rair exclusive',
@query =  'SET NOCOUNT ON; 
DECLARE @HTML NVARCHAR(MAX) ;
SET @HTML =
 ''<table>'' +

''<tr><th>OrderNumber</th></tr>'' +''<tr><th>ProductCode</th></tr>'' +
 Cast((SELECT T.TrnIntTrackNo as "td",'''',S.WhIntPrdCode as "td"
      FROM	dbo.CbzTempTrnMst AS T
LEFT JOIN dbo.CBzStockOnHand AS S WITH (NOLOCK) 
		ON S.LineItemId=T.LineItemId
LEFT JOIN dbo.CbzGenMst AS G WITH (NOLOCK) ON G.GenIntId=S.WhIntPrdSize
WHERE	 S.LineItemId IS NOT NULL
		AND S.StockStatus=3 AND BitFlag=1
		AND S.WhIntEWMID IN (34)
        FOR XML PATH(''tr'')) as NVARCHAR(MAX)) +
 ''</table>''
@body= @HTML,
@body_format = ''HTML'' 
',
			@attach_query_result_as_file = 0,
    	@query_attachment_filename = 'RairExclusiveRpt.XLS',
  	    @query_result_separator = '	',
		@execute_query_database = 'estoreadmin_stage',
		@query_result_header = 1,
		@query_result_no_padding = 1,
		@exclude_query_output = 1;

推荐答案

我假设其他邮件发送正常。但是，您没有给我们足够的信息。如果运行SQL来选择正文会发生什么，你会得到什么？



这是一回事：



@ body = @HTML，

@body_format =''HTML''



您无法在其他参数中设置proc的参数。这就是你在这里做的吗？



你需要简化它直到它工作，然后告诉我们哪个位坏了，如果你仍然无法搞清楚。但是，我认为这就是问题。

I assume other mails are sending fine. However, you don't give us anywhere near enough info. What happens if you run the SQL to select the body, what do you get ?

Here's one thing:

@body= @HTML,
@body_format = ''HTML''

You can't set parameters for your proc inside other parameters. Is that what you're doing here ?

You need to simplify this until it works, then tell us which bit is broken, if you still can't figure it out. But, I think that's the issue.

这篇关于数据库邮件查询问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！