October 17, 2014 at 7:02 am
I am facing quite abnormal issue with MS SQL Server 2008 R2 64-Bit we have a process which generates summary of sales and insert into another table 1st time it shows correct results next time you query it show something else and in the report it show correct figure in process result window it shows wrong result the anytime during the day it start showing correct result every where.
Can anyone help Please comment
October 17, 2014 at 7:04 am
No where near enough information.
Can you at least post the query, that may give a starting point. Also, what exactly do you mean by 'wrong result'? Wrong in what way?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 17, 2014 at 10:32 am
does your query use the NOLOCK hint?
Lowell
October 17, 2014 at 10:46 am
It is not a problem that your run some query once and you get answer A, later when you run you get B which is A + new sales. That is expected. If there are transactions to adjust previous sales like taking them out of one region and putting them in another, timing may be such that you run while it sales are zeroed out of the total and you get a lower figure, and when you run later they are added back into the next region so you get the same as you did originally. That can be confusing if you do not understand the nature and state of your data.
If the data is not changing and no transactions are going back into it to adjust historic sales then there is something else going on.
Others who posted are correct. To best undertand this we would need the text of the query to understand.
Not all gray hairs are Dinosaurs!
October 18, 2014 at 1:22 am
First of all thank you very much for a prompt reply to my query and here is the requested information:
I am using Power Builder as a Front End:
Process Select Statement is:
select
d.gst_type
, d.gst_type gst_typeo
, sum(sale_qty) qty
, sum(sale_discount) sale_discount
, sum(cexcl_stax) cgross
, sum(cstax) cstax
, sum(cincl_stax) inclusive_gross
, sum(cadstax) pur_tax
, sum(000000000.00) adjust_amount
, sum(000000000.00) margin_per
from stax_sale_det d
join stax_sale_mst pm on pm.trans_no=d.trans_no
where pm.mfgcom_code = :ra_mfgcomcode
and trans_date between :ra_trans_datef and :ra_trans_datet
and d.sale_type='UNR'
group by
d.gst_type
order by 1
Report 1 Select Statement is:
select
ntn_no = c.ntn_no
, cnic_no = c.cnic_no
, mfg_name = g.mfg_name
, cust_name = c.cust_name
, cust_type = (case when d.sale_type='LOC' then 'Registered' else 'Un-Registered' end)
, doc_type = 'SI'
, doc_no = pm.trans_no
, doc_date = right('0'+convert(varchar,day(pm.trans_date)),2) +'/'
+ right('0'+convert(varchar,month(pm.trans_date)),2) + '/'
+ right('0'+convert(varchar,year(pm.trans_date)),4)
, Hs_code = NULL
, sale_type = (case when (select count(*) from stp_cust_examption where cust_code=c.cust_code and pm.trans_date between start_date and end_date)>0 then 'E' else d.gst_type end)
, gst_rate = (d.gst_rate / 100)
, sale_qty = 0
, uom = NULL
, d.gst_type
, excl_stax = case when sum(isnull(free_qty,0))>0 then sum(cstax/case when d.gst_rate=0 then 1 else d.gst_rate/100 end) else sum(d.cexcl_stax) end
, stax = sum(cstax)
, sdis = sum(sale_discount)
, extra_tax = sum(cadstax)
, stax_held = NULL
from stax_sale_det d
join stax_sale_mst pm on pm.trans_no=d.trans_no
join stp_mfg g on (pm.mfg_code=g.mfg_code)
join stp_cust c on ( pm.cust_code = c.cust_code)
join stp_product p on ( d.prod_code = p.prod_code)
where pm.mfgcom_code = :ra_mfgcom_code
and ( isnull(:ra_cust_code,'')='' or pm.cust_code = :ra_cust_code)
and (pm.sale_type = :ra_sale_type or isnull(:ra_sale_type,'')='')
and trans_date between :ra_date_from and :ra_date_to
and ( p.gst_type = :ra_gst_type or isnull(:ra_gst_type,'')='')
and d.sale_qty>0
and g.mfgcom_code = :ra_mfgcom_code
group by c.ntn_no
, c.cnic_no
, c.cust_code
, c.cust_name
, c.gst_no
, d.gst_type
, pm.trans_no
, pm.trans_date
, d.gst_rate
, pm.mfg_code
, g.mfg_name
, d.sale_type
order by 4, 9
Report 2 Select Statement is:
select
inv_no = m.trans_no
, inv_date = m.trans_date
, cust_descr = c.cust_name + ' ' + c.addr1 + char(13) + isnull(c.gst_no,'')
, goods_descr = (case when (select count(*) from stp_cust_examption where cust_code=m.cust_code and m.trans_date between start_date and end_date)>0 then 'E' else d.gst_type end)
, qty = sum(d.sale_qty)
, gross_amt = sum(d.sale_qty * d.rate)
, discount = sum(sale_discount)
, gst_rate= d.gst_rate
, tot_stax = sum(cstax)
, incl_value = sum(cincl_stax - sale_discount)
, tot_ex_value = case when sum(isnull(free_qty,0))>0 then sum(cstax/case when d.gst_rate=0 then 1 else d.gst_rate/100 end) else sum(d.cexcl_stax) end
, sale_tax = sum(cstax)
, tot_adv_inc_tax_per = adv_inc_tax_per
, tot_adv_inc_tax_per_amt = sum(cadstax)
, company_name = dbo.f_decrypt(k.company_name)
, addr1 = k.addr1
, gst_no = convert(bigint,k.gst_no)
, ntn_no = k.ntn_no
, period_name = 'From ' + convert(varchar,:ra_date_from,103) +' To' + convert(varchar,:ra_date_to,103)
,d.gst_type
from stax_sale_mst m
join stax_sale_det d on ( m.trans_no = d.trans_no and m.company_code = d.company_code)
join stp_cust c on (m.cust_code = c.cust_code)
join company k on ( 1=1)
join stp_product p on (p.prod_code=d.prod_code)
where (1=1)
and (m.mfgcom_code=:ra_mfgcom_code or :ra_mfgcom_code = '')
and (m.mfg_code = :ra_mfg_code or isnull(:ra_mfg_code,'')='')
and (m.sale_type = :ra_sale_type or isnull(:ra_sale_type,'')='')
and (m.cust_code = :ra_cust_code or isnull(:ra_cust_code,'')='')
and trans_date between :ra_date_from and :ra_date_to
and sale_qty<>0
and (m.company_code = :gs_company_code)
group by m.trans_no
, m.trans_date
, m.cust_code
, c.cust_name
, c.addr1
, c.gst_no
, k.company_name
, k.addr1
, k.gst_no
, k.ntn_no
, d.gst_rate
, d.gst_type
, d.adv_inc_tax_per
order by 4
However, the issue is that on one PC this gives same results and other PC it give different results, then suddenly after 2 - 3 hours all start giving matching results. Secondly, as an other guy here on the forum commented that when sales change it is obvious that results will changed but in this case the data is static, we run a process which bulk insert data from production tables to these reporting tables. And the insert statement is as under:
delete from stax_sale_mst where mfgcom_code = :p_mfgcomcode and trans_date between :p_transdate and :p_transdate2 and sale_type='UNR';
insert into stax_sale_mst
(
trans_no
, company_code
, trans_date
, mfg_code
, cust_code
, sale_type
, status
, remarks
, mfgcom_code
)
select
(select right(convert(varchar,year(dt1)),2)
from am_next_key k2
where trans_date between k2.dt1 and k2.dt2
)
+
right('000000'+convert(varchar, isnull((select max(convert(bigint,right(trans_no,6))) from stax_sale_mst),0)+(row_number() over(order by trans_date))),6)
, company_code
, trans_date
, mfg_code
, cust_code
, sale_type
, status
, remarks
, :p_mfgcomcode
from
(
select distinct
company_code
, trans_date
, mfg_code = '101061'
, cust_code = '024577'
, sale_type = 'UNR'
, status
, remarks = 'Consolidated Invoice - Unregistered Sale Daily'
from sale_loc_mst
where mfg_code in(select mfg_code from stp_mfg where isnull(invoicing_per,'N')='Y' and mfgcom_code = :p_mfgcomcode)
and trans_date between :p_transdate and :p_transdate2
group by company_code
, trans_date
, mfg_code
, status
, remarks
) r;
if sqlca.sqlcode<>0 then
messagebox('Error', sqlca.sqlerrtext)
end if
insert into stax_sale_det
( trans_no
, company_code
, sale_type
, line_item
, gst_type
, prod_code
, sale_qty
, rate
, gst_rate
, ced_rate
, sale_discount
, ad_gst_rate
, is_rp
, rprate
, mfgcom_code
)
select (select top 1 trans_no from stax_sale_mst where trans_date=d.trans_date and sale_type = 'UNR' and cust_code='024577' and mfg_code = '101061' and mfgcom_code=:p_mfgcomcode)
, :gs_company_code
, sale_type = 'UNR'
, line_item= row_number() over(order by trans_date)
, gst_type = gst_type
, prod_code
, sale_qty = sale_qty
, rate = rate
, gst_rate = gst_rate
, ced_rate = 0
, isnull(sale_discount,0) + isnull(trade_amt,0)
, ad_gst_rate
, is_rp
, rprate
, :p_mfgcomcode
from
(
select
trans_date,
prod_code,
gst_type,
sum(sale_qty) sale_qty,
rate = dbo.f_prod_ratenewgst(m.trans_date, m.mfg_code, d.prod_code, 'S'),
gst_rate = dbo.f_prod_ratenewgst(m.trans_date, m.mfg_code, d.prod_code, 'G'),
ad_gst_rate = dbo.f_prod_ratenewgst(m.trans_date, m.mfg_code, d.prod_code, 'A'),
rprate = dbo.f_prod_ratenewgst(m.trans_date, m.mfg_code, d.prod_code, 'R'),
ced_rate,
sum(sale_discount) sale_discount,
sum(trade_amt) trade_amt,
d.is_rp
from sale_loc_mst m join sale_loc_det d on m.trans_no = d.trans_no
where m.trans_date between :p_transdate and :p_transdate2
and m.mfg_code in(select mfg_code from stp_mfg where isnull(invoicing_per,'N')='Y' and mfgcom_code = :p_mfgcomcode)
and ((gst_type='E' and gst_rate=0) or (gst_type<>'E' and gst_rate>0))
and isnull(gst_type,'')<>''
// New Change
and prod_code not in (select distinct prod_code from stax_sale_mst m join stax_sale_det d on m.trans_no=d.trans_no where m.trans_date between :p_transdate and :p_transdate2)
// New Change
group by trans_date, gst_type, prod_code, rate, gst_rate, ced_rate, d.ad_gst_rate, d.is_rp, d.rprate, m.mfg_code
) d
order by 1,prod_code ;
if sqlca.sqlcode=0 then
commit;
else
messagebox('Error', sqlca.sqlerrtext)
rollback;
end if
return sqlca.sqlcode
I hope this will explain the situation. Again in my opinion these queries are irrelevant because they are proven correct and the same query shows different result on different PC(s) shows different results if we run from source code mean from Power Builder IDE and different from complied EXE File. To me its a bug in SQL Server 2008 R2 may be we need some patch / fix to install and yes I thing is that it is installed on Windows 7 Ultimate 64-Bit Operating System and MS SQL Server 2008 R2 is also a 64-Bit version recently I also installed Service Pack 3 for SQL Server and Service Pack 1 for Windows 7.
Thanks Again.
Zafar
October 20, 2014 at 9:42 am
zafarahmad (10/18/2014)
First of all thank you very much for a prompt reply to my query and here is the requested information:I am using Power Builder as a Front End: .....
I hope this will explain the situation. Again in my opinion these queries are irrelevant because they are proven correct and the same query shows different result on different PC(s) shows different results if we run from source code mean from Power Builder IDE and different from complied EXE File. To me its a bug in SQL Server 2008 R2 may be we need some patch / fix to install and yes I thing is that it is installed on Windows 7 Ultimate 64-Bit Operating System and MS SQL Server 2008 R2 is also a 64-Bit version recently I also installed Service Pack 3 for SQL Server and Service Pack 1 for Windows 7.
Thanks Again.
Zafar
Zafar in troubleshooting often we look at what is the same and what is different. The things that appear to be the same in your situation are the SQL query, the SQL Server, and the data.
Assuming the data is identical is in fact an assumption on my part. If the data is fully committed and there is no delete etc then it is the same for all cases. I will assume that this is the case.
Are the versions of PowerBuilder the same from one machine to the other? And are the drivers being used the same? Like is the same SQL Server driver and specific version the same? When running in an exe is there anything in the "bin" file that would apply that does not when you run the report native in PowerBuilder?
If all this is the same then something else is different.
M.
Not all gray hairs are Dinosaurs!
October 23, 2014 at 3:48 am
Everything is same just the Windows 7 is installed from different CD(s)
October 23, 2014 at 5:48 am
Have you checked that you're reading from data and not cache, in each case?
http://en.helpdoc-online.com/powerbuilder_9.0/source/pbugp154.htm
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 28, 2014 at 10:47 am
zafarahmad (10/17/2014)
I am facing quite abnormal issue with MS SQL Server 2008 R2 64-Bit we have a process which generates summary of sales and insert into another table 1st time it shows correct results next time you query it show something else and in the report it show correct figure in process result window it shows wrong result the anytime during the day it start showing correct result every where.Can anyone help Please comment
zafarahmad - Did you get this answered?
Not all gray hairs are Dinosaurs!
October 29, 2014 at 1:09 am
Well you must run DBCC CHECKDB and checkout the entire database
SSMS Expert
October 29, 2014 at 9:30 am
Elliswhite (10/29/2014)
Well you must run DBCC CHECKDB and checkout the entire database
Elliswhite - Not that I disagree that this would be a good idea, and that double checking is a great step, for clarity why do you suggest this be done?
Not all gray hairs are Dinosaurs!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply