Update Statement fails

Posted on 2008-06-26
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
            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)
            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!
Question by:bamapie
  • 4
  • 3
LVL 21

Expert Comment

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?

Author Comment

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.
LVL 21

Expert Comment

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).
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud


Author Comment

ID: 21877082
Do I run sql profiler, and then somehow monitor this trigger?  And wait for it to fire?
LVL 21

Accepted Solution

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.
LVL 21

Expert Comment

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.
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.

Author Comment

ID: 21899223

>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.

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
I have a large data set and a SSIS package. How can I load this file in multi threading?
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.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

831 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