HELP! Incomplete data in SQL Server table.

[Sriram]

Hi SQL Server Experts,

I really need some help ASAP.I thank you in advance.

We have an ASP/MS SQL Server Shopping cart application,dev eloped by an
independent developer.We really have a problem where the Order_details
are not getting stored completely.

The concerned Tables are:

Table1 Order_Info
Stores Order_ID,custom er data,Total_Amou nt,Shipping details etc.
(One Record per Order.)

Table2 Order_details
Stores Order_ID,Produc t_Id,Price and Quantity.
(Multiple Records per Order.Basically stores the shopping cart items,
one by one.)

Table3 Payment_Info
Stores Order_ID and Transaction Details
(One Record per Order.)

Now after a customer checks out and the payment transaction has been
approved the order details are stored this way.

Step 1: First a new record is added to Table1 (Order_Info).

Step 2: A 'For' loop adds new records - one for each item in the cart-
to Table2(Order_de tails).

Step 3: A new record is added to the Table3 (Payment_Info).

Recently, we have been having problems with Step 2, where only a
partial list of the item details are stored in the Database and Step 3
is not executed at all.

And we don't have the problem all the time.

What could be the problem with Step 2 where a series of record are
added to a table continuosly? does this make SQL Server "too busy" so
that subsequent Add operations are not done?
Can someone see an apparent problem?

Any solution is greatly appeciated.

Please HELP ME!

Thanks.

Sriram

[alr]

Hi Sriram

If the Step 2 and 3 are really executed but no records are stored in the
database
there should be some error codes/messages returned by sql server.
You have to modify your application to log those errors so that you can
check them.

If you can't modify your application, you can use the SQL Profiler to make a
trace so
that you can try to insert the records manually with the SQL Analyzer (cut
and paste sql text).
By doing that you may reproduce the error and see the messages displayed by
sql.

Possible errors are:
database is not auto expand
log is full
duplicate keys (application bug)
etc...

By the way, you can also have a look at the sql server log with
Enterprise Manager.

Regards

Alain

"Sriram" <sr************ **@gmail.com> a écrit dans le message de
news:67******** *************** ***@posting.goo gle.com...

Hi SQL Server Experts,

I really need some help ASAP.I thank you in advance.

We have an ASP/MS SQL Server Shopping cart application,dev eloped by an
independent developer.We really have a problem where the Order_details
are not getting stored completely.

The concerned Tables are:

Table1 Order_Info
Stores Order_ID,custom er data,Total_Amou nt,Shipping details etc.
(One Record per Order.)

Table2 Order_details
Stores Order_ID,Produc t_Id,Price and Quantity.
(Multiple Records per Order.Basically stores the shopping cart items,
one by one.)

Table3 Payment_Info
Stores Order_ID and Transaction Details
(One Record per Order.)

Now after a customer checks out and the payment transaction has been
approved the order details are stored this way.

Step 1: First a new record is added to Table1 (Order_Info).

Step 2: A 'For' loop adds new records - one for each item in the cart-
to Table2(Order_de tails).

Step 3: A new record is added to the Table3 (Payment_Info).

Recently, we have been having problems with Step 2, where only a
partial list of the item details are stored in the Database and Step 3
is not executed at all.

And we don't have the problem all the time.

What could be the problem with Step 2 where a series of record are
added to a table continuosly? does this make SQL Server "too busy" so
that subsequent Add operations are not done?
Can someone see an apparent problem?

Any solution is greatly appeciated.

Please HELP ME!

Thanks.

Sriram

[Erland Sommarskog]

Sriram (sr************ **@gmail.com) writes:

Recently, we have been having problems with Step 2, where only a
partial list of the item details are stored in the Database and Step 3
is not executed at all.

And we don't have the problem all the time.

What could be the problem with Step 2 where a series of record are
added to a table continuosly? does this make SQL Server "too busy" so
that subsequent Add operations are not done?
Can someone see an apparent problem?

Forget about SQL Server being "too busy". This is an application error,
that I can tell from this little information. What the error is, I can
of course not tell. But likely reasons:

1) The loop is faulty and simply fails to include some rows.

2) Some of the order_details rows fails to save because of constraint
violations, and the application does not have proper error handling
to detect the error and rollback the transaction. (Or it does not
even start a transaction.)

As Alain suggested, use the Profiler to see what is being sent to SQL
Server. You can also trace for errors. Note that if you trace for both
StmtStarting and StmtCompleted and don't see Completed for a statement,
it may be because it failed.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Sriram Santhanam]

Thanks alain and Erland for replying.

The only problem is,

The application works everytime I test it.Only when someone is placing
an order from other geographical areas problem occurs.Even, when I place
the exact same order again, it goes through without any problem. All
values are stored correctly.

I cannot find out why or when this problem occurs.That is why I am
wondering if it is something else other than an application error.

Further, duplicate keys are allowed for this Table.

I'm currently rewriting the application.Any suggestion is greatly
appreciated.

Thanks.

Sriram

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Erland Sommarskog]

Sriram Santhanam (sr************ **@gmail.com) writes:

The only problem is,

The application works everytime I test it.Only when someone is placing
an order from other geographical areas problem occurs.Even, when I place
the exact same order again, it goes through without any problem. All
values are stored correctly.

I cannot find out why or when this problem occurs.That is why I am
wondering if it is something else other than an application error.
It could be deadlocks that are not correctly handled. Do you have
deadlock tracing enabled? If not, use Enterprise Manager to add
-T1204 and -T3605 to the startup parameters, and restart SQL Server
(assuming that you can afford a restart). If you can correlated the
deadlock with the incomplete orders, you have a lead.)

Of course, it could be a problem that is related to the web server only,
in which case I have no clues at all.
Further, duplicate keys are allowed for this Table.

Eh? That does not sound good.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Sriram Santhanam]

Thanks Erland,

I would enable Deadlock Tracing and check it out.

As you had mentioned that duplicate keys are bad(I think so too but
someone had done the DB design long time back)
is there a better way to store the item details in the order
(Product_Id,Pri ce,Qty at the minimum) than the current scenario I had
described above - Table2 where each record consists of
Order_Id,Produc t_Id,Price,Qty and whenever Item details are required a
query of all records by the Order_id is done.

Thanks.
Sriram


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Erland Sommarskog]

Sriram Santhanam (sr************ **@gmail.com) writes:

As you had mentioned that duplicate keys are bad(I think so too but
someone had done the DB design long time back)
is there a better way to store the item details in the order
(Product_Id,Pri ce,Qty at the minimum) than the current scenario I had
described above - Table2 where each record consists of
Order_Id,Produc t_Id,Price,Qty and whenever Item details are required a
query of all records by the Order_id is done.

The obvious natural key is (Order_id, Product_id), but that presumes that
a product cannot be ordered at two prices. For instance, because there is
a limited offer, and the customer wants more. This situation could be
handled with promotion codes.

A more common way is probably to add a rowno within the Order. If this
rowno is computed in the client, there is a protection of entering the
same row twice.

In general it is difficult to be precise with detailed knowledge of the
business rules.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp