locked
SSIS Execute SQL Task doesn't always execute like ssms RRS feed

  • Question

  • Please compare the line before last:

     

    update CrIndex
    set BetaIg=(LOG(Cds5y)-LOG(CurrHyIx5y))/(LOG(CurrIgIx5y)-LOG(CurrHyIx5y)),
    BetaHy = 1 - (LOG(Cds5y)-LOG(CurrHyIx5y))/(LOG(CurrIgIx5y)-LOG(CurrHyIx5y)),
    IxDrvCdsPctChg5y =   (((LOG(Cds5y)-LOG(CurrHyIx5y))/(LOG(CurrIgIx5y)-LOG(CurrHyIx5y))) * CurrIgIx5yPctChg) + ((1 - (LOG(Cds5y)-LOG(CurrHyIx5y))/(LOG(CurrIgIx5y)-LOG(CurrHyIx5y))) * CurrHyIx5yPctChg),
    IxDrvPspPnl5y        = (IxDrvCdsPctChg5y* Psp)
    where CrIndex.Date=? and Cds5y <> 0

    works but

    update CrIndex
    set BetaIg=(LOG(Cds5y)-LOG(CurrHyIx5y))/(LOG(CurrIgIx5y)-LOG(CurrHyIx5y)),
    BetaHy = 1 - (LOG(Cds5y)-LOG(CurrHyIx5y))/(LOG(CurrIgIx5y)-LOG(CurrHyIx5y)),
    IxDrvCdsPctChg5y =   (((LOG(Cds5y)-LOG(CurrHyIx5y))/(LOG(CurrIgIx5y)-LOG(CurrHyIx5y))) * CurrIgIx5yPctChg) + ((1 - (LOG(Cds5y)-LOG(CurrHyIx5y))/(LOG(CurrIgIx5y)-LOG(CurrHyIx5y))) * CurrHyIx5yPctChg),
    IxDrvPspPnl5y        = ( (((LOG(Cds5y)-LOG(CurrHyIx5y))/(LOG(CurrIgIx5y)-LOG(CurrHyIx5y))) * CurrIgIx5yPctChg) + ((1 - (LOG(Cds5y)-LOG(CurrHyIx5y))/(LOG(CurrIgIx5y)-LOG(CurrHyIx5y))) * CurrHyIx5yPctChg)* Psp)
    where CrIndex.Date=? and Cds5y <> 0 

    doesn't?! I've learned not to use previous lines definitions in the subsequent lines.  In the past it gave me issues.  So I've defined every thing based on already existing values. But the way the last line works, is the opposite of this rule, and it works! I am confused. What works in ssms, doesn't always work in ssis. What is your experience?

     


    • Edited by caliskan Wednesday, February 1, 2012 8:50 PM
    Wednesday, February 1, 2012 8:48 PM

Answers

  • Your post is somewhat cryptic (and does not seem to have much relevance to security in SQL Server.)

    But if I understand this correctly, you have something like:

    UPDATE tbl
    SET    a = <something>,
           b = a * <something else>

    In SQL the principle is "all at once". This means that when you set b in the example above, it's based on the old value of a.

    CREATE TABLE tbl (a int NOT NULL,
                      b int NOT NULL)
    INSERT tbl(a, b) VALUES (9, 12)
    UPDATE tbl
    SET    a = 0,
           b = a* 10
    go
    SELECT a, b FROM tbl
    go
    DROP TABLE tbl

    This returns b as 90, not 0.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Maggie Luo Friday, February 24, 2012 2:03 AM
    Wednesday, February 1, 2012 11:00 PM

All replies

  • Neither works actually!
    Wednesday, February 1, 2012 9:03 PM
  • Your post is somewhat cryptic (and does not seem to have much relevance to security in SQL Server.)

    But if I understand this correctly, you have something like:

    UPDATE tbl
    SET    a = <something>,
           b = a * <something else>

    In SQL the principle is "all at once". This means that when you set b in the example above, it's based on the old value of a.

    CREATE TABLE tbl (a int NOT NULL,
                      b int NOT NULL)
    INSERT tbl(a, b) VALUES (9, 12)
    UPDATE tbl
    SET    a = 0,
           b = a* 10
    go
    SELECT a, b FROM tbl
    go
    DROP TABLE tbl

    This returns b as 90, not 0.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Maggie Luo Friday, February 24, 2012 2:03 AM
    Wednesday, February 1, 2012 11:00 PM
  • Thank you Erland. I apologies for posting this here. In haste and frustration, at the end of the day, I must have clicked on the wrong group.  Thank you for confirming my findings.  Unfortunately, i am not able to see the same behavior from sql server integration services.  Thanks and regards.
    Thursday, February 2, 2012 10:32 PM