Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.
POSAPAY

POSAPAY

IS-IT--Management
Joined
Jul 27, 2001
Messages
192
Location
HU
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.
 
Sort by date Sort by votes
Looks like I'm using SQL 2000.


So for the record count, would it be an additional call like this(?):
Code: 
SELECT tl.Email, tq.EmailTo, tq.Subject, tq.MsgDate, 
        @somevariable = @@ROWCOUNT
        FROM tblLogins AS tl
        INNER JOIN tblQuarantine AS tq ON tl.Email = tq.EmailTo
        WHERE tl.Email =  @LoginEmail
 
Upvote 0 Downvote
I tried the @somevariable = @@ROWCOUNT, and got an error message:

Error 170: Line 26: Incorrect syntax near '='


I declared the @somevariable ans an int. What am I missing?
 
Upvote 0 Downvote
Ok, so while my previous attempt didn't work, a bit of research and trial/error I got this to work. I'm just not sure if this is the most efficient way of doing it.

Code: 
--===== Prepare E-mail Header
 SELECT @EmailBody = '<html><head><style type="text/css">*{margin:0; padding:0;}'
         +'body{font:12px Verdana;'
         +'}</style></head><body><h3>10 Most Recent Quarantined Emails in the last 24 hours '   --Caption (so to speak)
         +' [ <a href="[URL unfurl="true"]http://domain.com/Spam/">View[/URL] Quarantine</a> ]</h3><BR><b>Quarantine report for: '+@LoginEmail+'</b>'

--===== Add Total Spam Record Count to header
 SELECT @EmailBody = @EmailBody + '   Total number  of spam e-mails quarantined: '

--==== get total records of quarantined emails
         SELECT 
                tq.QuarID
           FROM tblLogins AS tl
          INNER JOIN tblQuarantine AS tq ON tl.Email = tq.EmailTo
          WHERE tl.Email =  @LoginEmail

SELECT @EmailBody = @EmailBody + '[ ' + CAST(@@ROWCOUNT AS VARCHAR(10)) + ' ]'
PRINT @@ROWCOUNT
 
Upvote 0 Downvote
BTW, wow, I guess I get the famous Jeff Moden's help here.. as I looked up RBAR, I got this in one article:

RBAR, is an acronym for 'Row By Agonising Row'. It was first coined by Jeff Moden, a very regular poster on SqlserverCentral.com.
Click to expand...
 
Upvote 0 Downvote
The code for the rowcount should look like the following...
Code: 
--===== Declare a variable somewhere near the beginning of the proc
DECLARE @MyRowCount INT
.
.
.
--===== Do what ever this is supposed to do...
 SELECT tl.Email, tq.EmailTo, tq.Subject, tq.MsgDate 
   FROM tblLogins AS tl
  INNER JOIN tblQuarantine AS tq ON tl.Email = tq.EmailTo
  WHERE tl.Email = @LoginEmail

--===== This MUST be the next line to capture the row count
     -- in a variable.
 SELECT @MyRowCount = @@ROWCOUNT

--Jeff Moden
-------------------------------------------------------------------------------
"RBAR" is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row
 
Upvote 0 Downvote
Actually, I need to scroll down before I reply... the last bit of code you posted looks just fine for the rowcount thing.

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

Awsome! Thanks!

Now I'm just left as a major challenge with the flipping through a table of logins and running the quarantine report one at a time for each Email address in the tblLogins table.

To which last you're replied with:
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?
Click to expand...

So with SQL 2000, what would you recommend that could possibly work in SQL2005 and 2008? (Would be great to not need to rewrite it in the future.)
 
Upvote 0 Downvote
Then this is one of the few places (unavoidable RBAR for process contol) where a Cursor or While Loop would be the way to go. Heh... I say "unavoidable" only because of the 8k limits in SQL Server 2000 and the fact that as soon as I say such a thing, someone will find an ingenious way to get around that limitation in a set based fashion. ;-)

--Jeff Moden
-------------------------------------------------------------------------------
"RBAR" is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row
 
Upvote 0 Downvote
While I'm digging through old books and online, I'm still stuck on figuring out how to do this.

How could this be stated in SQL:

While not EndOfFile from a record set....
- Set @LoginEmail - recordset's current record's Email
- EXEC SP_CreateEmail
- EXEC SP_SendEmail
Repeat with next record

 
Upvote 0 Downvote
Here is a simulation

declare @Id int

select top 1 @Id = ID from myTable order by ID DESC

while @ID is not null
begin
select top 1 @ID = ID from myTable where ID <@ID order by ID Desc
do some stuff
end

this is from the top of my head, just to give you an idea. You can also take a look at DECLARE CURSOR command in BOL

 
Upvote 0 Downvote
markros,

Thanks for the tip, I'm getting closer to my goal, but with that it seems I got an infinite loop. Got the processor maxed out.

Funny how I try that on a production box.... ooops.

This is what I tried:
Code: 
declare @Id int
select top 1 @Id = LoginID from tblLogins order by LoginID DESC

while @ID is not null
  begin
  select top 1 @ID = LoginID from tblLogins where LoginID <@ID order by LoginID Desc
  Print 'Now is: ' + CAST(@ID AS VARCHAR(10))
end
 
Upvote 0 Downvote
Okay, my mistake, after testing and stooping it, I realized it kept counting down into negative values.

So I've got that solved...
Code: 
declare @Id int
select top 1 @Id = LoginID from tblLogins order by LoginID DESC

while @ID is not null
  begin
  select top 1 @ID = LoginID from tblLogins where LoginID < @ID order by LoginID Desc
  Print 'Now is: ' + CAST(@ID AS VARCHAR(10))
  IF @ID <= 1
   BREAK
end


I'll work on combining previous steps, and let you guys know if any issues come up. (BTW, I'm still thinking how to get around the 8K issue.. possibly using text variables. Any thoughts?)
 
Upvote 0 Downvote
If you use the TEXT datatype, it'll work in 2k and 2k5... but it was deprecated in 2k5 and is no longer available in 2k8. The same goes for sp_makeWebTask. I haven't checked, but I'm not sure that CDONTS is available in 2k8, either.

The bottom line is, I'm not sure what would work on all 3 with no changes between the versions.

--Jeff Moden
-------------------------------------------------------------------------------
"RBAR" is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row
 
Upvote 0 Downvote
Jeff,
I see... What is meant to be used in SQL2K8?


And also got a second quick question.
I've figured out how to get the row count, but it returns also data with it. I don't need the data returned, just the count.
So I kept messing with it until I came up with these two options... one with ROWCOUNT and the other using COUNT.
I'm just not sure what is more efficient and better for future SQL versions:

Code: 
--===== Add Total Spam Record Count to header
 SELECT @EmailBody = @EmailBody + '   Total number  of spam e-mails quarantined: '

--==== get total records of quarantined emails
          SELECT QuarID
           FROM tblQuarantine
          WHERE EmailTo =  @LoginEmail

SELECT @EmailBody = @EmailBody + '[ ' + CAST(@@ROWCOUNT AS VARCHAR(10)) + ' ]'

Or using COUNT:
Code: 
        SELECT @ROWCOUNTINT = COUNT(QuarID)
        FROM tblQuarantine
          WHERE EmailTo =  @LoginEmail

SELECT @EmailBody = @EmailBody + 'OR [ ' + CAST(@ROWCOUNTINT AS VARCHAR(10)) + ' ]'
 
Upvote 0 Downvote
>> Are you sure it wasn't about sp_OAMethod and sp_OAGetProperty?

No, I was wrong. The problem is with any output parameter and the OA SPs. If you do all your email sending without getting an output parameter, then there is no memory leak. Sorry about that.

Note that:
1. All 3 SPs use the same library behind them
2. You can actually use sp_OAMethod and sp_OAGetProperty interchangeably (and perhaps sp_OASetProperty, too)
3. Microsoft says "this problem occurs if the stored procedure returns a property value in the OUTPUT parameter" so it seems the problem is with returning a value, not really with a particular one of the stored procedure, except that SetProperty isn't normally used for returning a value. I'm not sure if it can, but if it can, it will have the memory leak.

I also didn't know that this problem has been fixed in SQL 2005 in "Cumulative update package 2 for SQL Server 2005 SP2" or later.

The fix is to the Odsole70.dll file. The first fixed version of the binary is 2005.90.2232.0.
 
Upvote 0 Downvote
Anyone,

Any thoughts on my last post of @@ROWCOUNT vs. COUNT() ?

@@ROWCOUNT returns some data... any way to not have it return any data and still get the count with @@ROWCOUNT?

 
Upvote 0 Downvote
markros,

Thanks!

I've also discussed upgrade issues with our hosting company, Rackspace. They recommended for me to look at the upgrade advisor:

You can find instructions on using Upgrade Advisor at:

[URL unfurl="true"]http://msdn.microsoft.com/en-us/library/ms144256.aspx [/url]

SQL 2005 Upgrade Advisor download:


SQL 2008 Upgrade Advisor download:
 
Upvote 0 Downvote
All,

Just going back to the original part of this thread about using a job to insert a record, I still got two questions:

1) is @@ROWCOUNT or COUNT() a better option?
where with @@ROWCOUNT I'm puzzled by how to not have it return data for the job, just the rowcount.

2) If I were to log the sent out e-mails in a table, any tips on best practice to insert the EmailBody?
Is it like:
INSERT tblEmailsSent (EmailFrom, EmailTo, Subject, EmailBody) VALUES ('postmaster@domain.com',@LoginEmail, 'Quarantine Message', @EmailBody) ?

I'm just not a 100% sure if I got it all right, could someone confirm these two last components for me?
 
Upvote 0 Downvote
Which application you're using to execute this?

BTW, perhaps it's time to start a new thread.

Your second statement looks Ok to me.

If you only need to return the count, then

declare @RecCount int -- or return it through Output parameter

select @RecCount = count(*) from (select my Complex select) X
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

SashaBuilder3
  • Locked
  • Question Question
select a single row based on conditions in multiple rows
Replies
3
Views
1K
SashaBuilder3
SashaBuilder3
WomanPro
  • Locked
  • Question Question
Insert into query adds records to unreadable format, how do I change that?
Replies
5
Views
813
Chris Miller
Chris Miller
Steve Yu
  • Locked
  • Question Question
How secure is MS SQL on the Internet vs behind firewall inside a LAN
Replies
3
Views
891
Chris Miller
Chris Miller
BobThornton
  • Locked
  • Question Question
Drop a table based on the count of records in another table
Replies
13
Views
1K
Chris Miller
Chris Miller
Cpreston
  • Locked
  • Question Question
How to add a Letter to datepart
Replies
5
Views
1K
Andrzejek
Andrzejek

Part and Inventory Search

Sponsor

Back
Top