Creating a job to insert a record in a table about other records 5

[POSAPAY]

Hi,

I've created a job in Jobs on our SQL server, but not sure what I need to put in the Step's Command area.


I need to check TableA where EmailTo is same as Email in TableB and add a single record in TableC and TableD with a summary of the records found in TableA.

TableA has MsgID, EmailFrom, EmailTo, Subject, Date
TableB has Email
TableC has MsgID, Message
TableD has MsgID, EmailFrom, EmailTo, Subject, Date

For example, if TableB has two records: tom@smith.com and bill@smith.com
For each I'd like to scan TableA for last 24 hours of records, and take the EmailFrom, EmailTo, Subject, Date from TableA and put in a variable say Message as one blob of text.

Then take that Message and save it to TableC, and get the MsgID from TableC and save the MsgID, a hard-set EmailFrom, and EmailTo being same as TableB's Email, with a hard set Subject.

Basically a report in one record run once a day for each user's Email in TableB.

I'm just not sure how to compile that and where to put it.

 

[POSAPAY]

I got the Job configured to the point where it sends me an e-mail using a stored procedure.

Now I just need to figure out how I can get a Select statement to run and the output of it to be the message of the e-mail.

Here is what I have right now:

DECLARE @msg nvarchar(4000)
Set @msg = 'TEST'
--Execute it
EXEC sp_send_cdosysmail
'postmaster@domiantest.com',
'user1@domiantest.com', 
'This is an automated SQL test email', 
@msg

So I'd like to replace the word "TEST" in the MSG with the output of:
SELECT TOP 5 TL.Email, TQ.QuarID, TQ.EmailFrom, TQ.EmailTo, TQ.Subject, TQ.MsgDate, TQ.RejectDetails FROM tblLogins as TL, tblQuarantine as TQ WHERE TL.Email = TQ.EmailTo

Any ideas of how to do that?

 

[markros]

In this particular case I don't see a way without using a loop (cursor or while loop)

 

[POSAPAY]

Could you please assist me how I would create a While loop here, and how do I pass the data? Somehow building the data from a Select query is what got me stuck, but also I'm not sure how to create a while loop in SQL.

 

[markros]

declare @Email varchar(200), @QuarID int, etc.
declare @iLoop = 0
while @iLoop <5
set @iLoop = @iLoop + 1
select top 1 @Email = Tl.Email, @QuarID = TQ.QuarID, etc. 
from ... where @QuarID IS NULL OR TQ.QuarID > @QuarID
order by TQ.QuarID -- assuming QuarID in unique ID

execute ...

From the top of my head and not tested

 

[markros]

Sorry, I forgot to include begin/end in the while block - I meant to but was distracted by a call.

 

[JeffModen]

@POSAPAY

Which version of SQL Server are you using? I ask because we may be able to avoid the cursor.

--Jeff Moden
-------------------------------------------------------------------------------
"RBAR" is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row

 

[markros]

AFAIK it's SQL Server 2005 if I recall correctly.

Would you please post your suggestion regardless of the answer? I'm curious how can we avoid the loop to call an SP?

Do you mean using insert with EXECUTE?

 

[JeffModen]

No... not EXECUTE. With simple concatenation, instead. In SQL Server 2005, XML concatenation would certainly be faster but since we don't really know which version the OP is using, I'll post the version that also works on 2k.

Behind the scenes, the CDONTS code uses the TEXT datatype for the message (IIRC). Of course, we'd have to jump through a couple of hoops to go beyond the 8k VARCHAR limit in 2k but 5 rows of data should fit easily. Obviously, the following code is untested because I don't have the OP's tables, but it should be very close...

We'll also "spiff" up the output with a little HTML...

--===== Prep the variable to hold the email message
DECLARE @EmailBody VARCHAR(8000)
    
--===== Prepare the table
 SELECT @EmailBody = '<h2>Latest 5 Quarantined Emails</h2>'   --Caption (so to speak)
                   + '<table style="border:black 1px solid">' --Start the table with a thin border
                   + '<tr style="background-color:Red">'      --Start the header row and set background to red

--===== Build the cells in the header row using a hardcoded list of column names
 SELECT @EmailBody = @EmailBody
                   + '<td width="'+MyWidth+'px"><center><b>'+MyColName+'</b></center></td>'
   FROM (--==== List of column headers (the widths probably need tweaking)
         SELECT '100' AS MyWidth, 'Email'         AS MyColName UNION ALL
         SELECT  '80' AS MyWidth, 'QuarID'        AS MyColName UNION ALL
         SELECT '160' AS MyWidth, 'EmailFrom'     AS MyColName UNION ALL
         SELECT '160' AS MyWidth, 'EmailTo'       AS MyColName UNION ALL
         SELECT '100' AS MyWidth, 'Subject'       AS MyColName UNION ALL
         SELECT  '80' AS MyWidth, 'MsgDate'       AS MyColName UNION ALL
         SELECT '200' AS MyWidth, 'RejectDetails' AS MyColName
        ) colhdr

--===== Generate the data row of the table (again, the widths probably need tweaking)
 SELECT @EmailBody = @EmailBody
      + '<tr>' --Start a new row
      + '<td width="100px"><b>'+Email+'</b></td>'
      + '<td width="80px"><b>'+QuarID+'</b></td>'
      + '<td width="160px"><b>'+EmailFrom+'</b></td>'
      + '<td width="160px"><b>'+EmailTo+'</b></td>'
      + '<td width="100px"><b>'+Subject+'</b></td>'
      + '<td width="80px"><b>'+MsgDate+'</b></td>'
      + '<td width="200px"><b>'+RejectDetails+'</b></td>'
      + '</tr>' --End the row
   FROM (--==== Create the list of the top 5 latest quarantined emails
         SELECT TOP 5
                tl.Email, tq.QuarID, tq.EmailFrom, tq.EmailTo, tq.Subject, tq.MsgDate, tq.RejectDetails 
           FROM tblLogins AS tl
          INNER JOIN tblQuarantine AS tq ON tl.Email = tq.EmailTo
          ORDER BY t1.MsgDate DESC
        )data

--===== End the table
 SELECT @EmailBody = @EmailBody 
                   + '</table><br><br>'

--=====================================================================================================================
--      Create the footer of the message in HTML
--=====================================================================================================================
 SELECT @EmailBody = @EmailBody 
                   + '<a><b style=''color:Gray''>Automated Email From Technology Department</b></a><br />'

I believe that in order to get CDONTS to actually use HTML, you have to make a slight change to whatever stored procedure you call to actually send the mail. The line I use is...

   EXEC dbo.sp_OASetProperty @objEmailID, 'HTMLBody'   , @EmailBody

It will take both plain text and HTML.

--Jeff Moden
-------------------------------------------------------------------------------
"RBAR" is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row

 

[markros]

Thanks a lot - I'll review this later more thoroughly - looks like very interesting solution.

 

[JeffModen]

You bet... thanks for the feedback.

--Jeff Moden
-------------------------------------------------------------------------------
"RBAR" is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row

 

[JeffModen]

Ah... almost forgot... you can always export the data (sometimes, I'll use sp_MakeWebTask) and attach the file for the really big stuff. Of course, that has it's own set of challenges, as well.

--Jeff Moden
-------------------------------------------------------------------------------
"RBAR" is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row

 

[gmmastros]

(sometimes, I'll use sp_MakeWebTask)

http://msdn.microsoft.com/en-us/library/ms180099(SQL.90).aspx

[tt][blue]This feature will be removed in the next version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible.[/blue][/tt]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[JeffModen]

Yep... thanks George. I knew that and appologies for not mentioning the deprecation as one of the challenges (I really wish they'd stop taking away good tools).

I have a couple of folks that are locked into the older versions of SQL Server because of 3rd party software that is no longer supported by the 3rd party. That's about the only time I'll "sometimes" use sp_MakeWebTask.

Thanks for the reminder.

--Jeff Moden
-------------------------------------------------------------------------------
"RBAR" is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row

 

[POSAPAY]

Jeff / markros
Thanks for your suggestions, I started putting it in the system, had some issues, and been sidetracked since then.

I plan to use this on two different systems. I didn't installk SQL, but the server's product version shows:
Server 1: 8.00.194 (RTM)
Server 2: 8.00.2187 (SP4)

Any idea which is what version and these two would run your code?

 

[POSAPAY]

Jeff, BTW, I haven't seen the sp_MakeWebTask Is that something you wrote, or included with SQL? How does that work so that we can be future proofed?

 

[markros]

See

http://berezniker.com/content/pages/sql/microsoft-sql-server/sql-server-versions

So, you're using SQL Server 2000

 

[JeffModen]

POSAPAY (IS/IT--Management) 9 Oct 09 17:48
Jeff, BTW, I haven't seen the sp_MakeWebTask Is that something you wrote, or included with SQL? How does that work so that we can be future proofed?]/quote]

It's in Books Online under "sp_MakeWebTask" in both SQL Server 2000 and 2005. Basically, it will create web quality html files and has a huge amount of functionality. The recommended replacement is (ugh) Reporting Services.

There are conflicting reports as to whether it was still included in 2008 and Books Online says it shouldn't be there anymore. Could someone take a look in the Master database of 2008 and confirm one way or the other, please.

--Jeff Moden
-------------------------------------------------------------------------------
"RBAR" is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row

 

[markros]

I don't see it in there. SQL Server 2008 Express RTM

 

[JeffModen]

Cool... thanks for the confirmation. Damn... there goes another super useful tool.

--Jeff Moden
-------------------------------------------------------------------------------
"RBAR" is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row