Solved

Strange happenings in sql server

Posted on 2007-03-28
7
160 Views
Last Modified: 2010-03-19
I'm hoping someone can help me with a strange problem.

I have an inventory/billing management application I've created with Visual Basic Express using SQL Express.  

Everythig works fine but I have a strange glitch happening.  

On the target machine after a little while.  All invoices that have been added to different customers will all move to one customer.  Somehow sql is changing the CustomerID in all invoices.

I'm having trouble duplicating this behavior on the development machine I was hoping someone could maybe give me ideas of why this would happen.

Thanks.
0
Comment
Question by:Camyden
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 11

Expert Comment

by:Ved Prakash Agrawal
ID: 18809143
there is any trigger on that table ???????
0
 
LVL 11

Expert Comment

by:Ved Prakash Agrawal
ID: 18809173
Here is steps to resolve your problem

1. Run Profiler
2. and execute your command by application.
3. stop the profiler.
4. check what entires are in the table if not correct  look back to profiler and check what value passed by application....and the profiler can give you a hint whats is wrong there....
0
 
LVL 42

Accepted Solution

by:
dqmq earned 500 total points
ID: 18809319
Possibly an UPDATE with an indadequate WHERE clause.  One way to trace it down is with an after update trigger on the invoices table to trap that condition.

SELECT 1 FROM INSERTED INNER JOIN DELETED
ON INSERTED.InoviceID = DELETED.insertID
WHERE INSERTED.CustomerID <> DELETED.CustomerID
IF @@ROWCOUNT > 1
   BEGIN
   RAISERROR ('Mass update of customerID detected',15,1) WITH LOG
   ROLLBACK   --in case error is not trapped
   END
 
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:Camyden
ID: 18809453
1.The only trigger I have on Invoices Table is
ALTER TRIGGER trPaidInvoices
ON dbo.Invoices
FOR UPDATE
AS
      IF UPDATE (InvoiceTotal)
insert into ClosedInvoices (InvoiceID,CustomerID,[Date])
SELECT     InvoiceID, CustomerID, [Date]
FROM         Invoices
WHERE     (InvoiceTotal = 0.00)
Delete from invoices where invoiceTotal=0.00
select * from Invoices


2.Not sure how to run the profiler?
3. my invoices update statement is
UPDATE    Invoices
SET              CustomerID = @CustomerID, Date = @Date, InvoiceTotal = @InvoiceTotal, SubTotal = @SubTotal, Tax = @Tax
WHERE     (CustomerID = @CustomerID)
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 18810121
Totally unrelated, but the following line should not be in your trigger
select * from Invoices

You also may want to read up on Triggers.  You should only update the invoices that have changed not all the invoices in the whole table.  Specifically read up on the Inserted and Deleted logical tables in Triggers.


Also there is not much point in updating CustomerID with the same CustomerID, so I would re-write your Update statement as:
UPDATE    Invoices
SET              [Date] = @Date, InvoiceTotal = @InvoiceTotal, SubTotal = @SubTotal, Tax = @Tax
WHERE     CustomerID = @CustomerID
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 18810145
Again while the following is not the direct problem, you may want to read up on structured logic using IF statements.  I suspect that you meant to write this:

IF UPDATE (InvoiceTotal)
   Begin
          insert into ClosedInvoices (InvoiceID,CustomerID,[Date])
          SELECT     InvoiceID, CustomerID, [Date]
          FROM         Invoices
          WHERE     (InvoiceTotal = 0.00)

          Delete from invoices where invoiceTotal=0.00
   End

Also you should understand that IF UPDATE does NOT mean that InvoiceTotal has actually changed, merely that it was involved in an UPDATE statement.
0
 
LVL 42

Expert Comment

by:dqmq
ID: 18811942
I have other concerns with that trigger.  It can result in duplicate and/or lost closed invoice history unless the transaction is serialized. The problem occurs if the trigger fires mulitple times concurrently and the same invoice is inserted more than once before it is deleted or an InvoiceTotal changes to zero after the insert but before the delete and thus gets lost.

Carrying on from ACperkins, this would be better:

IF UPDATE (InvoiceTotal)
   Begin
          insert into ClosedInvoices (InvoiceID,CustomerID,[Date])
          SELECT     i.InvoiceID, i.CustomerID, i.[Date]
          FROM         Inserted i INNER JION deleted d on i.invoiceID = d.invoiceID
          WHERE     i.InvoiceTotal = 0 AND d.InvoiceTotal <> 0;

          Delete from invoices i where i.invoiceID in
            (select invoiceID from Inserted where invoiceTotal = 0);
   End

On second thought, there may still be some very-unlikely-to-happen holes in that logic. Beyond a doubt, the safest solution is to move the delete from that trigger to an AFTER INSERT trigger on the ClosedInvoice table.

--DQMiller



0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

789 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question