December 15, 2011 at 12:48 pm
SQL Server 2008 R2.
We have a 3rd party ERP system and occasionally records are not updated - we are wondering if it's a SQL 'hiccup' or something that is not being done in the code to insure the records are updated correctly.
1. Shipper Loads Trucks.
This process includes updating a Table that shows each of the Items in a "SHIPPING" bin location, which is a reserved bin location indicating the product is loaded on a truck. There are also Inventory Transactions table records showing the product was moved from whatever Warehouse Bin location it was in to the "SHIPPING" bin.
2. When Invoices are posted...
Inventory Transaction records are created, relieving the inventory (SHIPPING Bin). The SHIPPING Bin Location records are usually deleted.
* The processes are pretty much straightforward, there's not much the user can do to mess this up.
* I can view the transaction records indicating the activity. I can view the Invoice records, verifying that Invoice posting was performed.
However, as I said, occasionally the SHIPPING Bin records are not deleted. NOTE - SHIPPING Bin records for other Invoice records posted in the same batch are deleted OK.
Which raises the question - Why does this happen?
It's hard to believe that SQL Server has a flaw that occasionally this would happen because of something SQL Server Is/Is Not doing correctly. The other option is something in the code is not allowing SQL Server to perform as expected.
Anyone's thoughts?
[font="Verdana"]"The Road To Success Is Always Under Construction."[/font]
December 15, 2011 at 12:52 pm
does your database have any triggers?
if the trigger is not well written, it might rollback the action(in this case, a delete, right?) and the data would remain in place...that's the first place i'd look.
after that, i think you have to dig thru the details...maybe add a trace, and duplicate the action where the data's not being deleted,a nd go thru the details tosee if you can identify the flaw...whether the applicaiton is not deleting the data when you expect it to, or wether an error is occuring at some point.
Lowell
December 15, 2011 at 1:08 pm
The first question, of course, is do you have access to the code to fix anything if you do find the problem?
Some proprietary, third-party software, even when you find a flaw, fixing it would just void support contracts, et al. Assuming you can even get into the code in the first place.
Second, can you reproduce this behavior under controlled circumstances, or is it random/intermittent?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 15, 2011 at 1:32 pm
If you can reproduce it, maybe in a development environment, it would be useful to run a trace to see what is actually occurring.
December 15, 2011 at 2:02 pm
Actually the question is simply to understand whether SQL Server can be the culprit or I can contact the vendor and request a bug fix.I have no plans to dig into any code, even the sp's on the server. esides, Yes - it is proprietary, I don't have access. As I stated - I doubt that SQL Server is the likely problem and that the vendor needs to 'clean up the code'. BTW - Some other customers have posted the same problem.
Lowell - your reply first identified a poorly written trigger (code, not SQL).
Attempting to duplicate the problem will be difficult. There have only been 4 occurrences this year.
No, I don't have access to the code. It would be interesting to get my hands on it, because then I could step thru to see where the problem occurs.
I'm not holding any of you to this, but, given your replies, it sounds like we have legitimate reason to ask the vendor to fix the code. It's not likely that it's a SQL Server issue.
FYI - I am a Newbie with SQL Server, although we have had it since 2004. Think of me more like the owner of a car - I can drive it around, and for the most part have to do little maintenance. I don't do very much 'under the hood'. I once tried to set up a trigger and ended up bogging down performance and chewing up disk space. (Should have done it on a copy of the database). I studied sp's but not enough time spent practicing. My job is to develop Access reports to provide information the ERP system doesn't and respond to data requests. I can do queries, and think I'm getting pretty good at writing them from scratch rather than using Access to create the statement.
Thanks again for taking the time to help me out. I appreciate it.
Oh - Happy Holidays!
[font="Verdana"]"The Road To Success Is Always Under Construction."[/font]
December 15, 2011 at 2:04 pm
Definitely not an SQL Server issue. Code.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 16, 2011 at 10:33 am
I'd highly doubt it's a SQL issue, but there are several things that people do in SQL that could have unexpected consequences.
Using MERGE, for example, can have unexpected results given certain scenarios:
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply