Solved

Create SQL Trigger and the Email HTML table

Posted on 2013-12-20
1
361 Views
Last Modified: 2013-12-20
I am trying the email a HTML table after an event is triggered and I can't seem to get the @query section working.  This works if I do not try to send the query results.


USE [database]
GO
/****** Object:  Trigger [dbo].[INSERT_HISTORY_trigger]  ******/

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[INSERT_HISTORY_trigger]
ON  [dbo].[HISTORY]
AFTER INSERT
AS

declare @PartID varchar(50)
SET @PartID = (SELECT PRIMARY_KEY FROM INSERTED)


SET NOCOUNT ON

IF NOT EXISTS(SELECT 1
FROM INSERTED I, [db].[dbo].[VE_VALIDATE] PCV
WHERE(I.TBL_NAME = 'PART') AND (I.COL_NAME = 'PRODUCT_CODE') AND (I.ACTION = 'UPDATE')AND(I.PRIMARY_KEY = PCV.[Part ID]) AND (I.CREATE_DATE = PCV.[Part Maint Change Date]))

 RETURN

declare @query nvarchar (max)
set @query =
      
N'<table>' +
N'<caption>Product Code change</caption>'+
N'<tr><th>PM changed By</th><th>PM Old Product Code</th><th>PM New Product Code</th><th>Order Type</th><th>Order ID</th><th>Line or Lot</th><th>Status</th><th>Part ID</th><th>Part Desc</th><th>Current Product Code on Order</th></tr>'+

cast(
(SELECT
        td = V.[Part Maint Changed By],'',
      td = V.[Old Product Code],'',
      td = V.[New Product Code],'',
      td = V.[Document],'',
      td = V.[ID],'',
      td = V.[Line/Lot],'',
      td = V.[STATUS],'',
      td = V.[Part ID],'',
      td = V.[Part Description],'',
      td = V.[Order Qty],'',
      td = V.[Complete Qty],'',
      td = V.[Current Product Code],''
      FROM [db].[dbo].[VE_VALIDATE] V, INSERTED TR
      WHERE V.[Part ID] = TR.PRIMARY_KEY and V.[Part Maint Change Date] = TR.CREATE_DATE
      ORDER BY [ID] DESC
      for xml path ('tr'), type) as nvarchar(max)) +
      N'</table>'

SELECT @QUERY


DECLARE @subss VARCHAR(1024)
DECLARE @bodys VARCHAR(1024)

SET @subss = @PartID+' Product Code Change Notification'
SET @bodys = '*** THIS WILL AFFECT GL ACCOUNTS ***'

    BEGIN
        EXEC msdb.dbo.sp_send_dbmail
          @profile_name = 'SQLAgentProfile',
          @recipients = 'myname@mydomain.com',
          @importance ='High',
          @subject = @subss ,
          @execute_query_database = db,

              @body = @bodys,

-- I think something is messing up here

              @query = 'SET NOCOUNT ON; SELECT @QUERY',
              --@query = 'SELECT @QUERY',
              --@query_no_truncate = 1,
          @BODY_FORMAT = 'HTML'


    END
0
Comment
Question by:TPBasintek
1 Comment
 
LVL 25

Accepted Solution

by:
jogos earned 500 total points
ID: 39732451
@query = 'SET NOCOUNT ON; SELECT @QUERY';

This is exacly what is passed as the @query parameter

You  don't want to pass the value ' @QUERY' but the content of that variable so

declare @queryCmd nvarchar (max)
set @queryCmd = 'SET NOCOUNT ON; SELECT ' + @QUERY;

Open in new window


and as parameter in  your exec
@query = @queryCmd,

Open in new window

0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This article discusses four methods for overlaying images in a container on a web page
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now