?
Solved

Create SQL Trigger and the Email HTML table

Posted on 2013-12-20
1
Medium Priority
?
377 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 2000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
HTML5 has deprecated a few of the older ways of showing media as well as offering up a new way to create games and animations. Audio, video, and canvas are just a few of the adjustments made between XHTML and HTML5. As we learned in our last micr…
Suggested Courses

743 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