Solved

Update Statement fails

Posted on 2008-06-26
8
170 Views
Last Modified: 2010-03-19
This is a bit difficult to explain.  Basically, I have a 3rd-party product that acts as a "data-pump" between our manufacturing plant and my instance of SQL Server 2005.  Activities in the plant cause INSERTs to be executed in SQL.  Works great.

I have a trigger on one such table, which contains an UPDATE statement.  In a nutshell, that UPDATE looks like this:

      UPDATE dbo.LineSpeedSessions
         SET
            StopEventID = @EventID,
            StopTime = @Time,
            StopEventValue = @EventValue,
            StopEventCode = @EventCode,
            StopStatusCode = @StatusCode,
            DurationSeconds = DATEDIFF(ss, lss.StartTime, @Time)
         FROM dbo.LineSpeedSessions lss
         WHERE (
            (lss.StopEventID IS NULL OR lss.StopEventID = 0)
            AND
            lss.LineID = @LineID
            AND -- where it's the most recent event on this line, of this type, that's previous to this event
            SessionID = (SELECT MAX(SessionID) FROM dbo.LineSpeedSessions lss2 WHERE (lss2.LineID = @LineID AND lss2.StartEventCode = @EventCode AND lss2.StartTime < @DatapointTimestamp))
            AND -- where it's the same day
            [PFDC].[dbo].[fnGetDateTrunc](lss.StartTime) = [PFDC].[dbo].[fnGetDateTrunc](@DatapointTimestamp)
            AND -- where there are no shift breaks between the start and stop of this session
            NOT EXISTS (
               SELECT aot.ID FROM dbo.AnyOtherTable aot
            )
         )

In a nutshell:  the UPDATE fails when that last NOT EXISTS is included, and it succeeds when that NOT EXISTS is absent.

I need the NOT EXISTS there.  But is that illegal or something?  Bad syntax?  Seems like I couldn't alter the trigger if it were syntactically invalid.

Oh, I've also tried other tables in that NOT EXISTS.  Because originally, it was targeting a table in another database (PFDC), so I changed it to see if that was the problem.  Nope.  I can use other local tables, and very basic SELECTs inside that NOT EXISTS.  Same problem.

I'd put PRINT statements in here if I could see them after the fact.  I don't know if there's a way to do that in Sql Server though--have PRINT statement results written to a text file that's retrievable after the trigger is fired.

Points to the first answer that solves and explains.  Thank you!
0
Comment
Question by:bamapie
  • 4
  • 3
8 Comments
 
LVL 21

Expert Comment

by:mastoo
ID: 21876264
You don't say what the failure is.  You get an error message?  I'm guessing it just doesn't update because you want some criteria on that not exists.  That Select act.ID thing will always success as long as there are any records at all in AnyOtherTable, so Exists will always be true and Not Exists will always be false.  Is there an ID in LineSpeedSessions  that you want to check for?
0
 

Author Comment

by:bamapie
ID: 21876333
It just doesn't work.  That aforementioned data-pump just says, "it failed".  Really, it only increments a counter of the number of falied transactions.

>so Exists will always be true and Not Exists will always be false

Right.  And that should cause the UPDATE to not actually update anything, but it shouldn't cause it to actually fail the entire script.  I mean, there are other things this trigger does (ending up with an INSERT).  It's fine with me if the UPDATE affects zero rows.  But it failing--that I don't get.  I don't see why it would actually fail.

>Is there an ID in LineSpeedSessions  that you want to check for?

That SELECT inside the NOT EXISTS has been greatly simplified.  Originally it was doing a BETWEEN on some dates.  I simplified it down to this to just see if a very simple SELECT would succeed.
0
 
LVL 21

Expert Comment

by:mastoo
ID: 21876636
Ah.  So no real sql error and maybe the "it failed" is because the app detects something it doesn't like?  Can you run the sql profiler?  It can show you specific sql being executed along with any errors, or it can be used to capture the sql being executed and then you can replay the sql against a copy of the database (another method of seeing what goes wrong).
0
 

Author Comment

by:bamapie
ID: 21877082
Do I run sql profiler, and then somehow monitor this trigger?  And wait for it to fire?
0
Free Trending Threat Insights Every Day

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 21

Accepted Solution

by:
mastoo earned 500 total points
ID: 21877426
As a first cut I wasn't thinking you necessarily need to see the trigger itself execute.  It seems like the first question is, does the trigger cause some kind of sql server error or does it cause a condition that leads the app to consider it a failure?  You can use the standard profiler template, add the error events, and then watch for the inserts on the table.  If the trigger is causing a sql error you'll see it after the insert and it might point in the right direction.  But if the insert seems to work with no sql error then it would be the app just considers something to be wrong and I'm not sure where you would go on that on.
0
 
LVL 21

Expert Comment

by:mastoo
ID: 21877462
Although if you want to see the trigger execute, just add the profiler events under stored proc for statement starting and statement completed.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21879052
the syntax on what you showed is not bad.
however, I must assume that your actual syntax is wrong somehow.
please show the actual code.
0
 

Author Comment

by:bamapie
ID: 21899223
mastoo:

>It seems like the first question is, does the trigger cause some
>kind of sql server error or does it cause a condition that leads the app to consider it a failure?

>But if the insert seems to work with no sql error then it would be the app
>just considers something to be wrong

You know, I really think my "problem" isn't really a problem at all.  My updates are affecting rows (when applicable).  I just think the app is somehow detecting when a statement executed doesn't affect any rows, and saying "Hey it failed", when really that's not the case.

So, in my opinion, "False Alarm".  Thanks for your help.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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 UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

757 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

17 Experts available now in Live!

Get 1:1 Help Now