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.

 

[Emtucifor]

>> EXEC dbo.sp_OASetProperty
I know I myself have blogged about using this to send email, but I thought I should point out that this has a memory leak and excessive use of it will eventually break, and the only fix is a SQL Server service restart...

 

[POSAPAY]

Jeff and markros,
Could that SP be copied over to 2008 and used? or when you don't see it, does that mean "forget it"?

 

[JeffModen]

Emtucifor (Programmer) 9 Oct 09 19:34
>> EXEC dbo.sp_OASetPropertyI know I myself have blogged about using this to send email, but I thought I should point out that this has a memory leak and excessive use of it will eventually break, and the only fix is a SQL Server service restart...

Are you sure it wasn't about sp_OAMethod and sp_OAGetProperty? Please see the following for why I ask.

http://support.microsoft.com/kb/937277

http://support.microsoft.com/kb/816937

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

 

[POSAPAY]

Jeff,

So I tried it, and with one typo and couple of minor adjustments I got it to pass Syntax check.
But When I tried running it I got an error message:

Server: Msg 245, Level 16, State 1, Procedure SPTekTipMail, Line 26
Syntax error converting the varchar value '</b></td>' to a column of data type int.

So basically I have your code with minor adjustment:

CREATE PROCEDURE SPTekTipMail 

--===== Prep the variable to hold the email message
@EmailBody VARCHAR(8000) OUTPUT
AS
    
--===== 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 tq.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 />'
GO

And in Query Analyzer I tried running it by having:

declare @EmailBody varchar(8000)
EXEC SPTekTipMail @EmailBody OUTPUT
PRINT @EmailBody

Obviously first I'd like to run this in a controlled way for my e-mail account only before I set it in place to run for all e-mail accounts and hooking it up to a Job that would run periodically.

 

[JeffModen]

Try changing the following line from...

      + '<td width="80px"><b>'+QuarID+'</b></td>'

... to ...

      + '<td width="80px"><b>'+CAST(QuarID AS VARCHAR(10))+'</b></td>'

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

 

[POSAPAY]

Perfect!

Now how can I have it send one e-mail to each user in table tblLogins?

Right now it just dumps the first 5.
I'd need it to only call one user's email from the tblLogins, and build this HTML e-mail, and send it.

So here is what I did sofar, but got stuck after this:

Built another stored procedure:

CREATE PROCEDURE SPQuarantoneReport AS
declare @EmailBody varchar(8000)
declare @iLOOP int
declare @LoginEmail varchar(100)
SET @iLOOP = 0
Set @LoginEmail = 'me@domain.com'
While @iLOOP < 5
  Begin
    set @iloop = @iloop + 1
    exec SPTekTipMail @LoginEmail, @EmailBody OUTPUT
  End
PRINT @EmailBody
GO

Yeah I realize this calls it 5 times and does basically nothing but rebuild the email body. But got stuck here.

So in QueryAnalyzer I only call this by "EXEC SPQuarantoneReport"

And I modified the SPTekTipMail procedure by adding the LoginEmail variable at the beginning, and in the data call: (BTW, instead of calling TOP 5, I'd like to call all records within the last 6 hours. Just not sure how to add that)

CREATE PROCEDURE SPTekTipMail 
(
 @LoginEmail varchar(100),
--===== Prep the variable to hold the email message
@EmailBody VARCHAR(8000) OUTPUT
)
AS

[blue]....[/blue]

         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
          WHERE tl.Email =  @LoginEmail
          ORDER BY tq.MsgDate DESC

[blue]....[/blue]

I just don't know how to go through in a loop to call the e-mail builder one user at a time.

After that, I'll need to call the e-mailing stored procedure.

 

[POSAPAY]

I just tried changing the TOP 5 to TOP 15, and it's trimming off?

I took my output and put it in MS Word to count the characters, and got 4000 characters even being the max.

I looked through my SPs, and everything declared it at 8000.
Since it prints it out without any other process, something about it being passed back gets it trimmed. Any ideas?

Any workaround on that?

 

[POSAPAY]

Jeff,

I just reread one of your previous comments:

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.

Could you expand on this?

I only did 5 records at first to test the idea. The table has close to a million records. For any user currently there are around a couple hundred to a couple thousand records entered daily.

So if I schedule this to run every 6 or 12 hours, the report e-mail would need to show 100 - 1000 records.

While this could become useless with so much data, I do want to limit it, truncate it at lets say 100? perhaps even less, 50?

And lastly add a record count to show in example:
There are currently a total of 250 spam mails in your quarantine.

I'm not sure how to get that count.
Obviously as a separate project I'll need to create another SP and job to delete records older then a set number of days, unless anyone has other suggestions to handle billions of records in mainly two tables.

 

[JeffModen]

What appears on the screen isn't necessarily what goes into the variables. For example, even with a VARCHAR(MAX), the most you'll ever see from SSMS is 8k characters unless you write a script to slice it up into 8k slices.

Check your settings... chances are you max displayable column width is set to 4000. If you want to check the length of your email, use the DATALENGTH function (just in case there are trailing spaces or some unicode involved) to check the length.

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

 

[POSAPAY]

Jeff,

I tried in QueryAnalyzer simply hard setting the msg sent to the CDO e-mailer for the string to be 5600 .. and the e-mail that arrived was chopped off after 4000 characters.

So while I have your email body builder, which just on its own is cut off for some reason too, I also have the CDO sp somewhere cutting if off at 4000 characters.

So I'm wondering if there are any other ways to generate the emails to send a quarantine report with a lot more records.

 

[JeffModen]

What does the CDO Sender look like?

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

 

[POSAPAY]

Here is what I have for sp_send_cdosysmail:

CREATE PROCEDURE [dbo].[sp_send_cdosysmail] 
(
 @From varchar(100), 
 @To varchar(100), 
 @Subject varchar(100)=" ",
 @Body varchar(8000) =" " 
)


/****************************************** 


This stored procedure takes the parameters and sends 
an e-mail. All the mail configurations are hard-coded 
in the stored procedure. Comments are added to the 
stored procedure where necessary. References to the 
CDOSYS objects are at the following MSDN Web site: 
[URL unfurl="true"]http://msdn.microsoft.com/library/default.asp[/URL]
?url=/library/en-us/cdosys/html/_cdosys_messaging.asp 


*******************************************/ 
AS 


Declare @iMsg int 
Declare @hr int 
Declare @source varchar(255) 
Declare @description varchar(500) 
Declare @output varchar(1000) 


--***** Create the CDO.Message Object ***** 


EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT 


--*****Configuring the Message Object ***** 


-- This is to configure a remote SMTP server.
-- [URL unfurl="true"]http://msdn.microsoft.com/library/default.asp[/URL]
--  ?url=/library/en-us/cdosys/html/_cdosys_schema_configuration_sendusing.asp
EXEC @hr = sp_OASetProperty @iMsg,'Configuration.fields ("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'[/URL] 


-- This is to configure the Server Name or IP address. 


-- Replace MailServerName by the name or IP of your SMTP Server.
EXEC @hr = sp_OASetProperty @iMsg,'Configuration.fields("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpserver").Value',[/URL]  'localhost' 


-- Save the configurations to the message object.
EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null


-- Set the e-mail parameters.
EXEC @hr = sp_OASetProperty @iMsg, 'To', @To
EXEC @hr = sp_OASetProperty @iMsg, 'From', @From
EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject


-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
--EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @Body
EXEC @hr = sp_OASetProperty @iMsg, 'HTMLBody', @Body
EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL


-- Sample error handling. 
IF @hr <> 0 
SELECT @hr
BEGIN
  EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
  IF @hr = 0
  BEGIN
    SELECT @output = '  Source: ' + @source
    PRINT  @output
    SELECT @output = '  Description: ' + @description
    PRINT  @output
  END
  ELSE
  BEGIN
    PRINT '  sp_OAGetErrorInfo failed.'
    RETURN
  END
END


-- Do some error handling after each step if you have to.
-- Clean up the objects created.
EXEC @hr = sp_OADestroy @iMsg
GO

 

[JeffModen]

Ok... the sender looks ok. Check and see if you're concatenating any NVARCHAR columns or data when you're building the body. That means checking the table your building it from, as well. Such an NVARCHAR concatenation would cause it to convert the whole concatenation to NVARCHAR, hence, the 4k limit you're seeing.

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

 

[POSAPAY]

Well between your code and this CDO code, I only run one SP:

CREATE PROCEDURE SPQuarantoneReport AS
declare @EmailBody varchar(8000)
DECLARE @msg varchar(8000)
declare @LoginEmail varchar(100)
declare @SubjectEmail varchar(100)
Set @LoginEmail = 'user1@domain.com'
Set @SubjectEmail = 'Quarantine Report from Spam Filter '+CAST(GETDATE() AS VARCHAR(40))
exec SPTekTipMail @LoginEmail, @EmailBody OUTPUT
PRINT @EmailBody
SET @msg = @EmailBody
EXEC sp_send_cdosysmail
'postmaster@domain.com',
@LoginEmail, 
@SubjectEmail, 
@msg

--NEXT EMAIL ACCOUNT
SET @LoginEmail = 'user2@domain.com'
exec SPTekTipMail @LoginEmail, @EmailBody OUTPUT
SET @msg = @EmailBody
EXEC sp_send_cdosysmail
'postmaster@domain.com',
@LoginEmail, 
@SubjectEmail, 
@msg

--NEXT EMAIL ACCOUNT
SET @LoginEmail = 'user3@domain.com'
exec SPTekTipMail @LoginEmail, @EmailBody OUTPUT
SET @msg = @EmailBody
EXEC sp_send_cdosysmail
'postmaster@domain.com',
@LoginEmail, 
@SubjectEmail, 
@msg
GO

 

[JeffModen]

Understood... but check out the table that the SPTekTipMail is building the email body from... does the TABLE have any NVARCHAR? (Also, do a search in SPTekTipMail to make sure I didn't screw up and include an NVARCHAR somewhere).

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

 

[POSAPAY]

Jeff,
Thanks, I checked on that and the reject details was nvarchar. with all the data in the database it is hard to change it. the system was just trying and finally I gave up and rebooted it.

So I took the reject reason out, and only left the variables that are varchar to begin with.
Also from the tblLogins the email was nvarchar, but that was easy to change.

So I tested it, and it sent an e-mail chopped off at 4000 chars again.

I ran it using QueryAnalyzer, and now it gave me 5582 chars there.
So I guess we have it narrowed down to the CDO components, as something there must be using nvarchar, or other reaosn forcing it down to 4000 chars.

So I've got these issues still outstanding to figure out:
1) 4000 char cut-off, or figure a workaround.
2) How to get a record count of total records for a user.
3) How to loop through all the tblLogins Email fields and calling the e-mailer for each email address in logins.

I've only done similar to this in VBScript so I'm clueless in SQL of how to loop through records and process the email sending automatically for each.

 

[JeffModen]

You don't need to change the unerlying columns in the tables.... just cast them in the code. ;-)

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

 

[POSAPAY]

I updated the code to cast all column data to varchar before adding to EMailBody.

If we needed to send an e-mail with more then 8000 characters, what would you suggest?


Is there such thing in SQL as in ADO VBScript like RecordObject.RecordCount to get the maximum number of records returned in a select statement? (Or an easier way to get the number of spam records for a user?)


And sorry to sound repetitious, in my final goal I'd like to call this SP for each record in tblLogins. How can that be formatter in an SP?

By the way, thanks so much for helping me out with this project, I think I'm finally starting to get SQL on the surface.

 

[JeffModen]

Sorry about not getting to your other questions... I was just trying to peel one potato at a time. Overcoming th length problem you were having seemed like the most important potato. ;-)

If we needed to send an e-mail with more then 8000 characters, what would you suggest?

If you're using SQL Server 2005, just change all the VARCHAR references to VARCHAR(MAX). Do be advised that that will have some impact on performance because of the "out of row" nature of VARCHAR(MAX).

If you're using SQL Server 2000, it becomes a bit more difficult because we'd have to use the TEXT datatype, which is also "out of row" and simple concatenation doesn't work with the TEXT datatype.

Is there such thing in SQL as in ADO VBScript like RecordObject.RecordCount to get the maximum number of records returned in a select statement? (Or an easier way to get the number of spam records for a user?)

Yes... immediately after any SQL that affects/returns rows, you can do something like the following to capture the number of rows affected...

 SELECT @somevariable = @@ROWCOUNT

And sorry to sound repetitious, in my final goal I'd like to call this SP for each record in tblLogins. How can that be formatter in an SP?

That really depends on the version of SQL you're using... all versions can, of course, make use of a RBAR control loop because of the singleton nature of the sp_OA send stuff. It can be accomplished either by using an explict Corsor or While loop (all versions) or we can use XML to very quickly concatenate the necessary commands into a single VARCHAR(MAX) and execute the content of that variable.

So... which version of SQL Server are you using?

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

 

[JeffModen]

Sorry about the fat fingering... "Corsor" should have been "Cursor".

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