MSSQL 2005 Server Workgroup.
I have a database that is serving as a "Warehouse" for another, older, slower cobol database/application.
I've recently moved the warehouse from one SQL Server instance to a new instance, to keep the warehousing I'm doing separate from other production databases.
The issue I'm having has me perplexed. The table I have, as a copy of the older database, is has, for the purpose of this question, 4 fields: ID, LName, FName, MI, FullName - all NOT NULL. On the report server I have a calculated field FullName: LastName, FName MI:
FullName = LName + ', ' + FName + ' ' + MI;
What's happening is the names are not joining consistently, and it has me completely baffled.
DECLARE @ID int; SET @ID = 1; SELECT ID, 'LName' FName, LName Field FROM TABLE WHERE ID IN (@ID) UNION SELECT ID, 'FName' FName, FName Field FROM TABLE WHERE ID IN (@ID) UNION SELECT ID, 'MI' FName, MI Field FROM TABLE WHERE ID IN (@ID) UNION SELECT ID, 'LFM' FName, LName + ', ' + FName + ' ' + MI Field FROM TABLE WHERE ID IN (@ID)
This outputs the expected:
1 FName Jack 1 MI T 1 LName Frost 1 LFM Frost, Jack T
But if I switch it to a different ID I get:
2 FName Joe 2 MI E 2 LName Schmoe 2 LFM Schmoe
It's not an issue with nulls (Three fields have data). Trim, coalesce, different combinations of name (LFM, MFL, LF, MF, etc) end up the same: Either the person has a full name... or the person has the first one listed.
To my knowledge I did not have this issue in the other isntance. New instance is on the same server.
Original Server Instance: 2005 9.0.3042 Serv\SQL2005 New Server Instance: 2005 9.0.1399 Serv\DataWarehouse
Note: Query against "original" database works as expected. Query against a report built upon this warehouse works as expected.
So Original Database > Data Warehouse > Report. Original works fine. Report works fine. Data warehouse doesn't. Warehouse and Report are in the same instance, but different databases.