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).
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.


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 143

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

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!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SSIS GUID Variable 2 30
Convert VBA UDF to SQl SERVER UDF 4 45
Checking for column changes SQL 2014 4 15
access to sql migration 5 19
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

679 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