November 3, 2016 at 12:43 pm
This is a problem that has totally escaped by ability to research or figure out.
I have a VB 2015 console application, driving a SQL Server 2012 Standard SP3, on a Dell server running Windows Server 2012 R2 Standard 64 bit, 2 Xeon processors, 128 gb RAM and 2 TB solid state drive.
I am loading records from our ERP via COGNOS. These records are customer records that are updated on a daily basis (new addresses, updated email/phone, etc.). They are read by their Modified Date - the last date they had a value changed on their customer record.
This problem has now occurred TWICE!
The first time was 2 weeks ago, doing a load test of data for the entire year of 2014. To get an idea of how long it would take and how the application performed. The job was running overnight.
My application has built in KPI tracking, to keep tabs on records coming in, how many are processed in each step of the normalization and to make sure everything runs as expect.
At 0115 AM, 19 Oct 2016, for about 2 or 3 minutes, zero records were loaded from COGNOS! For the date range of 1 July thru 6 Nov 2014, not a single record was loaded and processed.
The exact same thing happened this morning, 0115 AM, for about 2 minutes, zero records were loaded from COGNOS! In this case, it was from 1 March 2004 thru 5 May 2004. When it restarted, the time to load 600 records was over 5 minutes, a task that should only take 30 seconds. After that, all was fine. I am rerunning that range and it is running fine.
I have checked Windows event logs, SQL Logs, Windows Scheduler Logs... and nothing is shown to have occurred at 0115 AM. If the network had failed, I would have expected an error report with a null returned; if it was a connection problem, again, a null condition. It is almost like CONGOS is feeding my application a zero value.
I have my tech people looking into the COGNOS server (SQL Server 2012 or 2014, not sure which). But when I asked them about it 2 weeks ago, their log check indicated nothing was wrong.
Any insights will be greatly appreciated!!
November 3, 2016 at 1:26 pm
An update... I figured out why it is reporting zero records - it's the way I am counting the load.
If no records are present, it will kick back a zero, but that doesn't explain the lack of records in the first place!
November 3, 2016 at 1:47 pm
Just curious (and this is a serious question - not being a smart @ss here): what in all that stuff you told us, which was a LOT, gives us any ability at all to tell you what went wrong? Or even if anything was in fact wrong. We can't see nor do we have any information at all about the data on either side, no tables, triggers, constraints, files, data movement code or configurations, data load tracking application ... nothing.
You did say in an update that the files loaded had zero records in them. That would have been one of the first things I checked. But again, how are we to have any idea why there were no records in the files? Did you check the data to see if there should have been records? Did you check the program that created the files with zero records if there were supposed to be records in them?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 3, 2016 at 2:20 pm
Kevin,
Legit questions, and I feel remiss for not including them initially.
Yes, there is data present for my job to read. 400+ records existed for each day.
Right now, my COGNOS guy is checking event logs to see if something was running at the same time which would have caused this to fail.
November 3, 2016 at 2:32 pm
abbogus (11/3/2016)
Kevin,Yes, there is data present for my job to read. 400+ records existed for each day.
Right now, my COGNOS guy is checking event logs to see if something was running at the same time which would have caused this to fail.
That would have been the first thing to do after finding zero-record files. 🙂 Even better would be to get an email alert that a) a failure did occur with the extract or b) an (apparently never-expected) zero-record file was created. 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 3, 2016 at 3:04 pm
doing that... but zero records were expected in the older records (before web, stores not open on weekends).
But that was done by 2004 with the web.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply