Post reply

Update in Procedure doesn't work (but it does outside the SP)

  • August 7, 2013 at 1:57 pm

    #297386

    Hi guys,

    I came back to check on my new stats gathering but found an issue..all the steps of the stored procedure run except for an update. A summarised version of the procedure is below with the full update statement. When I look at the results (running every 15 minutes since 4pm) the columns that should have had a numerical value are all null. If I run it manually it sets them all to 0 (since at this point the entries are already in the main table).

    -- 1. cursor of linked servers

    -- 2. truncate dbo.PERF_COUNTERS_TEMP

    -- 3.0 <loop each linked server>

    -- 3.1 insert perfmon values into dbo.PERF_COUNTERS_TEMP

    -- 3.2 </loop>

    -- 4. insert perfmon values for this repository db into dbo.PERF_COUNTERS_TEMP

    -- 5. run the update below

    UPDATE dbo.PERF_COUNTERS_TEMP SET

    TRANSACTIONS_DIFF = (a.TRANSACTIONS_BASE - b.TRANSACTIONS_BASE),

    LOGINS_DIFF = (a.LOGINS_BASE - b.LOGINS_BASE),

    LAZYWRITES_DIFF = (a.LAZYWRITES_BASE-b.LAZYWRITES_BASE),

    LATCHES_DIFF = (a.LATCHES_BASE-b.LATCHES_BASE),

    FULLSCANS_DIFF = (a.FULLSCANS_BASE-b.FULLSCANS_BASE),

    RANGESCANS_DIFF = (a.RANGESCANS_BASE-b.RANGESCANS_BASE)

    FROM PERF_COUNTERS_TEMP a INNER JOIN (

    SELECT row_number() over (partition by instance, db_name order by coll_date desc) rnk,

    instance, db_name, COLL_DATE, TRANSACTIONS_BASE, LOGINS_BASE,

    LAZYWRITES_BASE, LATCHES_BASE,FULLSCANS_BASE,RANGESCANS_BASE

    from dbo.PERF_COUNTERS) b

    on a.INSTANCE = b.INSTANCE and a.DB_NAME = b.DB_NAME

    and b.rnk=1;

    -- 6. insert all data from dbo.PERF_COUNTERS_TEMP into dbo.PERF_COUNTERS

    -- 7. insert all data from dbo.PERF_COUNTERS_TEMP into an Oracle database

    Steps 6 & 7 work fine but the problem is it doesnt have the "_DIFF" values I was hoping for 🙁 Instead they remain null from the inserts inside the loop. Do I need some GO statements inside my SP to make the update work or something? Why does it work when i run it separate to the rest of the code? :s

    Dird

  • August 7, 2013 at 2:43 pm

    #1639561

    Damn overflows -_ changed from smallint to int to resolve the issue (although I then had to update the oracle table too).

    Dird

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply