Solved

Update Statement fails

Posted on 2008-06-26
8
176 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Get Database Help Now w/ Support & Database Audit

Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.

 

Author Comment

by:bamapie
ID: 21877082
Do I run sql profiler, and then somehow monitor this trigger?  And wait for it to fire?
0
 
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 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.
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

623 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