Help!!! Strange Problem of SQL Server 7
-
Hi All,
I am using ASP to call a stored procedure. Here is the store procedure:
***************************************
CREATE PROCEDURE SalesSummary
AS
delete from SalesSummaryReport
--Insert Statement 1
Insert into SalesSummaryReport (officeid, SalesTotal)
select officeid,sum(Sales)
from Salesrecord_Prouct_A
group by by officeid
--Insert Statement 2
Insert into SalesSummaryReport (officeid,SalesTotal)
select officeid,sum(Sales)
from Salesrecord_Prouct_B
group by officeid
--Insert Statement 3
Insert into SalesSummaryReport (officeid, moneyReturnTotal)
select officeid,sum(moneyReturn)
from MoneyReturn_Product_A
group by officeid
--Insert Statement 4
Insert into SalesSummaryReport (officeid,moneyReturnTotal)
select officeid,sum(moneyReturn)
from MoneyReturn_Product_B
group by by officeid
select sum(SalesTotal) as 'SalesTotal', sum(moneyReturnTotal) as 'moneyReturnTotal'
from SalesSummaryReport
******************************************************
Most time it works fine. However, sometime there is a strange problem. When running this stored procedure it is supposed to deleted all data in table SalesSummaryReport, and re-insert data from the four insert statements. But sometime the delete statement only deletes the data that inserted by Insert Statement 1, all the rest cannot be deleted, and the four insert statement continue to insert data. So the last statement is keeping get wrong result because it add the undeleted data.
Does anybody see this before? What is the problem.
Thanks in advance,
Wilton
-
You could always change the delete statement to just truncate the table if you always want to start from an empty set.
Change
CREATE PROCEDURE SalesSummary
AS
delete from SalesSummaryReport
To
CREATE PROCEDURE SalesSummary
AS
truncate table SalesSummaryReport
It'd be quicker too (because it isn't being logged to the transaction log)
-
Thank you Journeyman.
I already change this stored procedure by using "truncate table SalesSummaryReport". I run it and didn't see any problem yet.
However, I still don't know why the delete statement I used doesn't work properly. Because this problem is only one of many problems. I just wonder if the SQL Server 7 or Database Development has problem.
The another problem is that I use ASP to run "select branch, branchID from branches", it is supposed to show all 28 rows. However, few times in a day, it only shows 13 rows. Actually, all select statement in ASP have the same problem at this moment. The cursor seems doesn't continue to move next. All of these problems happen randomly.
What could it be?
Thanks again,
Wilton
-
Wilton,
You should try placing your delete and insert operations in your sproc inside Begin and end statements:
Begin
Delete from aTable
End
Begin
Insert stuff
End
Good luck, Doug
-
Just curious -- can multiple users run this SP. Your SP looks like it uses a perm table "SalesSummaryReport". Could multiple users be loading the table at the same time.
If this is the only place that you need "SalesSummaryReport" them make it a temp table. That way only the user running the report is loading the data.
Curtis Smith
SQL Server DBA
Well in worked in Theory ... -
You shouldn't need to do a delete/insert to get these results, use a derived table with unions instead.
select officeID, sum(tSum) as TotalSum
from
(
select officeid,sum(Sales) as tSum
from Salesrecord_Prouct_A
group by officeid
union all
select officeid,sum(Sales)
from Salesrecord_Prouct_B
group by officeid
union all
select officeid,sum(moneyReturn)
from MoneyReturn_Product_A
group by officeid
union all
select officeid,sum(moneyReturn)
from MoneyReturn_Product_B
group by officeid
 
t---this is not a smiley face...it's a end-parangroup by TotalSum
Signature is NULL
-
I have the same kind of processing as you do in SQL 70 environment without problem.
Based on the code that you posted, I think this SP was called more than one place at the time that first call has not finished. System should bark if you have primary key constrains. If you change you SP to handle this, you may not see this strong problem.
Jie Ma -
Yes, using a temp table or union could be a good option. The SP probably can run better. However, this problem seems not the SP problem because the problem only happens few times a day. Most times it works fine. When the problem happens, there is also other symptom that select statement cannot get all data, it seems stuck in the middle. For example, if there are 30 row in a table, using select * from table, it only shows 13 rows. I really don't know what it is. It's so weird. Nobody sees this kind of problem before.
-
First : start you sp with set nocount on (unless you realy need the rows info)
Why aren't you using a transaction ?
begin tran
truncate table xxx
set @workerror = @@error
if @workerror = 0
begin
insert ...
set @workerror = @@error
end
if @workerror = 0
commit tran
else
rollback tran
end
Yes, you will lock and have others wait, but isn't that a prereq for having consistent refreshed data ?
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply