Inconsistent Query Results in SQL Server 2008 R2

  • zafarahmad

    SSC Veteran

    Points: 203

    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

  • Gail Shaw

    SSC Guru

    Points: 1004424

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Lowell

    SSC Guru

    Points: 323321

    does your query use the NOLOCK hint?

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Miles Neale

    SSChampion

    Points: 13147

    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!

  • zafarahmad

    SSC Veteran

    Points: 203

    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

  • Miles Neale

    SSChampion

    Points: 13147

    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!

  • zafarahmad

    SSC Veteran

    Points: 203

    Everything is same just the Windows 7 is installed from different CD(s)

  • ChrisM@Work

    SSC Guru

    Points: 186020

    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

    [font="Arial"]“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


    For fast, accurate and documented assistance in answering your questions, please read this article[/url].
    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]
    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
    [url

  • Miles Neale

    SSChampion

    Points: 13147

    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!

  • Elliswhite

    SSCommitted

    Points: 1673

    Well you must run DBCC CHECKDB and checkout the entire database

    SSMS Expert

  • Miles Neale

    SSChampion

    Points: 13147

    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 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply