[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

AND/OR problem

Posted on 2007-07-24
138
Medium Priority
?
358 Views
Last Modified: 2008-03-06
i have a little AND/OR problem.  see, we do electronic trades.  i have an alert running out there that will send us email notifications if/when suspect data is found.  bad symbols, bad prices, unknown id's, etc.
if/when suspect orders are found, they are written to a discrepancy table with a status of 'U' for unresolved.  the email alert informs the enduser, who goes in via web interface to correct the orders.  upon doing so, the status of the orders is changed to 'R' for resolved

this checker runs all day long throughout the trade day every 2 mintues
if a suspect order is found, it should be written to this table and the alert/email should be sent out.
if the problem is not corrected, and the order status is still 'U' (unresolved) the alert/email should continue to be sent, thus continually alerting the enduser until the order has been corrected

i thought i had it gotten that done with the NOT EXISTS checker way down there on status:

CREATE proc dbo.PROCNAME
 @output bit = NULL output
AS

SET NOCOUNT ON
SET @OUTPUT = 0

--USERID/ACCTID CHECKER
create table #userids (userid varchar(20),acctid varchar(20),unique clustered (userid,acctid))
insert #users select distinct userid,acctid
from server.database.dbo.useridTable
where active = 'y'

DECLARE @start DATETIME, @end DATETIME
SELECT @start = DATEADD(minute, -10, GETDATE()), @end = DATEADD(minute, -1, GETDATE())

--USERID/ACCTID CHECKER
IF EXISTS (
SELECT 1 FROM database.dbo.orderTable t WITH (NOLOCK)
WHERE timeofexecution BETWEEN @start AND @end
AND NOT EXISTS (
SELECT 1 FROM #userids WHERE userid = t.userid
AND acctid = t.acctid
) )
BEGIN  
  SET @output = 1
  PRINT 'Suspect Trades have been identified:  userid/acctid
      http://blah/blah.php'

  INSERT INTO database.dbo.discrepancyTable (.........bunch of fields........)
  SELECT .........same bunch of fields........
  FROM database.dbo.orderTable t WITH (NOLOCK)
  WHERE tradetime BETWEEN @start AND @end
  AND NOT EXISTS (
  SELECT 1 FROM #userids WHERE userid = t.userid
  AND acctid = t.acctid )

  AND NOT EXISTS (
  SELECT 1 FROM database.dbo.discrepancyTable d WHERE datereported >= left(getdate()-0, 11)
  AND d.status ='R'
  AND d.ordernumber = t.ordernumber )
  OPTION (MAXDOP 1)
END

but... subsequent email/alerts aren't being sent.  i believe i just now realized that's because it's looking for suspect orders AND d.status <> 'R'
i got it to work in query analyzer by changing it to OR (see below), but my runtime went from 2 seconds to 24.  see, i've got a test record in there, status = U, my OR NOT EXISTS is finding it, but the runtime is far too excessive

can anybody suggest a different means of doing this, or possibly let me know where in my tsql the flaw is, such that i can speed it back up ?
please?
...
.....
OR NOT EXISTS (
  SELECT 1 FROM database.dbo.discrepancyTable d WHERE datereported >= left(getdate()-0, 11)
  AND d.status ='R'
  AND d.ordernumber = t.ordernumber )
  OPTION (MAXDOP 1)
END
0
Comment
Question by:dbaSQL
  • 75
  • 62
138 Comments
 
LVL 14

Expert Comment

by:twoboats
ID: 19558458
do an outer join to discrepancyTable
and

where  d.status <> 'R' or  d.status is null

This gets away from the correlated sub-query, and may be faster
0
 
LVL 17

Expert Comment

by:Daniel Reynolds
ID: 19558465
Note the addition of some parens. This allows for the first part of the condition(tradetime between blah) to always be used.

WHERE tradetime BETWEEN @start AND @end
  AND ((NOT EXISTS (
  SELECT 1 FROM #userids WHERE userid = t.userid
  AND acctid = t.acctid ))

  OR ( NOT EXISTS (
  SELECT 1 FROM database.dbo.discrepancyTable d WHERE datereported >= left(getdate()-0, 11)
  AND d.status ='R'
  AND d.ordernumber = t.ordernumber ))
  OPTION (MAXDOP 1)
END

If that doesn't work, just break it into two inserts with the conditions in a separate insert each.

0
 
LVL 17

Author Comment

by:dbaSQL
ID: 19558527
twoboats, can you show me the syntax?
xDJR, i'm trying now
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 17

Author Comment

by:dbaSQL
ID: 19558564
xDJR, sytnactically it's nto happening
0
 
LVL 14

Expert Comment

by:twoboats
ID: 19558729
something like

SELECT      some fields
FROM      database.dbo.orderTable t,
      #userids u,
      database.dbo.discrepancyTable d
WHERE       t.userid *= u.userid
and      t.acctid *= u.acctid
and      t.ordernumber *= d.ordernumber
and     u.userid is null
and      (d.status ='R' or d.status is null)
and      t.tradetime BETWEEN @start AND @end
and      d.datereported >= left(getdate()-0, 11)
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 19558894
twoboats, it compiles fine, but when i attempt to run it i get one of these on every one of the fields i'm selecting

Ambiguous column name 'OrderNumber'.
Server: Msg 209, Level 16, State 1, Line 23
0
 
LVL 14

Expert Comment

by:twoboats
ID: 19558948
you need to preface the selected field names with t.

eg

select t.col1, t.col2
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 19558950
xDJR, i got around the syntax issues w/this:

WHERE tradetime BETWEEN @start AND @end
  AND (NOT EXISTS (
  SELECT 1 FROM #userids WHERE userid = t.userid
  AND acctid = t.acctid ))

  OR ( NOT EXISTS (    <<<<<extra paren to the left of NOT
  SELECT 1 FROM database.dbo.discrepancyTable d WHERE datereported >= left(getdate()-0, 11)
  AND d.status ='R'
  AND d.ordernumber = t.ordernumber ))
  OPTION (MAXDOP 1)
END

but, i killed it at 1:01  ( a minute, one sec), it's bringing me back thousands of records that aren't even in today's dataset
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 19558954
eeewgh... that's what i thought you'd say, twoboats.  hang on, back shortly
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 19559016
nope, twoboats.  something isnt' right
i've got one test order in the discrpepancy table with status = U
at 1st run, your suggesting gave me back 6762 records, status = U
at 2nd run, 7285 orders with status = U

and each time i run it, the dataset increases (clearly from the incoming orders, i would think)
again, there is only 1 record in the discrepancy table with status = U
i'm running this:

SELECT bunch of fields  FROM database.dbo.orderTable t,
      #userids u,
      database.dbo.discrepancyTable d
WHERE t.tradetime BETWEEN @start AND @end
AND t.userid *= u.userid
AND t.acctid *= u.acctid
AND t.ordernumber *= d.ordernumber
AND u.userid IS NULL
AND (d.status = 'R' or d.status IS NULL)
AND d.datereported >= left(getdate()-0,11)
0
 
LVL 14

Expert Comment

by:twoboats
ID: 19559028
Okay. Let's see.

Hang on
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 19559034
I changed them to =, instead of *=, it comes back with nothing
maybe silly needless, but i just wanted you to know
0
 
LVL 14

Expert Comment

by:twoboats
ID: 19559073
Mind is bending a bit, because need to reverse the logic

SELECT bunch of fields  FROM database.dbo.orderTable t,
      #userids u,
      database.dbo.discrepancyTable d
WHERE t.tradetime BETWEEN @start AND @end
AND t.userid *= u.userid
AND t.acctid *= u.acctid
AND t.ordernumber *= d.ordernumber
AND u.userid IS NULL
AND d.status <> 'R'
AND d.datereported < left(getdate()-0,11)
and d.ordernumber is null
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 19559128
the darndest thing.... first, did you mean < left(getdate()-0,11), cuz i only care about the current day
second, what actually is  *=   ?
third, and i kid you not, the dataset now is decreasing.
1st run 7223
2nd, 7208
3rd, 7046

i have double checked...there is only one record in the discrepancy table with status <> 'R'
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 19559146
having changed it to >= left(getdate(),11), the dataset is now increasing (not decreasing)
each invokation, the resultset is greater
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 19559280
i have to step away jsut for a bit, twoboats.  but i'll be back shortly.  do you have any suggestions?
0
 
LVL 14

Expert Comment

by:twoboats
ID: 19559316
*=

is shorthand syntax for outer join

t.userid *= u.userid

means give me all the values of userid in t regardless of whether there is the same value in userid in u

It puts in nulls for the missing u.userids - and it's these were interesetd in, because where the u.userid is null, is where a u.userid "not exists"




0
 
LVL 14

Expert Comment

by:twoboats
ID: 19559319
Will have to come back to this later.
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 19559329
please do, twoboats, if you are able.  i really do need to get this into place as soon as i can
all of your assistance is very much appreciated
0
 
LVL 14

Expert Comment

by:twoboats
ID: 19560206
try this

SELECT bunch of fields  FROM database.dbo.orderTable t,
      #userids u,
      database.dbo.discrepancyTable d
WHERE t.tradetime BETWEEN @start AND @end

-- not exists in #userid
AND t.userid *= u.userid
AND t.acctid *= u.acctid
AND u.userid IS NULL
AND u.acctid IS NULL

-- not exists discrepancy
AND t.ordernumber *= d.ordernumber
AND d.ordernumber is null
AND d.datereported >= left(getdate()-0,11)
AND d.status = 'R'

0
 
LVL 17

Author Comment

by:dbaSQL
ID: 19564515
no, twoboats.  to be honest, i really don't understand your approach.  right now, this runs and gives me back 1184 records, then 1183, then 1180... then 1223... it's a varying dataset.  there is ony one record in the discrepancy table with status = U

this is becoming hugely pressing
this alert checker is needed realtime production hours at all times, and it really isn't functional right now at all.  i will send only one alert on issues, as opposed to subsequent alerts at every job execution, until the item is resolved (R).
and i am writing dupe records to the discrepancy table.  for example, one record is found to be suspect... maybe it's got a bad symbol... it is written to discrepancy table initially, an email alert is sent, and then maybe it isn't resolved quickly, it is written to the discrepancy table again.  oddly enough, the email alert isn't sent again and again, the record is only written to the discrepancy table again and again, until resolution.

please advise
this is very, very pressing
0
 
LVL 14

Expert Comment

by:twoboats
ID: 19564671
How many records do you get if you run the original select?

SELECT .........same bunch of fields........
  FROM database.dbo.orderTable t WITH (NOLOCK)
  WHERE tradetime BETWEEN @start AND @end
  AND NOT EXISTS (
  SELECT 1 FROM #userids WHERE userid = t.userid
  AND acctid = t.acctid )

  AND NOT EXISTS (
  SELECT 1 FROM database.dbo.discrepancyTable d WHERE datereported >= left(getdate()-0, 11)
  AND d.status ='R'
  AND d.ordernumber = t.ordernumber )
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 19564694
one
0
 
LVL 14

Expert Comment

by:twoboats
ID: 19564713
Is it doing a select distinct by any chance?
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 19564717
but... this is kind of two fold now
i have created a discrepancy in the orders table
that is the one record returned by this select
there is an Unresolved record in the disrepancy table.  my alert proces needs to send notification on both.

if/when a discrepancy is found, it is written to the discrepancy table and an email/alert is sent
if/when the discrepancy is not resolved, subsequent checks should continue to send the email until the record status is changed to R, for resolved

so, we found one record, but we should have found two
the discrepancy i have just created in the orders table, and the Unresolved record in the discrepancy table, with status of U
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 19564724
no, no distinct
0
 
LVL 14

Expert Comment

by:twoboats
ID: 19564781
We'll get there - it just takes time when I can't test it for you

SELECT      bunch of fields  
FROM database.dbo.orderTable t,
      #userids u,
      database.dbo.discrepancyTable d
WHERE       t.tradetime BETWEEN @start AND @end
AND       t.userid *= u.userid
AND       t.acctid *= u.acctid
AND       t.ordernumber *= d.ordernumber
AND      d.datereported >= left(getdate()-0,11)
AND      d.status = 'R'

AND       (-- not exists in #userid
      u.userid IS NULL
      AND u.acctid IS NULL)
OR
      (-- not exists discrepancy
      d.ordernumber is null)

0
 
LVL 17

Author Comment

by:dbaSQL
ID: 19564812
i cancelled it at 1:03, to get back 500018 records, varying trade dates, none of which i am looking for
there is one discrepancy in the order table right now
and one record in the discrepancy table with status = U
0
 
LVL 14

Expert Comment

by:twoboats
ID: 19564818
what values are you using for @start & @end?

0
 
LVL 17

Author Comment

by:dbaSQL
ID: 19564834
SELECT @start = DATEADD(minute, -10, GETDATE()), @end = DATEADD(minute, -1, GETDATE())

and yes, i continually update the tradetime on the records i am trying to find so that they are within the current 10 minute window
0
 
LVL 14

Expert Comment

by:twoboats
ID: 19564855
Okay. And there are rows in #userids...
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 19564864
yes, of course
875 of them are written to #userids each time i try this
0
 
LVL 14

Expert Comment

by:twoboats
ID: 19564881
Okay.

what's the number that this returns?

SELECT      count(*)
from      database.dbo.discrepancyTable d
WHERE       d.datereported >= left(getdate()-0,11)
AND      d.status = 'R'
0
 
LVL 14

Expert Comment

by:twoboats
ID: 19564910
Then what do we get as the numbers for these 2 queries...

DECLARE @start DATETIME, @end DATETIME
SELECT @start = DATEADD(minute, -10, GETDATE()), @end = DATEADD(minute, -1, GETDATE())
SELECT count(*)
  FROM database.dbo.orderTable t WITH (NOLOCK)
  WHERE tradetime BETWEEN @start AND @end
  AND NOT EXISTS (
  SELECT 1 FROM useridTable WHERE userid = t.userid
  AND acctid = t.acctid )



--and


DECLARE @start DATETIME, @end DATETIME
SELECT @start = DATEADD(minute, -10, GETDATE()), @end = DATEADD(minute, -1, GETDATE())
SELECT count(*)
  FROM database.dbo.orderTable t WITH (NOLOCK)
  WHERE tradetime BETWEEN @start AND @end
  AND NOT EXISTS (
  SELECT 1 FROM database.dbo.discrepancyTable d WHERE datereported >= left(getdate()-0, 11)
  AND d.status ='R'
  AND d.ordernumber = t.ordernumber )
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 19564979
1st query, 0
2nd query, 2867, then 2865, then 2872, then 2777.... clearly inaccurate

-- 68 records
select * from discrepancyTable where datereported >= left(getdate(),11) and status = 'r'
-- 0 records
select * from ordersTable a with (nolock) where tradetime >= left(getdate(),11) and exists(
select 1 from discrepancyTable b where datereported >= left(getdate(),11) and status = 'r'
and a.ordernumber = b.ordernumber)

-- 2 records (there is only ONE legitimate discrepancy, my logic is writing dupes to the discrepancy table, but, as i said, this is pretty odd as it only sends the alert email the first time)
--select * from discrepancyTable where datereported >= left(getdate(),11) and status = 'u'


0
 
LVL 17

Author Comment

by:dbaSQL
ID: 19565010
i am sure if we just find the flaw here (your 2nd query), we should be able to move forward
i think it's obvious that we are going into the order table for anything within the current 10 minute window, where the ordernumber does not exist in the discrepancy table on resolved items
that's why the dataset is ever-increasing (lord knows why it gets smaller and bigger, too, though)
we have to restrict this dataset before looking to the discrepancy table, don't we?

DECLARE @start DATETIME, @end DATETIME
SELECT @start = DATEADD(minute, -10, GETDATE()), @end = DATEADD(minute, -1, GETDATE())
SELECT count(*)
  FROM database.dbo.orderTable t WITH (NOLOCK)
  WHERE tradetime BETWEEN @start AND @end
  AND NOT EXISTS (
  SELECT 1 FROM database.dbo.discrepancyTable d WHERE datereported >= left(getdate()-0, 11)
  AND d.status ='R'
  AND d.ordernumber = t.ordernumber )
0
 
LVL 14

Expert Comment

by:twoboats
ID: 19565052
So you want to know which entries in the order table, whose date is between @start & @end
and which either

don't have a a corresponding user account
(which is the count of the first query - 0)

or

don't have an entry in discrepancies table that has
same order number
status 'R'
datereported >= left(getdate()-0, 11)
(which is the count of the second query - changing)

Hmmm. Had a thought. Can you try the 2 count queries again, but without the NOLOCK hints.

0
 
LVL 14

Expert Comment

by:twoboats
ID: 19565066
The second not exists query is your's from above, just modified to gaive a count to see what's going on.
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 19565148
no, both of mine from above include the reference to #userids:
--THIS ONE
--USERID/ACCTID CHECKER
IF EXISTS (
SELECT 1 FROM database.dbo.orderTable t WITH (NOLOCK)
WHERE timeofexecution BETWEEN @start AND @end
AND NOT EXISTS (
SELECT 1 FROM #userids WHERE userid = t.userid
AND acctid = t.acctid
) )
BEGIN  
  SET @output = 1

--AND THIS ONE
  INSERT INTO database.dbo.discrepancyTable (.........bunch of fields........)
  SELECT .........same bunch of fields........
  FROM database.dbo.orderTable t WITH (NOLOCK)
  WHERE tradetime BETWEEN @start AND @end
  AND NOT EXISTS (
  SELECT 1 FROM #userids WHERE userid = t.userid
  AND acctid = t.acctid )

without the NOLOCK, results are the same --- 2886, then 2892, etc.

>>So you want to know which entries in the order table, whose date is between @start & @end
and which either

don't have a a corresponding user account
(which is the count of the first query - 0)

or

don't have an entry in discrepancies table that has
same order number
status 'R'
datereported >= left(getdate()-0, 11)
(which is the count of the second query - changing)
>>

i wouldn't really say it like that
--  i want to find what records exist in the order table during the current @tart/@stop with unknown/invalid userid/acctid
--  these records are inserted into the discrepancy table, an email/alert is sent out
--  At next execution, I want to know again, if any new records exist in the order table during the current @tart/@stop with unknown/invalid userid/acctid, if so, they are acted on accordingly
-- I also want to know, however, if any record(s) exist in the discrepancy table that haven't yet been resolved -- status = U
(if the record is in discrepancy with status = U, that means it is still in the order table unresolved)

0
 
LVL 14

Expert Comment

by:twoboats
ID: 19565191
"no, both of mine from above include the reference to #userids:"

From the post at the very top - the select statement in ()s for the second not exists, doesn't contain a reference to #userids - should it?

INSERT INTO database.dbo.discrepancyTable (.........bunch of fields........)
  SELECT .........same bunch of fields........
  FROM database.dbo.orderTable t WITH (NOLOCK)
  WHERE tradetime BETWEEN @start AND @end
  AND NOT EXISTS (
  SELECT 1 FROM #userids WHERE userid = t.userid
  AND acctid = t.acctid )

  AND NOT EXISTS (
  SELECT 1 FROM database.dbo.discrepancyTable d WHERE datereported >= left(getdate()-0, 11)
  AND d.status ='R'
  AND d.ordernumber = t.ordernumber )


As I understand it, you changed this to


INSERT INTO database.dbo.discrepancyTable (.........bunch of fields........)
  SELECT .........same bunch of fields........
  FROM database.dbo.orderTable t WITH (NOLOCK)
  WHERE tradetime BETWEEN @start AND @end
  AND NOT EXISTS (
  SELECT 1 FROM #userids WHERE userid = t.userid
  AND acctid = t.acctid )

  OR NOT EXISTS (
  SELECT 1 FROM database.dbo.discrepancyTable d WHERE datereported >= left(getdate()-0, 11)
  AND d.status ='R'
  AND d.ordernumber = t.ordernumber )

And it was too slow. So we're trying to get an alternative to that.

0
 
LVL 17

Author Comment

by:dbaSQL
ID: 19565214
when I said both of mine, i was referring to the 1st one, and then the insert/select/from...not the bottom subquery

regardless, yes, we are trying to get an alternative to my attempt with the OR NOT EXISTS, which was far too slow.  you are correct, that is the objective
0
 
LVL 14

Expert Comment

by:twoboats
ID: 19565271
Okay, so...the following (which counts how many orders there are that don't have a user account) produces 0 - i.e. all orders have valid accounts

DECLARE @start DATETIME, @end DATETIME
SELECT @start = DATEADD(minute, -10, GETDATE()), @end = DATEADD(minute, -1, GETDATE())
SELECT count(*)
  FROM database.dbo.orderTable t WITH (NOLOCK)
  WHERE tradetime BETWEEN @start AND @end
  AND NOT EXISTS (
  SELECT 1 FROM useridTable WHERE userid = t.userid
  AND acctid = t.acctid )




So this query...

DECLARE @start DATETIME, @end DATETIME
SELECT @start = DATEADD(minute, -10, GETDATE()), @end = DATEADD(minute, -1, GETDATE())
SELECT count(*)
  FROM database.dbo.orderTable t WITH (NOLOCK)
  WHERE tradetime BETWEEN @start AND @end
  AND NOT EXISTS (
  SELECT 1 FROM database.dbo.discrepancyTable d WHERE datereported >= left(getdate()-0, 11)
  AND d.status ='R'
  AND d.ordernumber = t.ordernumber )


counts how many orders don't have a discrepancy of status R, for today

And now I think I see the problem....

0
 
LVL 14

Accepted Solution

by:
twoboats earned 2000 total points
ID: 19565287
Does this give you the count of unresolved you are looking for?

DECLARE @start DATETIME, @end DATETIME
SELECT @start = DATEADD(minute, -10, GETDATE()), @end = DATEADD(minute, -1, GETDATE())
SELECT count(*)
  FROM database.dbo.orderTable t WITH (NOLOCK)
  WHERE tradetime BETWEEN @start AND @end
  AND EXISTS (
  SELECT 1 FROM database.dbo.discrepancyTable d WHERE datereported >= left(getdate()-0, 11)
  AND d.status ='U'
  AND d.ordernumber = t.ordernumber )
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 19565366
that query gives me one back, yes, for the record in discrepancy table with status = U
(what problem did you see?)

how do you feel, though, about the other half?
if a discrepancy exists in the orders table (which it does), and one or more still exist in discrepancy table with status = U, i am going to flag on both, right?
0
 
LVL 14

Expert Comment

by:twoboats
ID: 19565427
"how do you feel, though, about the other half?
if a discrepancy exists in the orders table (which it does), and one or more still exist in discrepancy table with status = U, i am going to flag on both, right?"

Yes.

The problem is, I was trying to convert what you had, and it had a bit of a bug, rather than getting an understanding of the logic first - sry.

Is there a reason for recording multiple discrepancies?
0
 
LVL 14

Expert Comment

by:twoboats
ID: 19565455
Also, is there a reason for copying the user accounts into #userids, rather than using useridTable directly?
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 19565480
>>Is there a reason for recording multiple discrepancies?

What do you mean?  This checker runs all day long through the trading day.  i flag on a number of different suspect items (bad IDs, bad symbol, missing prices, etc)
at time of occurence, the discrepancy is written to discrepancy table, the enduser (ops) is notified, and they resolve the situation
my problem is two-fold right now.
1- i only send email/alert upon first insertion into discrepancy table.  if enduser does not resolve it, i need to continue to notify  ( i think maybe we just got this)
2 - i need only to write the discrepancy to the discrepancy table ONE time.  right now, my logic keeps writing it over and over, until it is resolved.  i say 'over and over'..but, the job runs every 2 minutes.  if it is unresolved when the job runs again, it is written to discrepancy table again
0
 
LVL 14

Expert Comment

by:twoboats
ID: 19565513
Leaving the questions aside - we now have 2 ways of doing this (pick whichever you find quicker)

-- with not exists sub queries

SELECT       same bunch of fields........
FROM      database.dbo.orderTable t WITH (NOLOCK)
WHERE       tradetime BETWEEN @start AND @end
AND      NOT EXISTS (
            SELECT 1 FROM #userids WHERE userid = t.userid
            AND acctid = t.acctid )
OR      EXISTS (
            SELECT 1
            FROM database.dbo.discrepancyTable d
            WHERE datereported >= left(getdate()-0, 11)
            AND d.status ='U'
            AND d.ordernumber = t.ordernumber )
            
--- with outer joins              
            
SELECT      bunch of fields  FROM database.dbo.orderTable t,
      #userids u,
      database.dbo.discrepancyTable d
WHERE       t.tradetime BETWEEN @start AND @end
AND       t.userid *= u.userid
AND       t.acctid *= u.acctid
AND       t.ordernumber *= d.ordernumber
AND      d.datereported >= left(getdate()-0,11)
AND      d.status = 'U'
AND      u.active = 'y'

AND       (-- not exists in #userid
      u.userid IS NULL
      AND u.acctid IS NULL)
OR
      (exists discrepancy
      d.ordernumber is not null)
0
 
LVL 14

Expert Comment

by:twoboats
ID: 19565533
Hold on then.... let me come back to your answers..
0
 
LVL 14

Expert Comment

by:twoboats
ID: 19565583
In your proc at the top, you doing an insert to discrepancies if the user account doesn't exist, and you tried to add in a or that says or if there is a discrepancy that still exists with status 'U'

"1- i only send email/alert upon first insertion into discrepancy table.  if enduser does not resolve it, i need to continue to notify  ( i think maybe we just got this)"

Okay. So you should do the insert based on the bad data, not whether a discrepancy already exists with status 'U'

"2 - i need only to write the discrepancy to the discrepancy table ONE time.  right now, my logic keeps writing it over and over, until it is resolved.  i say 'over and over'..but, the job runs every 2 minutes.  if it is unresolved when the job runs again, it is written to discrepancy table again"

Yes, it will - becuase new rows to insert are selected where the account doesn't exist OR a dispcrepancy with status 'U' exists - it'll duplicate the discrepancy


Okay. So, how do you send the mail - is it because there's a trigger on discrapancy table by any chance?
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 19565827
i will ahve to assume i used the w/outer joins in error... i killed it at :52 seconds, to get a resultset back of 1085213 records --- every single one of them the same record

with not exists & subqueries found the one record, in the same :24 seconds I was finding it before

maybe i am stuck with this runtime
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 19565972
oh heck no.   i just ran it.. it ran for 51 seconds.  yeah, sure, it sent the email alert, which is cool
but not with a runtime of 51 seconds

2 - i need only to write the discrepancy to the discrepancy table ONE time.  right now, my logic keeps writing it over and over, until it is resolved.  i say 'over and over'..but, the job runs every 2 minutes.  if it is unresolved when the job runs again, it is written to discrepancy table again

this problem still very much exists.  it found the ONE record, but wrote two into the discrepancy table.  so now i've got three in the discrepancy table for the same discrepancy

eeewgh

>>Okay. So you should do the insert based on the bad data, not whether a discrepancy already exists with status 'U'

yes, absolutely
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 19566147
there is a query and a sentflag in my job table
the job runs every 2 minutes throughout the day, running the associated query
first step in the job is to check for output of that query (see below)
if output = 1 (meaning, any discrepancies are found) , sentflag is changed to 0, the email is sent

but.... i hate to even go here, we don't have a query yet which is performing as it should.  i am scared to look at the email at all, first because i use this methodology in MANY jobs, all of which are fine
and second, i don't understand why the email generation itself is the issue

see, if a discrepancy exists in the order table, it is written to discrepancy table, status = U, email is sent
job runs again 2 minutes later
any new discrepancy is handled the same as above
if any existing discrepancies still have status = U, the email should be sent again, until it is resolved

this is the 1st step of the job:
DECLARE @spcount BIT
EXEC database.dbo.PROCNAME @spcount OUTPUT

IF @spcount =1
BEGIN
     UPDATE jobTable SET sentflag = 0 WHERE subject = 'Sanity Check Failure'
END

if the procedure we are working on EITHER finds new discrepancies OR existing discrepancies with status = 'U', the output will be 1, the sentflag will be updated to 0, the email should be sent

do you see what i'm saying?
oh my gosh this is frustrating.  i just don't know what i am missing
0
 
LVL 14

Expert Comment

by:twoboats
ID: 19566170
Okay.

So the select for your insert to the discrepancy table should be

SELECT       same bunch of fields........
FROM      database.dbo.orderTable t WITH (NOLOCK)
WHERE       tradetime BETWEEN @start AND @end
AND      NOT EXISTS (
            SELECT 1 FROM #userids WHERE userid = t.userid
            AND acctid = t.acctid )

or this

SELECT      bunch of fields
FROM      database.dbo.orderTable t,
      #userids u
WHERE       t.tradetime BETWEEN @start AND @end
AND       t.userid *= u.userid
AND       t.acctid *= u.acctid
AND       (-- not exists in #userid
      u.userid IS NULL
      AND u.acctid IS NULL)

Which is where you started.


Now comes this bit

-- I also want to know, however, if any record(s) exist in the discrepancy table that haven't yet been resolved -- status = U (if the record is in discrepancy with status = U, that means it is still in the order table unresolved)

But your proc is doing an insert to discrepancies, and you're adding this as critirea to select the orders to insert - it's already there.

Which is why I asked about the trigger. Is the email sent by a trigger on the table?


0
 
LVL 17

Author Comment

by:dbaSQL
ID: 19566289
from above:  
there is a query and a sentflag in my job table
the job runs every 2 minutes throughout the day, running the associated query
first step in the job is to check for output of that query (see below)
if output = 1 (meaning, any discrepancies are found) , sentflag is changed to 0, the email is sent

and this:
this is the 1st step of the job:
DECLARE @spcount BIT
EXEC database.dbo.PROCNAME @spcount OUTPUT

IF @spcount =1
BEGIN
     UPDATE jobTable SET sentflag = 0 WHERE subject = 'Sanity Check Failure'
END

if the procedure we are working on EITHER finds new discrepancies OR existing discrepancies with status = 'U', the output will be 1, the sentflag will be updated to 0, the email should be sent


i dont' know if i'd call that a trigger.... but, that is how my email is driven
0
 
LVL 14

Expert Comment

by:twoboats
ID: 19566425
Ahhhhhhhh!!!!!

Right. You issue isn't about the insert ... select satement. It's about the setting the output value.

Presumably, after the insert ... select you set @OUTPUT = to the number of rows inserted?

If so, leave the discrepancies off the the select, but add in

select      @output = @output + count(t.ordernumber)
from      database.dbo.orderTable t WITH (NOLOCK),
      database.dbo.discrepancyTable d
where      t.tradetime BETWEEN @start AND @end
and      datereported >= left(getdate()-0, 11)
and      d.status ='U'
and      d.ordernumber = t.ordernumber


So you're doing the following logic

set the output to number of new discrepancies + number of unresolved discrepancies.

And you shouldn't have a problem with how long it takes any more ;)
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 19566430
>>Yes, it will - becuase new rows to insert are selected where the account doesn't exist OR a dispcrepancy with status 'U' exists - it'll duplicate the discrepancy

possibly i can correct this by divvying up my logic just a bit, maybe like this:

CREATE proc dbo.PROCNAME
 @output bit = NULL output
AS

SET NOCOUNT ON
SET @OUTPUT = 0

--USERID/ACCTID CHECKER
create table #userids (userid varchar(20),acctid varchar(20),unique clustered (userid,acctid))
insert #users select distinct userid,acctid
from server.database.dbo.useridTable
where active = 'y'

DECLARE @start DATETIME, @end DATETIME
SELECT @start = DATEADD(minute, -10, GETDATE()), @end = DATEADD(minute, -1, GETDATE())

--USERID/ACCTID CHECKER
IF EXISTS (
SELECT 1 FROM database.dbo.orderTable t WITH (NOLOCK)
WHERE timeofexecution BETWEEN @start AND @end
AND NOT EXISTS (
SELECT 1 FROM #userids WHERE userid = t.userid
AND acctid = t.acctid
) )
BEGIN  
  SET @output = 1
  PRINT 'Suspect Trades have been identified:  userid/acctid
      http://blah/blah.php' 

  INSERT INTO database.dbo.discrepancyTable (.........bunch of fields........)
  SELECT .........same bunch of fields........
  FROM database.dbo.orderTable t WITH (NOLOCK)
  WHERE tradetime BETWEEN @start AND @end
  AND NOT EXISTS (
  SELECT 1 FROM #userids WHERE userid = t.userid
  AND acctid = t.acctid )
END

DECLARE @start DATETIME, @end DATETIME
SELECT @start = left(getdate(),11) 0, GETDATE()), @end = DATEADD(minute, -10, GETDATE())

  IF EXISTS ( SELECT 1 FROM database.dbo.discrepancyTable d
  WHERE datereported >= left(getdate()-0, 11)
  AND d.status ='U'
  AND d.tradetime BETWEEN @start AND @stop)
  BEGIN
      PRINT 'Suspect Trades still require resolution
                http://blah/blah.php' 
     END

I don't know if that's really necessary, but what do you think?  this will at least prevent dupe insertions if/when an existing discrepancy is still unresolved
and it will look outside the current 10 minute window

yeah?
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 19566445
i'm sorry, twoboats.... you totally lost me there
0
 
LVL 14

Expert Comment

by:twoboats
ID: 19566462
Yes - you definitely need a 2 stage process...

Hang on a mo....
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 19566470
...oh, that isn't all i need right now!
(kidding)
0
 
LVL 14

Expert Comment

by:twoboats
ID: 19566489
"if the procedure we are working on EITHER finds new discrepancies OR existing discrepancies with status = 'U', the output will be 1, the sentflag will be updated to 0, the email should be sent"

So to get the output to be 1, you need to either insert rows, or find discrepancies with status U.

Ignore my count - you're working on a 1 or 0 for the output from the proc.

hang on....
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 19566568
>>Ignore my count - you're working on a 1 or 0 for the output from the proc.  
cool.  we're on the same page.  i think i have the change made... the one that splits apart the insert of new descrepancies from the check of pre-existing discrepancies w/status = U

this only applies to my writing the darned discrepancy over and over.... but still, very important
i will wait to hear back from you
0
 
LVL 14

Expert Comment

by:twoboats
ID: 19566738
create proc dbo.procname @output bit = null output
as
 
set nocount on
set @output = 0
set @unresolved = 0
 
declare @start datetime, @end datetime, @unresolved int
select @start = dateadd(minute, -10, getdate()), @end = dateadd(minute, -1, getdate())

-- identify any problem orders and create a discrepancy
-- but don't create a new discrepancy if an unresolved
-- one for the order already exists

insert into database.dbo.discrepancytable (.........bunch of fields........)
select .........same bunch of fields........
from      database.dbo.ordertable t with (nolock)
where      tradetime between @start and @end
and not exists (select 1
                from server.database.dbo.useridtable
                where userid = t.userid
                and acctid = t.acctid
                and active = 'y')
and not exists (select 1
            from database.dbo.discrepancytable d
            where datereported >= left(getdate()-0, 11)
            and d.status ='u'
            and d.ordernumber = t.ordernumber )

                
-- use @@rowcount to check if new ones were found
if @@rowcount > 1
begin
      set @output = @output | 1
       print 'suspect trades have been identified: userid/acctid http://blah/blah.php'
end             
                

-- are there any discrepancies waiting to be processed
select      @unresolved = count(t.ordernumber)
from      database.dbo.ordertable t with (nolock),
      database.dbo.discrepancytable d
where      t.tradetime between @start and @end
and      datereported >= left(getdate()-0, 11)
and      d.status ='u'
and      d.ordernumber = t.ordernumber

if @unresolved > 1
begin
      set @output = @output | 1
       print 'suspect trades still require resolution http://blah/blah.php'
end             

0
 
LVL 17

Author Comment

by:dbaSQL
ID: 19566850
i am concerned about this:  set @unresolved = 0
and the change to setting the output within the proc
i guess i am paranoid, probably

why the output change?
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 19566875
and why is the temp table gone?
0
 
LVL 14

Expert Comment

by:twoboats
ID: 19566884
"why the output change?"

Because you want the emails to get sent again.

DECLARE @spcount BIT
EXEC database.dbo.PROCNAME @spcount OUTPUT

IF @spcount =1
BEGIN
     UPDATE jobTable SET sentflag = 0 WHERE subject = 'Sanity Check Failure'
END


If the ouput is 0, they won't be. If we set the output to 1 because either there are new discrepancies or there are existing ones, then the above bit sends the email.

"set @unresolved = 0"

Don't worry @unresolved is a local variable that I've introduced.





0
 
LVL 17

Author Comment

by:dbaSQL
ID: 19566897
oooooooooooooh.... was my output not 1 ?
0
 
LVL 14

Expert Comment

by:twoboats
ID: 19566898
"and why is the temp table gone?"

Well, you don't need it. All you were doing was using up system resources to take a copy of data.
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 19566911
ok... but i was concerned that the rpc to the remote server was equally resourceful
no?
0
 
LVL 14

Expert Comment

by:twoboats
ID: 19566990
Sry, yes, was forgetting it's remote.

In which case, not sure which is quicker - I'd test both ways to find out.
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 19567030
k.... still working on this, i'll let you know shortly
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 19567073
Server: Msg 137, Level 15, State 1, Procedure procTest, Line 9
Must declare the variable '@unresolved'.

0
 
LVL 17

Author Comment

by:dbaSQL
ID: 19567170
i don't see the error.  do you?
0
 
LVL 14

Expert Comment

by:twoboats
ID: 19567282
Yes

need to move

set @unresolved = 0

to under the declare statement - doh ;)
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 19567952
twoboats, are you around?
0
 
LVL 14

Expert Comment

by:twoboats
ID: 19567984
yes - but not constantly
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 19568154
i'm having a problem firing it
remember, we do electronic trading....there's a lot of priority sitting on these alerts...a lot of people watching.  so, i'm doing it all in the dev bed first to see everything is good, then i'll deploy

silly question.. but... the output i get from my current proc is what then sets the sentflag for my email

set @output =0

if exists
blah blah
set @output = 1

of course, if anything is found, output = 1
job step 1:
DECLARE @spcount BIT
EXEC PROCNAME @spcount OUTPUT
IF @spcount =1
BEGIN
     UPDATE JOBTABLE SET sentflag = 0 WHERE subject = 'TESTSanityChecker'
END

job step 2:
EXEC dbo.SendAsyncEmail @ResultPath='...',@MailHost='smtpservername.com',@DefaultRecipient='...',@Subject='testAlert'

( in step 2, email is sent only for those where sentflag = 0 )

this test proc works.  it finds the discrepancy and then writes it to the DiscrepancyTEST table,
but that's it
how am i triggering this email?

i totally don't know if i voiced myself correctly
do you see what i mean?
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 19568254
yeah, the unresolved piece must not be handing it properly
the email is sent, with no content
the record does exist in my discrepancy table, status = u
but nothing in the body of the email
i'll keep trying
0
 
LVL 14

Expert Comment

by:twoboats
ID: 19568266
"silly question.. but... the output i get from my current proc is what then sets the sentflag for my email "

Yes, I follow that.

"do you see what i mean?"

Not really.

I follow you use the output parameter from the proc we've been working as the check to see whether to do the update jobtable, which is why the version I posted first sets it to 0, and then on 2 occaisions does checks to see if it needs to set output to 1 - once if new discrepancies are inserted, and once if there are unresolved discrepancies.

"how am i triggering this email?"

I don't know
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 19568290
yeah, i deleted it from the discrepancy table, allowed it to re-run/re-find the discrepancy
it found it, wrote it to the discrepancy table, but no email was sent
when it exists already, the email is sent, but it is empty

and, now i can't get the email to gen at all....
errgh
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 19568303
well, this is a problem
finding the suspect data is great, but i have to issue notice on it
that's the whole point behind this checker
now i'm not sure what to do
0
 
LVL 14

Expert Comment

by:twoboats
ID: 19568365
what's the code that sends emails look like?
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 19568436
very big and very much in use in many of my jobs
http://www.sswug.org/see/15091

again, though, i am really unsure why we'd look there.  if the output on the other proc(s) gen the email, couldn't we do the same with this one?
0
 
LVL 14

Expert Comment

by:twoboats
ID: 19568507
Okay, let's not look there.

Have you checked the output from the proc?

0
 
LVL 17

Author Comment

by:dbaSQL
ID: 19568531
twoboats, i'm not sure what/how to check that?
0
 
LVL 14

Expert Comment

by:twoboats
ID: 19568548
Run this in query analyser

DECLARE @spcount BIT
EXEC PROCNAME @spcount OUTPUT
select 'the output value from the proc was:', @spcount
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 19568583
wait... i got it to send an email
again, only on the pre-existing discrepancies that are not yet resolved (status = u )
let me create another new one and see what happens
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 19568792
well, now it's not sending again... give me some time, twoboats, i need to walk thru this end to end, i will let you know just as soon as i can
0
 
LVL 14

Expert Comment

by:twoboats
ID: 19568945
no probs
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 19568972
thanks much, cross your fingers
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 19577184
sorry, two boats.  this just is not coming together.  i wanted you to know i am still trying to utilize your suggestion   no-go, yet.  but i'm still trying
0
 
LVL 14

Expert Comment

by:twoboats
ID: 19577261
No worries. Sounds like you have a complex setup.

"very big and very much in use in many of my jobs
http://www.sswug.org/see/15091"

I had a look at the url, but to see anything, need to register.

m I right in thinking that you've got a big chunk of code on there?
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 19577374
oh yes... you're right about that.  but i know that's not the problem.  it's my version of your procedrue...that's where i'm struggling.
that email piece is in place on all servers, nearly all jobs...
but, my problem is i find the discrepancy, write it to the discrepany table, and then do nothing further
still working thru it, i must get sometjing together tonight/tomorrow
i will let you know something asap
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 19577459
ok, i just now FINALLY got the darned thing to send the email
it's only the initial email... upon first finding the discrepancies.. so, i still have to test subsequent alerts.  the big thing is firing the subsequent alert w/out writing yet another record into the discrepancy table
this is where i was tripping major yesterday
i'll let you know shortly
cross your fingers cross your fingers ....
0
 
LVL 14

Expert Comment

by:twoboats
ID: 19577616
"the big thing is firing the subsequent alert w/out writing yet another record into the discrepancy table
this is where i was tripping major yesterday"

Quite.

Since it's the output from the proc that triggers the email, you should be okay.

If it appears to not work, verify the proc gives the right output.



0
 
LVL 17

Author Comment

by:dbaSQL
ID: 19577623
oh my gosh... it worked.... sort of
ok, i made 2 discrepancies, thye ONLY existed in the order table
ran the job, it found both, and wrote 2 records to the discrepancy table, and sent me the email
i then ran the job again, there were no new discrepancies, but it sent the email again

all good

one problem.  the 1st email i got said this:
'Suspect Orders still await resolution:  UserID/AcctID http://blah/blah.php'

so did the 2nd one

the 1st one is supposed to be this:
'Suspect Orders have been identified:  UserID/AcctID    http://blah/blah.php'

gimme a sec, i'll show you the code
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 19577707
well, i ran it twice, it worked twice.  i just ran it again, it worked again.  w/exception to the fact that the 1st email msg isn't coming like it should... i believe it's good.
do you see the error, what is giving only the 2nd msg, even when the 1st msg is expected?

CREATE proc dbo.PROCNAME
 @output bit = null output
as
 
set nocount on
set @output = 0
 
declare @start datetime, @end datetime, @unresolved int
select @start = dateadd(minute, -10, getdate()), @end = dateadd(minute, -1, getdate())
set @unresolved = 0

-- identify any problem orders and create a discrepancy but don't create a new discrepancy if an unresolved one for the fill already exists
INSERT INTO DiscrepancyTable (.......bunch of fields.........)
SELECT 'U',SYSTEM_USER,CONVERT(VARCHAR(50),GETDATE(),101),'ID',..........same bunch of fields.......
FROM database.dbo.table t WITH (NOLOCK)
WHERE tradetime BETWEEN @start AND @end
AND NOT EXISTS(SELECT 1 FROM server.database.dbo.userIDs
             WHERE userid = t.userid
               AND acctid = t.acctid
               AND active = 'y')
        AND NOT EXISTS(SELECT 1 FROM DiscrepancyTable d
               WHERE datereported >= left(getdate()-0, 11)
               AND d.status ='u'
               AND d.ordernumber = t.ordernumber )
               
-- use @@rowcount to check if new ones were found
IF @@rowcount > 1
BEGIN
      SET @output = @output | 1
      PRINT  'Suspect Orders have been identified:  userID/acctID
            http://blah/blah.php'
END            
               
-- are there any ID discrepancies waiting to be processed?
SELECT @unresolved = count(t.ordernumber)
FROM database.dbo.table t WITH (NOLOCK),DiscrepancyTable d
WHERE t.tradetime BETWEEN @start AND @end
AND d.datereported >= left(getdate()-0, 11)
AND d.status ='u'
AND d.ordernumber = t.ordernumber

IF @unresolved > 1
BEGIN
     SET @output = @output | 1
     PRINT 'Suspect Orders still await resolution:  userID/acctID
            http://blah/blah.php'
END
GO
0
 
LVL 14

Expert Comment

by:twoboats
ID: 19577748
I don't believe that has anything to do with the proc, as the proc should set the output bit on a new discrepancy, or on an unresolved one.

To confirm this, run it in SQL Query Analyser

DECLARE @spcount BIT
EXEC PROCNAME @spcount OUTPUT
select 'the output value from the proc was:', @spcount


For the 2 cases

- where there are no existing unresolved, but there is a new one
- where there is at least one existing unresolved, and no new ones

If the value of spcount is 1 in both cases, then the problem lies with what happens after

UPDATE JOBTABLE SET sentflag = 0 WHERE subject = 'TESTSanityChecker'

Occurs - i.e. in the code that's handling what hapens when the jobtable is updated
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 19581678
ok, new day, created 2 discrepancies in orders table, no discrepancies exist in discrepancy table
i ran the proc in query analyzer as you suggested, it returned 1
(the query also wrote the two orders into the discrepancy table)

i ran it again in query analyzer, the output was 1 again, it did not write the orders into the discrepancy table

>>If the value of spcount is 1 in both cases, then the problem lies with what happens after
UPDATE JOBTABLE SET sentflag = 0 WHERE subject = 'TESTSanityChecker'
Occurs - i.e. in the code that's handling what hapens when the jobtable is updated

so that's where the problem is?
this is job step1:

DECLARE @spcount BIT
EXEC procname @spcount OUTPUT

IF @spcount =1
BEGIN
     UPDATE jobTable SET sentflag = 0 WHERE subject = 'JobName'
END

step 2 just runs the job/sends the email if the sentflag has been updated to 0

twoboats, i don't see how this could be the problem
why?  because the update to 0 is occurring and the email is being sent in both cases -- it's just being sent with the wrong text in case 1

For the 2 cases
- where there are no existing unresolved, but there is a new one
- where there is at least one existing unresolved, and no new ones

possibly there is an error in my flow between these two items? :
(it's going straight to @unresolved, as opposed to stopping at @@rowcount?

-- use @@rowcount to check if new ones were found
IF @@rowcount > 1
BEGIN
      SET @output = @output | 1
      PRINT  'Suspect Orders have been identified:  userID/acctID
            http://blah/blah.php' 
END            
               
-- are there any ID discrepancies waiting to be processed?
SELECT @unresolved = count(t.ordernumber)
FROM database.dbo.table t WITH (NOLOCK),DiscrepancyTable d
WHERE t.tradetime BETWEEN @start AND @end
AND d.datereported >= left(getdate()-0, 11)
AND d.status ='u'
AND d.ordernumber = t.ordernumber

IF @unresolved > 1
BEGIN
     SET @output = @output | 1
     PRINT 'Suspect Orders still await resolution:  userID/acctID
            http://blah/blah.php'
END
GO

0
 
LVL 14

Expert Comment

by:twoboats
ID: 19582164
"possibly there is an error in my flow between these two items? :
(it's going straight to @unresolved, as opposed to stopping at @@rowcount?"

No, because we set @output based on an or operator (the pipe)

So initially, it's 0 (set at the begining).

Then if @@rowcount > 1 (which is wrong - it should be > 0 - there's a bug)
 we do

SET @output = @output | 1

This will set it to 1, as (0 or 1) or 1 = 1

Then

IF @unresolved > 1

we do the same

SET @output = @output | 1

This will set it to 1, as (0 or 1) or 1 = 1

So it tests both cases - and sets output to 1 whether there are new discrepancies or existing unresolved ones.

But need to change  

if @@rowcount > 1

to

 if @@rowcount > 0

0
 
LVL 14

Expert Comment

by:twoboats
ID: 19582194
"ok, new day, created 2 discrepancies in orders table, no discrepancies exist in discrepancy table
i ran the proc in query analyzer as you suggested, it returned 1
(the query also wrote the two orders into the discrepancy table)

i ran it again in query analyzer, the output was 1 again, it did not write the orders into the discrepancy table"

This proves the proc is correct (except it would only work if there were 2 or more new discrepancies)

If there are new discrepancies, or unresolved existing ones, the code

DECLARE @spcount BIT
EXEC PROCNAME @spcount OUTPUT
IF @spcount =1
BEGIN
     UPDATE JOBTABLE SET sentflag = 0 WHERE subject = 'TESTSanityChecker'
END

will result in @spcount being 1, and therefore the update will happen.

Since it's another job (or job step) that then processes jobtable, the problem must lie there.

0
 
LVL 17

Author Comment

by:dbaSQL
ID: 19582249
>>.Then if @@rowcount > 1 (which is wrong - it should be > 0 - there's a bug)
I am not following, twoboats, nor do I know how to move forward.
0
 
LVL 14

Expert Comment

by:twoboats
ID: 19582963
Okay.

There's a bug. After the insert, it checks if @@rowcount (which tells us the number of rows inserted) is >1. This is wrong. We need to set @output to 1 when more than 1 row is inserted i.e. when a new discrepancy is found - not when more than 1 discrepancy is found.

so it should be if @@rowcount > 0

0
 
LVL 17

Author Comment

by:dbaSQL
ID: 19591690
well, twoboats, i made the suggested change.  i then created one discrepancy in the orders table, and fired the job (there were no pre-existing orders in the discrepancy table)

it found it and it sent an alert/email notification
there is, however, no body in that email.  it is completely blank
i fired the job again, nothing happened, no emails, nothing
surely you're as frustrated by this one as i am by now
it is extremely urgent, i must get around this and get something into prod
at an absolute minimum, i need to quit writing dupes to the discrepancy table w/the current logic that is in production
are you avail, or able to walk thru this w/me?
0
 
LVL 14

Expert Comment

by:twoboats
ID: 19592382
"there is, however, no body in that email.  it is completely blank"

The code that sends the email is outside this proc - need to look at that

"i fired the job again, nothing happened, no emails, nothing"

Since this proc returns 1 in both cases, which causes the updat of the jobtable, suggest that the code that sends the email checks something else, which stops the email being sent for existing unresolved discrepanmcies.

"are you avail, or able to walk thru this w/me?"

Off and on (mostly off I'm afraid) - got work to do :(
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 19592484
unerstood, twoboats.  i will just see what i can do
0
 
LVL 14

Expert Comment

by:twoboats
ID: 19592785
I looked at the link you posted for the mail code

http://www.sswug.org/see/15091"

can't see anything without registering - is there anything there?
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 19592839
i don't know exactly what you're looking for, twoboats
but no, i don't see anything in the email logic that is even questionable
0
 
LVL 14

Expert Comment

by:twoboats
ID: 19592974
Want to see the code that reads the jobtable - in particular the bit that handles WHERE subject = 'TESTSanityChecker'
0
 
LVL 14

Expert Comment

by:twoboats
ID: 19598977
Any progress?
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 19599149
unfortunatey, no, not much progress at all.  i'm near the point of calling msft supt, the urgency on this thing is terrible.  but surely they'll walk thru everything that you and i are doing, and i don't care to start down that path again

>>Want to see the code that reads the jobtable - in particular the bit that handles WHERE subject = 'TESTSanityChecker'

i have to assume it's reading it just fine, given the email is being sent.  the body of the email, however, isn't present.  

i am going to start from scratch right now, i'll let you know shortly my results
0
 
LVL 14

Expert Comment

by:twoboats
ID: 19599175
Okay.

Is there a reason that the sending of the email is outside of this procedure? If it were me, identifying dicrepancies and sending alerts about them would all be in the same proc.
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 19599185
well, at this point, if it gets me there faster, i've got no problem giving it a go.  i am not sure, though, how to wrap all that together.  the piece we've been working with is only a subset of the entire discrepancy checker.  i am wondering, might i cut/paste the whole thing, or possibly email it to you?  would you be able to advise on the best means of handling the email within the same procedure?
0
 
LVL 14

Expert Comment

by:twoboats
ID: 19600720
send the whole lot across, I'll see if I can find what's going on
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 19600947
alright, twoboats.  i need to take a moment to genericize a few things... nothing big..i'm just replacing server/db/attribute names, in some places, in order to avoid posting our codebase on the web.
no changes in the logic, just in the object names
and my changes will be quite obvious... i.e, 'databaseName'
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 19601451
ok.  it's a big one.  the piece you've seen thus far is the 1st item i check -- IDs.
that and all the others are labeled with comments  --- ID checker, -- symbol checker, etc.
you will see i create my temp tables up top, then remove them, of course, all together at end of proc
i also declare my @start/@stop parms up top, too, rather than down within each piece of logic

note -- this piece in the ID checker:
--  AND NOT EXISTS (SELECT 1 FROM dbo.DiscrepancyTable d WHERE datereported >= left(getdate()-0, 11) AND ActionType='ID' AND d.ordernumber = t.ordernumber AND d.status = 'R')    

you will see, I tried to do something similar across the board -- hoping to avoid writing dupes for already inserted discrepancies ... it's commented out, of course, cuz that isn't working.  

remember, i have two problems -
1)  i write discrepancies repeatedly into the discrepancy table, after the initial insert
2)  i need to gen the alert if the discrepancy still exists as unresolved - (status = U)
    but i need to gen this alert/email/notification without again writing the record to the discrepancy table

please know that any assistance, twoboats, is hugely, HUGELY appreciated.
i am anxious to hear back from you


CREATE proc dbo.procname
 @output bit = NULL output
AS

SET NOCOUNT ON
SET @OUTPUT = 0

--ID CHECKER
create table #userids (userid varchar(20),acctid varchar(20),unique clustered (userid,acctid))
insert #userids select distinct userid,acctid
from sqlserver2.supportDB.dbo.userids
where active = 'y'
--SYMBOLS
create table #symbolsA (symbol varchar(8),unique clustered (symbol))
insert #symbolsA select '?'
union
select ''
union
select 'UNKNOWN'
--SIDE
create table #side (side char(1),unique clustered (side))
insert #side select 'B'
union
select 'S'
--ENDPOINT
create table #endpoints (endpoint varchar(8),unique clustered (endpoint))
insert into #endpoints select distinct endpoint from sqlserver2.supportDB.dbo.epgroups
--PRICE
create table #symbolsB (symbol varchar(16),unique clustered (symbol))
insert into #symbolsB select distinct symbol from dbo.ordersTable where symbol like  '%[-:]%'

--SET TIME PARMS
DECLARE @start DATETIME, @end DATETIME
SELECT @start = DATEADD(minute, -10, GETDATE()), @end = DATEADD(minute, -1, GETDATE())

--ID CHECKER
IF EXISTS (
SELECT 1 FROM dbo.ordersTable t WITH (NOLOCK)
WHERE tradetime BETWEEN @start AND @end
AND NOT EXISTS (SELECT 1 FROM #userids WHERE userid = t.userid AND acctid = t.acctid)
)
BEGIN  
  SET @output = 1
  PRINT 'Suspect Orders have been identified:  userid/acctid
      http://blah/blah.php'

  INSERT INTO dbo.DiscrepancyTable (Status,UserID,DateResolved,ActionType,.... whole bunch of fields....)
  SELECT 'U',SYSTEM_USER,CONVERT(VARCHAR(50),GETDATE(),101),'ID',..........same bunch of fields.....
  FROM dbo.ordersTable t WITH (NOLOCK)
  WHERE tradetime BETWEEN @start AND @end
  AND NOT EXISTS (SELECT 1 FROM #userids WHERE userid = t.userid  AND acctid = t.acctid )
--  AND NOT EXISTS (SELECT 1 FROM dbo.DiscrepancyTable d WHERE datereported >= left(getdate()-0, 11) AND ActionType='ID' AND d.ordernumber = t.ordernumber AND d.status = 'R')    
  OPTION (MAXDOP 1)
END

--SYMBOL CHECKER
IF EXISTS (SELECT 1 FROM dbo.ordersTable T WITH (NOLOCK)
WHERE tradetime BETWEEN @start AND @end
AND NOT EXISTS (SELECT 1 FROM sqlserver2.supportDB.dbo.EPGroups WHERE endpoint = t.endpoint )
OR
tradetime BETWEEN @start AND @end AND SYMBOL IN ('','UNKNOWN','?') )

BEGIN
  SET @output = 1
  PRINT 'Suspect Orders have been identified:  Symbol
      http://blah/blah.php'
  INSERT INTO dbo.DiscrepancyTable (Status,UserID,DateResolved,ActionType,.... whole bunch of fields....)
  SELECT 'U',SYSTEM_USER,CONVERT(VARCHAR(50),GETDATE(),101),'Symbol',..........same bunch of fields.....
  FROM dbo.ordersTable t WITH (NOLOCK)
  WHERE tradetime BETWEEN @start AND @end
  AND EXISTS (
  SELECT 1 FROM #symbolsA WHERE symbol = t.symbol
  AND NOT EXISTS (
  SELECT 1 FROM dbo.DiscrepancyTable d WHERE datereported >= left(getdate()-0, 11) AND d.ActionType = 'Symbol' AND d.ordernumber = t.ordernumber  AND d.status = 'R')  )
  OPTION (MAXDOP 1)
END

--SIDE CHECKER
IF EXISTS (
SELECT 1 FROM dbo.ordersTable T WITH (NOLOCK)
WHERE tradetime BETWEEN @start AND @end
AND NOT EXISTS ( SELECT 1 FROM #side WHERE side = t.Side) )

BEGIN
  SET @output = 1
  PRINT 'Suspect Orders have been identified:  Side
      http://blah/blah.php'
  INSERT INTO dbo.DiscrepancyTable (Status,UserID,DateResolved,ActionType,.... whole bunch of fields....)
  SELECT 'U',SYSTEM_USER,CONVERT(VARCHAR(50),GETDATE(),101),'Side',..........same bunch of fields.....
  FROM dbo.ordersTable t WITH (NOLOCK)
  WHERE tradetime BETWEEN @start AND @end
  AND NOT EXISTS (
  SELECT 1 FROM #side WHERE side = t.Side)
  OPTION (MAXDOP 1)
END

--ENDPOINT CHECKER
IF EXISTS (
SELECT 1 FROM dbo.ordersTable t WITH (NOLOCK)
WHERE tradetime BETWEEN @start AND @end
AND NOT EXISTS (SELECT 1 FROM #endpoints WHERE endpoint = t.endpoint) )
BEGIN
  SET @output = 1
  PRINT 'Suspect Orders have been identified:  EndPoint
      http://blah/blah.php'
  INSERT INTO dbo.DiscrepancyTable (Status,UserID,DateResolved,ActionType,.... whole bunch of fields....)
  SELECT 'U',SYSTEM_USER,CONVERT(VARCHAR(50),GETDATE(),101),'EndPoint',..........same bunch of fields.....
  FROM dbo.ordersTable t WITH (NOLOCK)
  WHERE tradetime BETWEEN @start AND @end
  AND NOT EXISTS (SELECT 1 FROM #endpoints WHERE endpoint = t.endpoint)
  AND NOT EXISTS (SELECT 1 FROM dbo.DiscrepancyTable d WHERE datereported >= left(getdate()-0, 11) AND d.ActionType = 'EndPoint' AND d.ordernumber = t.ordernumber AND d.status = 'R')
  OPTION (MAXDOP 1)
END

--PRICE CHECKER
IF EXISTS ( SELECT * FROM dbo.ordersTable t WITH (NOLOCK)
WHERE tradetime BETWEEN @start AND @end AND price <=0
AND NOT EXISTS (SELECT 1 FROM #symbolsB WHERE symbol = t.symbol) )

BEGIN
  SET @output = 1
  PRINT 'Suspect Orders have been identified:  Price
      http://blah/blah.php'

  INSERT INTO dbo.DiscrepancyTable (Status,UserID,DateResolved,ActionType,.... whole bunch of fields....)
  SELECT 'U',SYSTEM_USER,CONVERT(VARCHAR(50),GETDATE(),101),'Price',..........same bunch of fields.....
  FROM dbo.ordersTable t WITH (NOLOCK)
  WHERE tradetime BETWEEN @start AND @end  AND price <=0
  AND NOT EXISTS (SELECT 1 FROM #symbolsB WHERE symbol = t.symbol)
  AND NOT EXISTS (SELECT 1 FROM dbo.DiscrepancyTable d WHERE datereported >= left(getdate(), 11)  AND d.ActionType = 'Price' AND d.ordernumber = t.ordernumber AND d.status = 'R')
  OPTION (MAXDOP 1)
END

--QUANTITY CHECKER
IF EXISTS ( SELECT * FROM dbo.ordersTable t WITH (NOLOCK)
WHERE tradetime BETWEEN @start AND @end
AND quantity <=0
AND endpoint <> 'HHHH'
OR
tradetime BETWEEN @start AND @end
AND quantity <0
AND endpoint = 'HHHH'
OR
tradetime BETWEEN @start AND @end
AND quantity >100
AND endpoint = 'FFFF' )

BEGIN
  SET @output = 1
  PRINT 'Suspect Orders have been identified:  Quantity
      http://blah/blah.php'
  INSERT INTO dbo.DiscrepancyTable (Status,UserID,DateResolved,ActionType,.... whole bunch of fields....)
  SELECT 'U',SYSTEM_USER,CONVERT(VARCHAR(50),GETDATE(),101),'Quantity',..........same bunch of fields.....
  FROM dbo.ordersTable t WITH (NOLOCK)
  WHERE tradetime BETWEEN @start AND @end
  AND quantity <=0
  AND endpoint <> 'HHHH'
  AND d.status = 'R')
  OR
  tradetime BETWEEN @start AND @end
  AND quantity <0
  AND endpoint = 'HHHH'
  AND d.status = 'R')  
  OR
  tradetime BETWEEN @start AND @end
  AND quantity >100
  AND endpoint = 'FFFF'
  AND NOT EXISTS (SELECT 1 FROM dbo.DiscrepancyTable d WHERE datereported >= left(getdate(), 11)  AND d.ActionType='Quantity' AND d.ordernumber = t.ordernumber
  AND d.status = 'R' )
  OPTION (MAXDOP 1)
END

--TIME CHECKER
IF EXISTS (
SELECT * FROM dbo.ordersTable t WITH (NOLOCK)
WHERE tradetime >= DATEADD(mi,61,getdate()) )
--AND NOT EXISTS (  SELECT 1 FROM dbo.DiscrepancyTable d WHERE datereported >= left(getdate(), 11)  AND d.ActionType='Time' AND d.ordernumber = t.ordernumber ))
BEGIN
  SET @output = 1
  PRINT 'Suspect Orders have been identified:  TradeTime
      http://blah/blah.php'
  INSERT INTO dbo.DiscrepancyTable (Status,UserID,DateResolved,ActionType,.... whole bunch of fields....)
  SELECT 'U',SYSTEM_USER,CONVERT(VARCHAR(50),GETDATE(),101),'Time',..........same bunch of fields.....
  FROM dbo.ordersTable t WITH (NOLOCK)
  WHERE tradetime >= DATEADD(mi,61,getdate())
  AND NOT EXISTS (
  SELECT 1 FROM dbo.DiscrepancyTable d WHERE datereported >= left(getdate(), 11)  AND d.ActionType='Time' AND d.ordernumber = t.ordernumber AND d.status = 'R')
  OPTION (MAXDOP 1)
END

DROP TABLE #userids
DROP TABLE #symbolsA
DROP TABLE #side
DROP TABLE #endpoints
DROP TABLE #symbolsB

SET NOCOUNT OFF
RETURN @OUTPUT
GO
0
 
LVL 14

Expert Comment

by:twoboats
ID: 19601575
What about the code that actually generates the email?
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 19601710
this is the 1st step in the job, which updates the sentflag:

DECLARE @spcount BIT
EXEC procname @spcount OUTPUT

IF @spcount =1
BEGIN
     UPDATE jobTable SET sentflag = 0 WHERE subject = 'Sanity Check Failure'
END
END

--this is the 2nd step, which sends the email IF sentflag = 0
EXEC dbo.SendAsyncEmail @ResultPath='c:\...\',@MailHost='smtpMailServerName',@DefaultRecipient='me.com',@Subject='Sanity Check Failure'

and this is dbo.SendAsyncEmail  -- again, remember, i did not write this, i got it at sswug.com, i use it on MANY of my jobs and it works beautifully.  i honestly do not understand how this is the problem at all, but here it is, every piece of the puzzle:

CREATE PROCEDURE [dbo].[SendAsyncEmail]
@ResultPath varchar(255),
@MailHost varchar(255),
@DefaultRecipient varchar(50),
@subject varchar(255)
AS
/*******************************************************************************************************
*      dbo.SendAsyncEmail
*      Project:           SendAsyncEmail - replaces xp_sendmail
*      Description:      SMTP replacement for xp_sendmail.
*      Notes:      SentFlag values used  include
*                 0 - ready to send
*                 1 - being sent
*                 2 - sent
*                 3 - cancelled
*                 any negative number - failed to send
*
*      Usage:
            exec dba_stat dbo.SendAsyncEmail
                  @ResultPath = 'C:\temp' -- or '\\<file server>\<share>'
                  , @MailHost = 'MAILSERVER.yourdomain.com'
                  , @DefaultRecipient = 'DBA@yourdomain.com'
*         Developer Name     Date     Brief Description
*         ------------------ -------- ------------------------------------------------------------
********************************************************************************************************/
-- declare variables
---------------------------------------------
declare @EmailId int
      , @rc int
      , @ec int
      , @Recipients varchar(512)
      , @Message varchar(2000)
      , @Query varchar(1000)
      , @CCRecipients varchar(255)
      , @BCCRecipients varchar(255)
--      , @Subject varchar(255)
      , @NoHeader varchar(5)
      , @Width int
      , @Separator varchar(255)
      , @DBUse varchar(255)
      , @SQLStr varchar(8000)
      , @ResultFile varchar(255)
      , @DateSend datetime
      , @EmailFromAddress varchar(50)
      , @EmailToAddress varchar(128)
      , @AttachResultsFile varchar(255)
      , @EmailFromName varchar(75)
      , @Priority varchar(6)
      , @Quote char(1)
      , @CrLf char(2)
      , @ReplyToAddress varchar(100)
      , @MessageFile varchar(255)
      , @Attachments varchar(2000)
      , @AttachResults varchar(5)
      , @RecipientWithTooMany varchar(512)
      , @SubjectWithTooMany varchar(255)
      , @EmailsToSend int
      , @DefaultFromAddress varchar(50)
---------------------------------------------
-- create temp tables
---------------------------------------------
-- create a temporary table to store file details.  This is used to determine query results > 120000
create table #filedetails
      (AlternateName varchar(33)
      , Size int
      , CreationDate int
      , CreationTime int
      , LastWrittenDate int
      , LastWrittenTime int
      , LastAccessedDate int
      , LastAccessedTime int
      , Attributes int)  

declare @worklist table (EmailId int primary key)                         
---------------------------------------------
-- set session
---------------------------------------------
SET NOCOUNT ON

set @Quote = char(39)
set @DateSend = getdate()
set @CrLf = char(13) + char(10)
-- instance name pluse same domaina as default recipient
set @DefaultFromAddress = replace(@@servername,'\','$')
+ substring(@DefaultRecipient,charindex('@',@DefaultRecipient),datalength(@DefaultRecipient))
---------------------------------------------
-- body of stored procedure
---------------------------------------------
-- remove old record once a day (keep 60 days)
if datepart(minute, getdate()) = 1 and datepart(hour, getdate()) = 1
      delete dbo.AsyncEmailQueue
      where Datestamp < getdate() - 60
      and SentFlag = 2

-- to prevent a race condition where a job is adding multiple request while
-- this proc is processing them always work from a snapshot of emails to send
-- more request for the same recipient and subject,
insert @worklist select EmailId from dbo.AsyncEmailQueue where SentFlag = 0
set @EmailsToSend = @@rowcount

while @EmailsToSend > 10
      begin                  
            -- Make sure the queue is not loaded with many requests for the same email
            select @EmailsToSend = max(x.[count]),
                  @RecipientWithTooMany = x.Recipients,
                  @SubjectWithTooMany = x.Subject
            from (select top 1 count(q.EmailId) [count], Recipients, Subject
                  from dbo.AsyncEmailQueue q
                    join @worklist w
                  on q.EmailId = w.EmailId      
                  where q.SentFlag = 0
                  group by q.Recipients, q.Subject
                  order by count(q.EmailId) desc) x
            group by x.Recipients, x.Subject

            if @@rowcount > 0 and @EmailsToSend > 10
                  begin
                        -- cancel
                        update dbo.AsyncEmailQueue
                        set SentFlag = 3
                        where Recipients = @RecipientWithTooMany
                        and Subject = @SubjectWithTooMany
                        and SentFlag = 0      

                        --remove from the worklist
                        delete w
                        from dbo.AsyncEmailQueue q
                          join @worklist w
                        on q.EmailId = w.EmailId      
                        where q.Recipients = @RecipientWithTooMany
                        and q.Subject = @SubjectWithTooMany
                        and q.SentFlag = 3      

                        -- tell somebody about the spammer
      Set @Message =  'The server has ' + cast(@EmailsToSend as varchar(10)) +
      + ' email request with subject ' + @CrLf + space(5) + @SubjectWithTooMany + @CrLf
+ 'queued to send to recipient(s) ''' + @RecipientWithTooMany + ''' . These messages will not '
+ 'be sent and are now set to status 3 (canceled) in dbo.AsyncEmailQueue to '
+ 'prevent excess load on the mail delivery system.'

exec @rc = master.dbo.xp_smtp_sendmail
@from = @DefaultFromAddress,
@from_name = @@servername,
@to = @DefaultRecipient,
@replyto = @DefaultRecipient,
@priority = 'high',
@subject = 'Excessive requests to send the same email.',
@message = @Message,
@server = @MailHost                  

set @ec = @@error
if @rc <> 0 or @ec <> 0
goto ErrorHandler
end
else
begin
      -- Get a new count to move to the main loop
                   select @EmailsToSend = count(EmailId) from @worklist
      break
      end      
      end

-- loop through the waiting emails if any exist
while @EmailsToSend > 0
      begin
            -- init variables for this iteration
            set @MessageFile = null
            -- pick up the ID of the first email waiting
            select top 1 @EmailId = q.EmailId,
      @Recipients = replace(isnull(q.Recipients,@DefaultRecipient), @CrLf, space(1))
      , @CCRecipients = replace(q.CopyRecipients, @CrLf, space(1))
      , @BCCRecipients = replace(q.BlindCopyRecipients, @CrLf, space(1))
      , @Message = isnull(q.Message,'')
      , @Query = replace(isnull(q.Query,''), @CrLf, space(1))
      , @Subject = isnull(q.Subject, 'SQL Server Message')
      , @NoHeader = isnull(q.NoHeader,'false')
      , @Width = isnull(q.Width,80)
      , @Separator = isnull(q.Separator,space(1))
      , @DBUse = isnull(q.DBUse,'tempdb')
      , @AttachResultsFile = isnull(q.AttachResultsFileName,'')
      , @EmailFromAddress = isnull(q.EmailFromAddress, @DefaultFromAddress)
      , @EmailFromName = isnull(q.EmailFromName, replace(@@servername,'\','$'))
      , @Priority = isnull(q.Priority,'normal')
      , @ReplyToAddress = isnull(q.ReplyToAddress,@DefaultRecipient)
      , @Attachments = replace(isnull(q.Attachments,''),',',';')
      , @AttachResults = isnull(q.AttachResults,'false')
      from dbo.AsyncEmailQueue q
            join @worklist w
            on q.EmailId = w.EmailId
            where q.SentFlag = 0
            
            -- set the flag (1 = attempting to send)
            update dbo.AsyncEmailQueue
            set SentFlag = 1
            where EmailId = @EmailId            
            
            -- if there is a query, execute the proc and place results in result file.
            if @Query <> ''
                  begin
-- prefix the resultfile content with the message this will cause redundant delivery of message text
-- but think this is better than creating two files when result set > 120K skip this concatenation if
-- user explicitly wants result attached
      if @Message <> '' and @AttachResults <> 'true'
      set @Query = 'print' + space(1) + @Quote
      + replace(replace(replace(replace(@Message
, @Quote , @Quote + @Quote), char(13)
, @Quote + ' + char(13) + ' + @Quote)
, char(10)
, @Quote + ' + char(10) + ' + @Quote)
, char(9)
, @Quote + ' + char(9) + ' + @Quote)
, @Quote + ' + char(13) + char(10) + char(13) + char(10)'
+ space(1) + @Query

-- generate a query result file at this location and name
if (substring(@ResultPath, datalength(@ResultPath), 1) <> '\')
set @ResultPath = @ResultPath + '\'
select @ResultFile = @ResultPath +
case when (@AttachResultsFile <> '')
      then case when charindex('.',@AttachResultsFile) > 0 -- has extension
                     then @AttachResultsFile
      else @AttachResultsFile + '.txt'            
end
else replace(@@servername,'\','$') + + '_'+Convert(Varchar(12), GetDate(), 112) + '.txt'
            end            
--build on osql string to move the query results to a file
select @SQLStr = 'osql -S' + @@servername + ' -E -d' + @DBUse + ' -t240 ' +
case when @NoHeader = 'true' then '-h-1 ' else ' ' end +
'-s"' + @Separator + '" ' +
'-w' + cast(@Width as varchar(10)) + ' ' +
'-Q"' + @Query + '" -n -m-1 -o"' + @ResultFile + '" -b'              
exec @rc = master.dbo.xp_cmdshell @SQLStr
set @ec = @@error
-- don't fail the send on a bad return code
-- could be a bad query and the error should be passed along
-- if the output file is not produced will fail in next step
      if @ec <> 0
      goto ErrorHandler
      insert #filedetails
      exec @rc = master.dbo.xp_getfiledetails @ResultFile
set @ec = @@error
if @rc <> 0 or @ec <> 0
goto ErrorHandler
--execute xp_smtp_sendmail..based on file size either use attachment or messagefile
if (select Size from #filedetails) > 120000
   or @AttachResults = 'true'
begin
if (select Size from #filedetails) > 2000000
begin
Print 'EmailId ' + cast(@EmailId as varchar(10)) +
+ ' requested a result set that contains '
+ 'more than 2MB of data. The result set was not '
+ 'attached to prevent excess load on the mail delivery '
+ 'system.'
GoTo ErrorHandler
end
      else
if @Attachments = ''
set @Attachments = @ResultFile
else      
set @Attachments = @Attachments + ';' + @ResultFile
end      
      else
      begin
      set @Message = null
set @MessageFile = @ResultFile
end
end -- there is a query to run
exec @rc = master.dbo.xp_smtp_sendmail
@from = @EmailFromAddress, @from_name = @EmailFromName, @to = @Recipients,
@replyto = @ReplyToAddress, @cc = @CCRecipients, @bcc = @BCCRecipients,
 @priority = @Priority, @subject = @Subject, @message = @Message,
@attachments = @Attachments, @messagefile = @Messagefile, @server = @MailHost
set @ec = @@error
if @rc <> 0 or @ec <> 0
goto ErrorHandler
-- mark this email as sent
update dbo.AsyncEmailQueue
set SentFlag = 2
where EmailId = @EmailId
-- clear temp table
truncate table #filedetails

set @EmailsToSend = @EmailsToSend - 1
end --end while

return

ErrorHandler:

-- Sent flag should always be less than 0 if failed to send
update dbo.AsyncEmailQueue
set SentFlag = case when @rc = 0 then -99 else -@rc end
where EmailId = @EmailId

Raiserror ('dbo.SendAsyncEmail failed to send EmailId %d, return code: %d, error: %d ',16,1,@EmailId, @rc, @ec)

SET NOCOUNT OFF
GO


0
 
LVL 14

Expert Comment

by:twoboats
ID: 19602302
Okay,

SendAsyncEmail - we'll ignore.
----------------------------------------------------------------------

IF sentflag = 0
EXEC dbo.SendAsyncEmail @ResultPath='c:\...\',@MailHost='smtpMailServerName',@DefaultRecipient='me.com',@Subject='Sanity Check Failure'

Isn't there more to this bit?
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 19602376
nope.  that's it
seriously

if there's output, update sentflag
if sentflag = 0, do the job/send the email
0
 
LVL 14

Expert Comment

by:twoboats
ID: 19602770
where's it getting sent flag from? As I undstand it, the proc is tested by this

 UPDATE JOBTABLE SET sentflag = 0 WHERE subject = 'TESTSanityChecker'

so some code somewhere surely must read the table
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 19602840
yes, of course.  sendasyncmail reads sentflag
my job sets it.  remember, the 1st step of the job -
if procedure output is 1 (which we know it is), it sets the flag to 0 :


DECLARE @spcount BIT
EXEC procname @spcount OUTPUT

IF @spcount =1
BEGIN
     UPDATE jobTable SET sentflag = 0 WHERE subject = 'Sanity Check Failure'
END
END
0
 
LVL 14

Expert Comment

by:twoboats
ID: 19603151
So is jobtable really dbo.AsyncEmailQueue?
0
 
LVL 14

Expert Comment

by:twoboats
ID: 19603405
"yes, of course.  sendasyncmail reads sentflag
my job sets it.  remember, the 1st step of the job -
if procedure output is 1 (which we know it is), it sets the flag to 0 :


DECLARE @spcount BIT
EXEC procname @spcount OUTPUT

IF @spcount =1
BEGIN
     UPDATE jobTable SET sentflag = 0 WHERE subject = 'Sanity Check Failure'
END
END"

I don't follow what's reading jobtable - certainly jobtable doesn't appear in the SendAsyncEmail proc.

and

IF sentflag = 0
EXEC dbo.SendAsyncEmail @ResultPath='c:\...\',@MailHost='smtpMailServerName',@DefaultRecipient='me.com',@Subject='Sanity Check Failure'


as a bit of code, that doesn't look like it makes sense.

"IF sentflag = 0"

sent flag isn't a variable (otherwise it'd have @ in front of it, and you can't do an if on a table column.



0
 
LVL 17

Author Comment

by:dbaSQL
ID: 19603572
oh gosh, twoboats, my bad.  'jobtable' = 'AsyncEmailQueue', and yes, the SendAsyncEmail proc is calling it quite a bit
0
 
LVL 14

Expert Comment

by:twoboats
ID: 19603681
Ok, and the

"IF sentflag = 0"

bit?
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 19604633
naah...it's just int.  see up top in the proc:

*      Notes:      SentFlag values used  include
*                 0 - ready to send
*                 1 - being sent
*                 2 - sent
*                 3 - cancelled
*                 any negative number - failed to send
0
 
LVL 14

Expert Comment

by:twoboats
ID: 19606502
How come you're using this EmailAsync proc?

The way I'd have done it would have been to call to do the email at the end of sproc - something like

If @output = 1
   exec @rc = master.dbo.xp_smtp_sendmail
                       @from = 'me@me.com',
                       @from_name = 'Sanity Checker',
                       @to = 'whoever@fix-problems.com',
                       @priority = @Priority,
                       @subject = 'You've got prblems',
                       @message = 'I found trade discrepancies',
                       @server = 'MailHost'


You could also then have different messages - one for each type of discrepancy found
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 19607680
>>How come you're using this EmailAsync proc?
Because I need each of these entries included in the body of the email:

 PRINT 'Suspect Orders have been identified:  Quantity
      http://blah/blah.php'

with the proc above, are you able to show me another way to do this?

0
 
LVL 17

Author Comment

by:dbaSQL
ID: 19607713
If @output = 1
   exec @rc = master.dbo.xp_smtp_sendmail
                       @from = 'me@me.com',
                       @from_name = 'Sanity Checker',
                       @to = 'whoever@fix-problems.com',
                       @priority = @Priority,
                       @subject = 'You've got prblems',
                       @message = 'I found trade discrepancies',
                       @server = 'MailHost'


see, in the proc above, i check different items.  if @output = 1, sending the mail with a 'you've got problems' email isn't enough.  i need to send a notice specific to the discrpancy found.
i.e, Invalid ID, Symbol, Time checker, etc.

I don't know how to run your proposed check on @output to each of the objects being checked, as opposed to the entire proc

do you know what I'm saying?  
so, like w/what i'm doing now, in the procedure after each object is checked, i set @output = 1 only if @@rowcount > 0

how would i handle your suggestion?
0
 
LVL 14

Expert Comment

by:twoboats
ID: 19607883
I guess from what you are saying, that somewhere in your existing code, the details are pulled out of the discrepancy table?

See, the problem is, I only have part of the picture...

Nowhere in EmailAsync does it reference DiscrepancyTable - so I don't know how it's getting the info you want to email.

And, how do rows end up in jobtable?
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 19608521
the insert into the discrepancy table is done in the checker proc that we've been working on.  it's up there, here's a piece here:

BEGIN  
  SET @output = 1
  PRINT 'Suspect Trades have been identified:  userid/acctid
      http://blah/blah.php' 

  INSERT INTO database.dbo.discrepancyTable (.........bunch of fields........)
  SELECT .........same bunch of fields........
  FROM database.dbo.orderTable t WITH (NOLOCK)
  WHERE tradetime BETWEEN @start AND @end
  AND NOT EXISTS (
  SELECT 1 FROM #userids WHERE userid = t.userid
  AND acctid = t.acctid )
END

and as you can see up there, IF EXISTS (certain conditions), the records are written to the discrepancy table

the jobTable is exactly that ... a job table.  it houses 15 or 20 records sitting behind 15 or 20 jobs of mine.  i set this up if/when i am configuring a job in which the results have to be sent out via email.  let me clarify -- i know all about attachments and reportingservices... but i am sending reports to some people who don't care for attachments.  show me the stats now, so to speak.  and, this is the only way i was able to get the procedure results into the body of the emails. ( http://www.sswug.org/see/15091)

so, i wrote the corresponding record into the job table at time of setup

this particular job changed -- 1st iteration i was sending the discrepancies out via email  
current iteration, i am sending a link thru which the recipients open/access the interface thru which they view and correct the discrepancies

that is the only thing i have to send now -- but i can't just do one for the entire procedure, i need to do them for the specific discrepancies:

'Suspect Orders have been identified:  Quantity
      http://blah/blah.php

'Suspect Orders have been identified:  IDs
      http://blah/blah.php

etc
now, in this particular case
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 19608861
>>now, in this particular case
not sure where i was going there, please disregard that last line, twoboats
0
 
LVL 14

Expert Comment

by:twoboats
ID: 19609997
Bear with me.

I see. So the jobtable is static.

I know where the discrepancy insert is :)

How does the email end up with details of the discrepancy in it? EmailAsync Doesn appear to read the discrepancy table.

0
 
LVL 17

Author Comment

by:dbaSQL
ID: 19610850
i'm sorry, twoboats, major outage issue here.  the SendAsyncEmail proc doesn't need to read the discrepancy table, per say, it only needs to handle the results of the varying stored procedures being used to run my varying jobs.  the results = the output of the procs

remember, i said this proc used to include the disrepancies in the body of the email, but now i just include reference to them, and a pointer to the interface thru which they'll resolve the discrpancies, like this;

'Suspect Orders have been identified:  Quantity
      http://blah/blah.php'


see here:
One thing that xp_smtp_sendmail doesnt do is execute a query. It has the ability to include a file attachment, even multiple attachments, or it can include the contents of a file up to 64K in size in the body of the mail. Thats half way there but I still needed to get the query results to a file so I can let the extended stored procedure either attach or embed them in an email as appropriate. One truth about human nature motivates me to make this work: Its much more likely that the recipient will read the body of an email than open an attachment. It is worthwhile to briefly cover how I did this because it highlights the requirements when the subsystem needs some file server storage space where it can generate result files.

The SendAsyncEmail procedure uses a trusted osql call via xp_cmdshell to move the results of a query to a text file. Once the file is created the undocumented xp_getfiledetails extended stored procedure is used to determine the size of the result file  only if @attach_results is not set to true. If the xp_getfiledetails indicate that the file is less than 64K when combined with any @message text specified it will embed the results in the email body. If that size is more than 64K the results are always attached. I find xp_getfiledetails useful in many situations, so I'll show you a chopped down example of how xp_getfiledetails can be used to get information about a query result file. You can use this extended stored procedure to get the same details for any file that the SQL Server can access.

0
 
LVL 17

Author Comment

by:dbaSQL
ID: 19707530
hi again, twoboats.  i've been out and in, buried under several projects.  this one is still very much an issue.  at an absolute minimum, i am still writing the dupe record to the discrepancy table  

>>and i am writing dupe records to the discrepancy table.  for example, one record is found to be suspect... maybe it's got a bad symbol... it is written to discrepancy table initially, an email alert is sent, and then maybe it isn't resolved quickly, it is written to the discrepancy table again.  oddly enough, the email alert isn't sent again and again, the record is only written to the discrepancy table again and again, until resolution.

any ideas?
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 19784505
unfortunately, twoboats, the problem still persists.  at an absolute min, i am writing dupe discrepancies into the table again and again, regardless of my efforts to correct

i realize this thing has been open far too long, though, so i am inclined to award, thanking you for you input thus far, and possibly i open another inquiry.  i understand that isn't really desired, though.  you know... awarding w/out an answer, or opening a new inquiry of the same

please let me know if you have any input, otherwise i will go ahead and award and close
thank you in advance, and for all of your assistance thus far.  it really is appreciated.
0
 
LVL 14

Expert Comment

by:twoboats
ID: 19789504
Without having all the code set up and running to see what's happening, it's become difficult to follow.

We seem to have a solution to the original bit - to get the stored proc to return 1 when either new discrepancies are found, or if discrepancies exist that haven't been handled.

Why the email isn't coming out isn't clear (yet).
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.
Suggested Courses

872 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