Solved

Strange happenings in sql server

Posted on 2007-03-28
7
161 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
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.

 

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
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 shrink a transaction log file down to a reasonable size.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

726 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