0

I have to maintain FRP (Financial Resource Planning) Software that was implemented by some shady consultants (they came up with the jobs), and it stores the data in a SQL Server database. The software loads some info in the morning from a text file (from an ERP) and then 3 hours after it runs 4 jobs. If I run all these processes by hand, I get the same result at the first job. This is why I have isolated this one job.

I have checked for triggers and there are none. I have checked for procedures and all belong to stuff users can do in the software. Even with every user logged out (I took down the service, so no users would log in) it still happens.

Job number 1

UPDATE SOME_TABLE SET VALUE_DATE = DATEADD(dd, DATEDIFF(dd,0,GETDATE()),0)
WHERE VALUE_DATE < DATEADD(d,0,GETDATE()) AND FLAG BETWEEN '0' AND '1'

This baffles me because Flag can only take 3 values 0,1,2 and are numeric values.

DataSet prior to job

                +-------+----------+---------------+---------+
                |  ENT  |  AMOUNT  |   VALUE_DATE  |   FLAG  |
                +-------+----------+---------------+---------+
                |  AAA  |   10000  |   2018-05-22  |    0    |
                |  AAA  |   19999  |   2018-05-21  |    1    |
                |  BBB  |    1000  |   2017-12-21  |    2    |
                |  BBB  |    2000  |   2018-02-21  |    2    |
                |  BBB  |   10000  |   2018-05-15  |    0    |
                |  CCC  |   15000  |   2018-04-15  |    1    |
                +-------+----------+---------------+---------+

DataSet after the is job run (today's date is 2018-06-14)

                +-------+----------+---------------+---------+
                |  ENT  |  AMOUNT  |   VALUE_DATE  |   FLAG  |
                +-------+----------+---------------+---------+
                |  AAA  |   10000  |   2018-06-14  |    1    |
                |  AAA  |   19999  |   2018-06-14  |    1    |
                |  BBB  |    1000  |   2017-12-21  |    2    |
                |  BBB  |    2000  |   2018-02-21  |    2    |
                |  BBB  |   10000  |   2018-06-14  |    1    |
                |  CCC  |   15000  |   2018-06-14  |    1    |
                +-------+----------+---------------+---------+

Second job executed

UPDATE SOME_TABLE SET FLAG='0'
WHERE OTHER_FLAG IN ('COND1', 'COND2'...) AND FLAG='1'

Result of dataSet after second job - expected

                +-------+----------+---------------+---------+
                |  ENT  |  AMOUNT  |   VALUE_DATE  |   FLAG  |
                +-------+----------+---------------+---------+
                |  AAA  |   10000  |   2018-06-14  |    0    |
                |  AAA  |   19999  |   2018-06-14  |    1    |
                |  BBB  |    1000  |   2017-12-21  |    2    |
                |  BBB  |    2000  |   2018-02-21  |    2    |
                |  BBB  |   10000  |   2018-06-14  |    0    |
                |  CCC  |   15000  |   2018-06-14  |    1    |
                +-------+----------+---------------+---------+

This query doesn't only update the VALUE_DATE it also sets FLAG to 1 on every entry. Then the second job is in queue to set the FLAG value back to 0 where it updated. I find it convoluted and inefficient. I want to update it because it takes a great deal of time and people once a month to curate the information in the db.

Why is the FLAG column getting updated? Will changing FLAG BETWEEN '0' and '1' to FLAG in ('0','1') have any corrective effect? Or any adding "context" delimiter will help ("",'',(),[],{},etc)?

How can I make the first job only update the Value_date when flag is 0 or 1 and ditch the second job completely? This way I can get the expected table results

I'm sure it's not a software issue, because the date integrity holds just fine when doing stuff in the software. It only happens when this job is executed.

  • What's the data type for FLAG? – scsimon Jun 15 '18 at 18:58
  • @scsimon The type is smallint, I know you shouldn't use ' ' on int types, but that's the way the consultors wrote it. – dmb Jun 15 '18 at 19:16
  • Job 1 does not and cannot update the flag column unless there is a trigger. Or another job that is running sometime before or after job 1. Please show us the CREATE TABLE statement. – ypercubeᵀᴹ Jun 15 '18 at 19:39
  • @ypercubeᵀᴹ There are only 4 jobs. Job1, the one in question. Job2 wich corrects this odd behavior. Job3, that deletes stuff that has nothing to with 1. And Job4 that also deletes stuff. Every job runs 10mins from each other. If you stop every job, and run Job1 alone you get this behavior. I rechecked this db(Sql Server 2012) and the old db(Sql Server 2008, here I stopped eveything in db) and still happens. Both reports no triggers and the same behavior. – dmb Jun 15 '18 at 20:04
3

The SQL statement you've listed, run by itself, would not alter the FLAG column.

I've put together a SQLFiddle that shows what that statement does on its own, and you'll see that FLAG isn't changed by it.

This is why so many have assumed there's a trigger, or another statement, involved in the process.

You've said that there's no trigger on SOME_TABLE. If so, then some other statement is making the change.

The best way to find what that statement is would be to use SQL Profiler (or extended events) to track what's being executed when your job runs. If some other statement is being triggered by the relevant job, this should show you what that is. If you search this site, you'll find information on these options.

If you need to prove to yourself whether or not the UPDATE statement in and of itself is causing the change to FLAG, I'd recommend the following:

During a maintenance window:

  • Begin a transaction
  • Run the UPDATE statement inside that transaction
  • Check the results of the statement on SOME_TABLE (again from within the transaction)
  • Roll back the transaction.

If there's no transaction, and that SQL Statement (not the whole job, just that statement) does change the value of FLAG, then either this is tied to a bug in SQL Server (not impossible, but somewhat unlikely), or there's something seriously wrong with your SQL Server installation. Or, of course, there really is a trigger - I'm assuming not, based on what you've said, but that's a much more likely solution than a bug or a messed up SQL Server instance.

0

The catch here is BETWEEN which is the same as writing >= and <=.

BETWEEN returns TRUE if the value of test_expression is greater than or equal to the value of begin_expression and less than or equal to the value of end_expression.

So, for you code, everywhere that FLAG != 2. You can see this with a test.

declare @table table (flag tinyint)
insert into @table
values
(0),(1),(2)

select *
from @table
where flag between '0' and '1'

--translates to...

select *
from @table
where flag >= 0 
  and flag <= 1 

Using your data, you can see why the first row is updated for example...

declare @table table (ent char(3), amount int, value_date date, flag tinyint)
insert into @table
values
('AAA',10000,'2018-06-14',0)


select *
from @table
where value_date < DATEADD(d,0,GETDATE()) and flag between 0 and 1
  • Could it be that because is a smallint type the between evaluate 0 as the whole argument, and then thinks that and '1' is just part of the set statement? I'll try to get me a test enviroment for jobs(that server is populated with db's of other services) – dmb Jun 15 '18 at 19:35
  • I'm not sure what you mean... but the between statement is explicitly evaluating for any values >= 0 and <= 1 which is 0 and 1, or 0.nnnn~ - 1.00000~ (if it were a decimal) – scsimon Jun 15 '18 at 19:37
  • I meant as being a smallint you should pass the arguments like 0 and 1 in this case is '0' and '1', like a string. So my assumption is that because of the ' ' 0 is being handled as the whole condition for between, and the AND '1' is being asociated with the SET. This is just an asumption that I came with, because is the only way I can think of that will trigger this behavior. – dmb Jun 15 '18 at 19:45
  • No, it isn't being associated with the SET operation like you think. It's being evaluated in the WHERE. Also, SQL Server is converting those strings to INT implicitly, and no the quotes aren't needed. This is able to be done because of order of precedence. If you were to try '0.0' and '1.0' though, you'd get a conversion error where as you wouldn't with 0.0 and 1.0 – scsimon Jun 15 '18 at 19:50
  • @dmb is flag a computer column? Can you post the ddl? – scsimon Jun 15 '18 at 23:00
0

Have you considered that there may be other compiled binaries that the application uses to manage and maintain the data? If you have different outcomes when the app service is running, you may not be able to change the behavior of this job.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.