when ( AckReceiptDate is null or Year(AckReceiptDate) = 1900 )
else Replace(convert( varchar, AckReceiptDate, 106), ' ','-')
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.
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.