Report invalidly displays null values for 2 columns...

Last Post 22 Oct 2007 05:12 AM by oshaw. 2 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
oshaw
New Member
New Member

--
19 Oct 2007 05:52 AM
SELECT *

, case

when ( AckReceiptDate is null or Year(AckReceiptDate) = 1900 )

then 'Missing'

else Replace(convert( varchar, AckReceiptDate, 106), ' ','-')

end ARDescription

FROM RPT_ShipmentReport_V

WHERE
convert(varchar, ShippedDate, 111) >= '2005/01/01'

AND convert(varchar, ShippedDate, 111) <= '2007/12/01'

ORDER BY ClientName, Project, ProductName

______________________________________________________________________________


The above script was executed in our test and production environments. In test, the first two columns display accurate values. In production, when executed, the first two columns are null. To a different name, I restored to the production database in the test environment. The restored database displayed accurate values in the test environment.

I checked the properties of the two servers and the only noticeable difference is - the test server has one processor and the production server has 4 processors. Both sql server versions have SP1 applied.

There were no differences in the database properties.

___________________________________________________________________________________

Our solution:

The query is being run against a nested view which involves outer joins and linked servers. (When I restored the database in test, I made sure the linked servers were consistent.) The nesting is three levels deep.

I'm not a big fan of using complicated views for ease of maintenance so, my suggestion was to retrieve the report directly from the tables as opposed to using the view. This resolved the problem.

______________________________________________________________________________________

Other mysterious results:

The script gives accurate results for those two columns if the "REPLACE" function is removed. It also gives accurate results for those two columns if the "ORDER BY" is removed. (The inaccurate columns are not part of the "REPLACE" function. The inaccurate columns are a part of the "ORDER BY").


_______________________________________________________________________________________

If anyone has experienced a similar problem and discovered the source, please share.


***** I forgot to mention - The test and production servers are virtual servers.

Thank You.


oshaw
New Member
New Member

--
22 Oct 2007 05:12 AM
Thank You. I'll try the Set option.

You're right about using a function on an indexed field. I did not notice that and will pass the info. on to the person who wrote the query.

Thanks again. I'll let you know my results.
oshaw
New Member
New Member

--
22 Oct 2007 07:40 AM
You were right - changing the number of processors to one produced accurate results. This is a serious problem that is not detailed in the SP 2 list of bugs fixed.

Also, I checked and the date field in the where statement is not indexed. (It probably should be so, I'll talk to the person who created the table about this.)

Thank again.
You are not authorized to post a reply.

Acceptable Use Policy