Solved

Create SQL Trigger and the Email HTML table

Posted on 2013-12-20
1
374 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Finding original email is quite difficult due to their duplicates. From this article, you will come to know why multiple duplicates of same emails appear and how to delete duplicate emails from Outlook securely and instantly while vital emails remai…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…

705 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