Solved

IF EXISTS (EXEC..... )

Posted on 2006-11-03
42
868 Views
Last Modified: 2013-11-18
I have a proc which checks trade data for suspect/invalid data; like <0 prices, invalid symbols, <0 quantities...things of this nature.
now, if any suspect data is found, the proc returns it like this:

>>>>>>>
Suspect Trades    - UserIDs:
6 identifying fields of the trades found with suspect userids....
 
Suspect Trades   - Symbols:
6 identifying fields of the trades found w/suspect symbols....

Suspect Trades   - Quantity:
6 identifying fields of the trades found w/suspect quantities...
>>>>>>>>>>>>

See...if suspect data is found, it is returned for the user to review.  If not, nothing is returnred.  There are 7 items checked at each invocation.  If any of the seven are found suspect, an email is sent.

All this is fine right now, i created the proc, then created suspect data, they're detected and the notice is sent just fine.  This is done on a scheduled basis, every 5 minutes, everything works just fine.  BUT, I just now deployed it to the prod box, ran it, and found it to come back clean, as I had forgotten to make some suspect data, so everything was good, and i got a blank email

If nothing is found suspect, I'd really like to refrain from sending an email.  So, I thought I'd do it like this:  

IF EXISTS (exec proc....)
begin
   update a flag value which ensures the email is sent IF it is updated to a certain value
end
end

but, apparently i can't do that with the EXEC, or my syntax is just bad.  

Server: Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'EXEC'.
Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near ')'.

i really don't want to send blank emails...these are alerts....they should only be sent if/when a value is found to be suspect
can anybody provide some insight?  please




0
Comment
Question by:dbaSQL
  • 28
  • 10
  • 4
42 Comments
 
LVL 17

Author Comment

by:dbaSQL
ID: 17868135
what if i first create a temp table, insert into that table the results from the proc, and if exists on that table.  if results are there, i update my flag for the mail.  what do you think about that?  is there an easier/better way to do this?
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17868138
IF EXISTS (exec proc....)
 the above is not permissible, you can do either of these
1. CREATE a temp table with the exact structure the sp returns and insert the o/p of the sp in this
    INSERT INTO #temp
    EXEC proc
 
    IF EXISTS (SELECT 1FROM #temp )

2. change the sp to a table valued function  and use it
  IF EXISTS (SELECT 1 FROM dbo.functionName)
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 17868150
hi aneesh.  looks like we were both on the same page, same time (item 1)
i'm going to try that.  can you explain #2 to me a bit?
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17868176
you can change your procedure to a function wich returns a table, so insteatd of the 1st option, you can do this

CREATE FUNCTION fn_functionname()
RETURNS @t table (col1 int, col2 int ... )
AS
BEGIN
        --- code
END
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 17868269
i'm sorry, aneesh, i'm not sure what you mean.  i've got a great big proc...how do i change this to a function, and then call it like option 1 ?
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 17868322
and, the proc takes about 13 seconds to return, this if exists into a tmp table is still running at 56 sec??
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17868532
dbaSQL,


SET ROWCOUNT 1
insert into #Temp
EXEC sps
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 17868810
no, still the same t hing, aneesh.  the proc is quite fast.  this thing is still running.  my syntax, maybe?  here's the whole piece - that part at the top is just something i run to ensure it is only ran on a business day -

SET DATEFIRST 7
IF DATEPART(WEEKDAY, GETDATE()) BETWEEN 2 AND 6
AND NOT EXISTS (SELECT 1 FROM database.dbo.calendar WHERE dt = CONVERT(CHAR(8), GETDATE(), 112) AND isholiday = 1)
BEGIN

create table #a (bunch of fields...)

SET ROWCOUNT 1
insert into #a exec dba_stat.dbo.usp_procname

if exists (select * from #a)
begin
      update dba_stat.dbo.table SET sentflag = 0 WHERE subject = 'this particular subject'
end
drop table #a
END
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17868825
SET DATEFIRST 7
IF DATEPART(WEEKDAY, GETDATE()) BETWEEN 2 AND 6
AND NOT EXISTS (SELECT 1 FROM database.dbo.calendar WHERE dt = CONVERT(CHAR(8), GETDATE(), 112) AND isholiday = 1)
BEGIN

create table #a (bunch of fields...)

SET ROWCOUNT 1
insert into #a exec dba_stat.dbo.usp_procname
SET ROWCOUNT 0 --- U need to insert this line too
if exists (select * from #a)
begin
     update dba_stat.dbo.table SET sentflag = 0 WHERE subject = 'this particular subject'
end
drop table #a
END
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 17868962
it's still terrible, aneesh.  i can't figure out what's doing this.  it ran 4 minutes, i cancelled, it did that for another 4 minutes.
again, the proc is nearly immediate compared to this
possibly the function?  but i'm not sure how to do that the best way
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 17869013
and there are a few tmp tables in my proc

attempt to create the funciton fails with msg 2772
cannot access temporary tables from within a function
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 17869474
ok, dismiss the function, aneesh, can you help me w/the temp table proc?
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 17869810
well, aneesh, i thought i got it w/this:

SET DATEFIRST 7
IF DATEPART(WEEKDAY, GETDATE()) BETWEEN 2 AND 6
AND NOT EXISTS (SELECT 1 FROM database.dbo.calendar WHERE dt = CONVERT(CHAR(8), GETDATE(), 112) AND isholiday = 1)
BEGIN

declare @spcount int
exec @spcount = dbo.procname

if @spcount >0
begin
      update table SET sentflag = 0 WHERE subject = 'this particular subject'
END
END

it came back faster - about 30 seconds - but it's not updating that flag to 0

do you have any ideas?
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 17870251
rafrancisco put this out there once:  

>>>>>You can put a condition before sending the email to check if there are records in the table:

IF EXISTS (SELECT * FROM db1.dbo.OP_STATUS6_STUCK)
    EXEC master..xp_sendmail @recipients = 'me',
            @query = 'SELECT * FROM db1.dbo.OP_STATUS6_STUCK ;',
            @subject = 'Customer Service - Message1',
            @message = 'Attached file is a list of Problem Orders',
            @attach_results = 'TRUE',
            @width = 500

how can i do the same thing?  this is exactly what i'm trying to do -- only send the email if there are results
or, specifically, only perform that update if the procedure has results
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 17872810
>>IF EXISTS (exec proc....)
>>begin
>>   update a flag value which ensures the email is sent IF it is updated to a certain value
>>end
>>end

many do this update inside the proc. why checking the proc and then updating.
cutsomize the procedure to include this update statement.

0
 
LVL 17

Author Comment

by:dbaSQL
ID: 17877631
imran, thank you, but i'm just not sure i follow you.
and i'm just trying to check to see if the proc has results, if it does, then update the flag.  you know what i mean?  i'd be doing this inside an agent job.  if that flag is updated, a mail is sent.   if not, the job completes and no mail is sent, as is desired
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 17879300
hi dbaSql,
Should have any output parameter inside the procedure and do someting like this

like

Create YourProc @input datatype, @output bit output
begin
--your code
if count(*) >1 then set @output = 1
return
end


Declare @output datatype
Exec Yourproc 'inputvalue',@output output
IF @output =1
begin
  update a flag value which ensures the email is sent IF it is updated to a certain value
end
>>end
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 17880258
imran, again, thank you.  but could you walk me thru this a bit more?  
my procedure has no input, it's simply statistical analysis.  so,

create my proc @output bit output
..bunch of code
if count(*) >1 then set @output = 1
return
end

run my proc, am i only getting the output, what of the results, if/when there are any?

the second chunk, i believe i get.  this will be simply the driver that updates the flag, or not
i'm going to try the first piece, but i'm unsure
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 17880279
>>run my proc, am i only getting the output, what of the results, if/when there are any?

post your procedure my be i can change it but it will return only output which you have to set something like the below.

create my proc @output bit output
you code what ever you are doing then you have to see if there are records
if count(*) >1 then
set @output = 1
return
end
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 17880583
oh, imran, it's a big one.  i've genericized, where necessary.  please do let me know what you think.  i am up against the wall on this one now, badly, i really do appreciate your input.

as you can see herein, there are a handful of attributes we are concerened about realtime.  if/when anything is found to be suspect, an alert must be sent.  i plan to schedule this...as soon as i can avoid sending an email if/when there is no suspect data.  ( if you see anything sreamingly-bad, please point it out )

CREATE proc dbo.procedurename
AS

SET NOCOUNT ON

--USERID/ACCTID CHECKER
create table #USERIDs (USERID varchar(20),acctid varchar(20))
insert #USERIDs select distinct USERID,acctid
from database.dbo.USERIDs
where active = 'y'

IF EXISTS (
SELECT DISTINCT t.USERID,t.acctid
FROM database.dbo.table t WITH (NOLOCK)
WHERE tradetime BETWEEN left(getdate()-0, 11) AND dateadd(mi,-1,getdate())
AND NOT EXISTS (
SELECT 1 FROM #USERIDs WHERE USERID = t.USERID
AND acctid = t.acctid
) )
BEGIN  
      PRINT 'Suspect Fills   - USERID/acctids:'
      SELECT USERID,AcctID,EndPoint,Symbol,[B/S],Quantity,Price,tradetime
      FROM database.dbo.table t WITH (NOLOCK)
      WHERE tradetime BETWEEN left(getdate()-0, 11) AND dateadd(mi,-1,getdate())
      AND NOT EXISTS (
      SELECT 1 FROM #USERIDs WHERE USERID = t.USERID
      AND acctid = t.acctid )
      OPTION (MAXDOP 1)
END

--SYMBOL CHECKER
create table #symbolsA (symbol varchar(8))
insert #symbolsA select '?'
union
select ''
union
select 'UNKNOWN'

IF EXISTS (
SELECT DISTINCT symbol
FROM database.dbo.table T WITH (NOLOCK)
WHERE tradetime BETWEEN left(getdate()-0, 11) AND dateadd(mi,-1,getdate())
AND EXISTS (
SELECT 1 FROM #symbolsA
WHERE symbol = t.symbol)
)
BEGIN
      PRINT 'Suspect Fills   - Symbols:'
      SELECT USERID,acctid,EndPoint,Symbol,[B/S],Quantity,Price,tradetime
      FROM database.dbo.table t WITH (NOLOCK)
      WHERE tradetime BETWEEN left(getdate()-0, 11) AND dateadd(mi,-1,getdate())
      AND EXISTS (
      SELECT 1 FROM #symbolsA WHERE symbol = t.symbol)
      OPTION (MAXDOP 1)
END

--SIDE CHECKER
create table #side (side char(1))
insert #side select 'B'
union
select 'S'

IF EXISTS (
SELECT DISTINCT [B/S]
FROM database.dbo.table T WITH (NOLOCK)
WHERE tradetime BETWEEN left(getdate()-0, 11) AND dateadd(mi,-1,getdate())
AND NOT EXISTS (
SELECT 1 FROM #side
WHERE side = t.side)
)
BEGIN
      PRINT 'Suspect fills   - side:'
      SELECT USERID,acctid,EndPoint,Symbol,[B/S],Quantity,Price,tradetime
      FROM database.dbo.table t WITH (NOLOCK)
      WHERE tradetime BETWEEN left(getdate()-0, 11) AND dateadd(mi,-1,getdate())
      AND NOT EXISTS (
      SELECT 1 FROM #side WHERE side = t.side)
      OPTION (MAXDOP 1)
END

--ENDPOINT CHECKER
create table #endpoints (endpoint varchar(8))
insert into #endpoints select distinct endpoint from servername.databasename.dbo.tablename

IF EXISTS (
SELECT DISTINCT t.EndPoint
FROM database.dbo.table t WITH (NOLOCK)
WHERE tradetime BETWEEN left(getdate()-0, 11) AND dateadd(mi,-1,getdate())
AND NOT EXISTS (
SELECT 1 FROM #endpoints WHERE endpoint = t.EndPoint) )
BEGIN
      PRINT 'Suspect fills   - EndPoint:'
      SELECT USERID,acctid,EndPoint,Symbol,[B/S],Quantity,Price,tradetime
      FROM database.dbo.table t WITH (NOLOCK)
      WHERE tradetime BETWEEN left(getdate()-0, 11) AND dateadd(mi,-1,getdate())
      AND NOT EXISTS (
      SELECT 1 FROM #endpoints WHERE endpoint = t.EndPoint )
      OPTION (MAXDOP 1)
END

--PRICE CHECKER
create table #symbolsB (symbol varchar(16))
insert into #symbolsB select distinct symbol from database.dbo.table where symbol like  '%[-:]%'

IF EXISTS ( SELECT * FROM database.dbo.table t WITH (NOLOCK)
WHERE tradetime BETWEEN left(getdate()-0, 11) AND dateadd(mi,-1,getdate())
AND price <=0
AND NOT EXISTS (
SELECT 1 FROM #symbolsB WHERE symbol = t.symbol)
)
BEGIN
      PRINT 'Suspect fills   - Price:'
      SELECT USERID,acctid,EndPoint,Symbol,[B/S],Quantity,Price,tradetime
      FROM database.dbo.table t WITH (NOLOCK)
      WHERE tradetime BETWEEN left(getdate()-0, 11) AND dateadd(mi,-1,getdate())
      AND price <=0
      AND NOT EXISTS (
      SELECT 1 FROM #symbolsB WHERE symbol = t.symbol)
      OPTION (MAXDOP 1)
END

--QUANTITY CHECKER
IF EXISTS ( SELECT * FROM database.dbo.table t WITH (NOLOCK)
WHERE tradetime BETWEEN left(getdate()-0, 11) AND dateadd(mi,-1,getdate())
AND quantity <=0
)
BEGIN
      PRINT 'Suspect fills   - Quantity:'
      SELECT USERID,acctid,EndPoint,Symbol,[B/S],Quantity,Price,tradetime
      FROM database.dbo.table t WITH (NOLOCK)
      WHERE tradetime BETWEEN left(getdate()-0, 11) AND dateadd(mi,-1,getdate())
      AND quantity <=0
      OPTION (MAXDOP 1)
END

--TIME CHECKER
IF EXISTS (
SELECT * FROM database.dbo.table t WITH (NOLOCK)
WHERE tradetime >= DATEADD(mi,61,getdate() ))
BEGIN
      PRINT 'Suspect fills   - Time:'
      SELECT USERID,acctid,EndPoint,Symbol,[B/S],Quantity,Price,tradetime
      FROM database.dbo.table t WITH (NOLOCK)
      WHERE tradetime >= DATEADD(mi,61,getdate())
      OPTION (MAXDOP 1)
END

DROP TABLE #USERIDs
DROP TABLE #symbolsA
DROP TABLE #side
DROP TABLE #endpoints
DROP TABLE #symbolsB

SET NOCOUNT OFF
GO
0
 
LVL 28

Accepted Solution

by:
imran_fast earned 500 total points
ID: 17881023
--Look for set @output


CREATE proc dbo.procedurename @output bit output
AS

SET NOCOUNT ON
SET @output = 0
--USERID/ACCTID CHECKER
create table #USERIDs (USERID varchar(20),acctid varchar(20))
insert  #USERIDs
select distinct USERID,acctid from [database].dbo.USERIDs where active = 'y'

IF EXISTS (
SELECT DISTINCT t.USERID,t.acctid
FROM [database].dbo.[table] t WITH (NOLOCK)
WHERE tradetime BETWEEN left(getdate()-0, 11) AND dateadd(mi,-1,getdate())
AND NOT EXISTS (
SELECT 1 FROM #USERIDs WHERE USERID = t.USERID
AND acctid = t.acctid
) )
BEGIN  
    SET @output = 1
     PRINT 'Suspect Fills   - USERID/acctids:'
     SELECT USERID,AcctID,EndPoint,Symbol,[B/S],Quantity,Price,tradetime
     FROM [database].dbo.[table] t WITH (NOLOCK)
     WHERE tradetime BETWEEN left(getdate()-0, 11) AND dateadd(mi,-1,getdate())
     AND NOT EXISTS (
     SELECT 1 FROM #USERIDs WHERE USERID = t.USERID
     AND acctid = t.acctid )
     OPTION (MAXDOP 1)
END

--SYMBOL CHECKER
create table #symbolsA (symbol varchar(8))
insert #symbolsA select '?'
union
select ''
union
select 'UNKNOWN'

IF EXISTS (
SELECT DISTINCT symbol
FROM [database].dbo.[table] T WITH (NOLOCK)
WHERE tradetime BETWEEN left(getdate()-0, 11) AND dateadd(mi,-1,getdate())
AND EXISTS (
SELECT 1 FROM #symbolsA
WHERE symbol = t.symbol)
)
BEGIN
    SET @output = 1
     PRINT 'Suspect Fills   - Symbols:'
     SELECT USERID,acctid,EndPoint,Symbol,[B/S],Quantity,Price,tradetime
     FROM [database].dbo.[table] t WITH (NOLOCK)
     WHERE tradetime BETWEEN left(getdate()-0, 11) AND dateadd(mi,-1,getdate())
     AND EXISTS (
     SELECT 1 FROM #symbolsA WHERE symbol = t.symbol)
     OPTION (MAXDOP 1)
END

--SIDE CHECKER
create table #side (side char(1))
insert #side select 'B'
union
select 'S'

IF EXISTS (
SELECT DISTINCT [B/S]
FROM [database].dbo.[table] T WITH (NOLOCK)
WHERE tradetime BETWEEN left(getdate()-0, 11) AND dateadd(mi,-1,getdate())
AND NOT EXISTS (
SELECT 1 FROM #side
WHERE side = t.side)
)
BEGIN
    SET @output = 1
     PRINT 'Suspect fills   - side:'
     SELECT USERID,acctid,EndPoint,Symbol,[B/S],Quantity,Price,tradetime
     FROM [database].dbo.[table] t WITH (NOLOCK)
     WHERE tradetime BETWEEN left(getdate()-0, 11) AND dateadd(mi,-1,getdate())
     AND NOT EXISTS (
     SELECT 1 FROM #side WHERE side = t.side)
     OPTION (MAXDOP 1)
END

--ENDPOINT CHECKER
create table #endpoints (endpoint varchar(8))
insert into #endpoints select distinct endpoint from servername.databasename.dbo.[table]name

IF EXISTS (
SELECT DISTINCT t.EndPoint
FROM [database].dbo.[table] t WITH (NOLOCK)
WHERE tradetime BETWEEN left(getdate()-0, 11) AND dateadd(mi,-1,getdate())
AND NOT EXISTS (
SELECT 1 FROM #endpoints WHERE endpoint = t.EndPoint) )
BEGIN
    SET @output = 1
     PRINT 'Suspect fills   - EndPoint:'
     SELECT USERID,acctid,EndPoint,Symbol,[B/S],Quantity,Price,tradetime
     FROM [database].dbo.[table] t WITH (NOLOCK)
     WHERE tradetime BETWEEN left(getdate()-0, 11) AND dateadd(mi,-1,getdate())
     AND NOT EXISTS (
     SELECT 1 FROM #endpoints WHERE endpoint = t.EndPoint )
     OPTION (MAXDOP 1)
END

--PRICE CHECKER
create table #symbolsB (symbol varchar(16))
insert into #symbolsB select distinct symbol from [database].dbo.[table] where symbol like  '%[-:]%'

IF EXISTS ( SELECT * FROM [database].dbo.[table] t WITH (NOLOCK)
WHERE tradetime BETWEEN left(getdate()-0, 11) AND dateadd(mi,-1,getdate())
AND price <=0
AND NOT EXISTS (
SELECT 1 FROM #symbolsB WHERE symbol = t.symbol)
)
BEGIN    
 SET @output = 1
     PRINT 'Suspect fills   - Price:'
     SELECT USERID,acctid,EndPoint,Symbol,[B/S],Quantity,Price,tradetime
     FROM [database].dbo.[table] t WITH (NOLOCK)
     WHERE tradetime BETWEEN left(getdate()-0, 11) AND dateadd(mi,-1,getdate())
     AND price <=0
     AND NOT EXISTS (
     SELECT 1 FROM #symbolsB WHERE symbol = t.symbol)
     OPTION (MAXDOP 1)
END

--QUANTITY CHECKER
IF EXISTS ( SELECT * FROM [database].dbo.[table] t WITH (NOLOCK)
WHERE tradetime BETWEEN left(getdate()-0, 11) AND dateadd(mi,-1,getdate())
AND quantity <=0
)
BEGIN
    SET @output = 1
     PRINT 'Suspect fills   - Quantity:'
     SELECT USERID,acctid,EndPoint,Symbol,[B/S],Quantity,Price,tradetime
     FROM [database].dbo.[table] t WITH (NOLOCK)
     WHERE tradetime BETWEEN left(getdate()-0, 11) AND dateadd(mi,-1,getdate())
     AND quantity <=0
     OPTION (MAXDOP 1)
END

--TIME CHECKER
IF EXISTS (
SELECT * FROM [database].dbo.[table] t WITH (NOLOCK)
WHERE tradetime >= DATEADD(mi,61,getdate() ))
BEGIN    
SET @output = 1
     PRINT 'Suspect fills   - Time:'
     SELECT USERID,acctid,EndPoint,Symbol,[B/S],Quantity,Price,tradetime
     FROM [database].dbo.[table] t WITH (NOLOCK)
     WHERE tradetime >= DATEADD(mi,61,getdate())
     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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 17

Author Comment

by:dbaSQL
ID: 17881134
Server: Msg 147, Level 15, State 1, Procedure usp_SanityChk, Line 186
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.


tring to revise the proc
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 17881288
ok, got it, imran.  and it runs.  i need to create some invalids right now so i can test.
but can you walk me thru this - what's the right way to only update the flag if i've got output?

>>>>
SET DATEFIRST 7
IF DATEPART(WEEKDAY, GETDATE()) BETWEEN 2 AND 6
AND NOT EXISTS (SELECT 1 FROM database.dbo.calendar WHERE dt = CONVERT(CHAR(8), GETDATE(), 112) AND isholiday = 1)
BEGIN

declare @spcount int
exec @spcount = dbo.procname

if @spcount >0
begin
     update table SET sentflag = 0 WHERE subject = 'this particular subject'
END
END
>>>>>>>>>>>
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 17881381
it does exactly what my other proc did, only the output param is required.  so, exec procname 1 gives me the data.
what's the right way to call it herein:


SET DATEFIRST 7
IF DATEPART(WEEKDAY, GETDATE()) BETWEEN 2 AND 6
AND NOT EXISTS (SELECT 1 FROM database.dbo.calendar WHERE dt = CONVERT(CHAR(8), GETDATE(), 112) AND isholiday = 1)
BEGIN

declare @spcount int
exec @spcount = dbo.procname

if @spcount >0
begin
     update table SET sentflag = 0 WHERE subject = 'this particular subject'
END
END
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 17881547
no, same as before, it seems to run, except the data is returned and the value is not updated.
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 17881645
Server: Msg 7391, Level 16, State 1, Procedure procname, Line 15
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].


create table #a (...bunch of fields....)

insert into #a exec database.dbo.procname 1
if exists (select * from #a)
begin
     update database.dbo.table SET sentflag = 0 WHERE subject = 'this subject'
end
drop table #a
END

drop table #a
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 17881839
call it like this

declare @spcount int
exec  dbo.procname, @spcount output

if @spcount >0
begin
     update table SET sentflag = 0 WHERE subject = 'this particular subject'
END
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 17881844
sorry no need for the comma


call it like this

declare @spcount int
exec  dbo.procname @spcount output

if @spcount >0
begin
     update table SET sentflag = 0 WHERE subject = 'this particular subject'
END
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 17881872
as i said, it runs, the results are returned, the value is not updated
i really need to get this into place.  i just cannot see why the update isn't occurring
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 17882041
print @spcount and see what is the value you are getting for it.

declare @spcount bit

exec  dbo.procname @spcount output
print @spcount
if @spcount = 1
begin
     update table SET sentflag = 0 WHERE subject = 'this particular subject'
END
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 17882083
it has to be the way i'm calling it.  there is suspect data, the proc returns the results, but the value isn't updated.  so an email won't be sent.  do you see it?

SET DATEFIRST 7
IF DATEPART(WEEKDAY, GETDATE()) BETWEEN 2 AND 6
AND NOT EXISTS (SELECT 1 FROM db.dbo.calendar WHERE dt = CONVERT(CHAR(8), GETDATE(), 112) AND isholiday = 1)
BEGIN

declare @spcount int
exec db.dbo.procname @spcount output

if @spcount >0
begin
     update db.dbo.table SET sentflag = 0 WHERE subject = 'this subject'
END
END
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 17882134
where you are sending mail i don't see it  is see this update statement

>> update db.dbo.table SET sentflag = 0 WHERE subject = 'this subject'

but did you check
print @spcount what is it printing in the messege tab 0 or some value.
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 17882149
ok, i don't know how to voice this
-it is 2 before i run it
-it prints as 0
-i select again after it completes, it's 2
-the update is not run

select sentflag,subject from table where subject = 'this subject'

SET DATEFIRST 7
IF DATEPART(WEEKDAY, GETDATE()) BETWEEN 2 AND 6
AND NOT EXISTS (SELECT 1 FROM db.dbo.calendar WHERE dt = CONVERT(CHAR(8), GETDATE(), 112) AND isholiday = 1)
BEGIN

declare @spcount bit
exec db.dbo.procname @spcount output
print @spcount

if @spcount >0
begin
     update db.dbo.table SET sentflag = 0 WHERE subject = 'this subject'
END
END
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 17882173
i'm not sending it in the text i've placed thus far.  the statement i'm trying is step 1 of an agent job.  step two sends the email, if in fact the value has been updated to 0.  anything other than 0 will not be sent
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 17882218
if it prints as 0, there's my problem.  that's why there is no update.  what the heck am i missing here?
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 17882239


Man look at the print @spcount it is printing 0 if it prints 0 then it means the stroed procedure return 0 so if you check
below condition will fail and hence wont update

if @spcount >0
begin
     update db.dbo.table SET sentflag = 0 WHERE subject = 'this subject'
END
END


Did you change your db.dbo.procname as i suggested because currently if you see this @spcount 0 so check your stored procedure db.dbo.procname and modify it so that it will update the value of @spcount
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 17882280
yes, i believe i just found my error.  give me a second
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 17882341
oh good lord.  my bad, imran.  i am very sorry for dragging you through that oversight with me
i'm going to complete the test now....gen an email...i'll let you know shortly
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 17882581
yep.  it works.
my mistake was that i only put the beginning and ending set @ouptu in there.  which, of course, was setting the thing to 0
i didn't have the individual ones in there at each validation check

thank you, imran.  big time
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 17891890
imran, can i do the same thing with a procedure which also has parameters?  
i'm trying, but thus far it's not taking it syntactically
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 17895901
>>imran, can i do the same thing with a procedure which also has parameters?  

Yes

something like

Create Procecure procedurename @inputpar int, @outppar int
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 17897260
ok, yes, i tried/am trying that, imran.  a couple problems, but i think i'm getting there.  thank you much
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

What is Node.js? Node.js is a server side scripting language much like PHP or ASP but is used to implement the complete package of HTTP webserver and application framework. The difference is that Node.js’s execution engine is asynchronous and event…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

760 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now