August 7, 2013 at 1:57 pm
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
August 7, 2013 at 2:43 pm
Damn overflows -_ changed from smallint to int to resolve the issue (although I then had to update the oracle table too).
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy