Concat issue has me perplexed

Question

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.

Answer 1

Based on your sample data, the data you're actually querying for ID = 1396779 is different

No amount of trims, isnulls etc will change Schmoe into Frost or Jack into Joe.
Ergo, data is different.

Otherwise, you have some DB or Server confusion going on. A report based on the DataWarehouse can't give correct data if your query against the DataWarehouse is wrong.

Note that a cross database Synonyms or View, or a table in a different schema (your query doesn't use dbo.PCDB_VISIT for example) could mislead you. Or you have some client alias that means you are pointing at the wrong server from SSMS but the Report is correct.

Try this to make sure that you are where you think you are

DECLARE @ID int;
SET @ID = 1396779;
SELECT @@SERVERNAME, DB_NAME(), ID, 'LName' FName, LName Field FROM PCDB_VISIT PV 

Answer 2

Stab in the dark:

What does this get you?

DECLARE @ID int;
SET @ID = 2;
SELECT ID, 'LFM'   FName, RTRIM(LName) + ', ' + RTRIM(FName) + ' ' + RTRIM(MI) Field FROM TABLE  WHERE ID IN (@ID)

If I set results-to-text, 50 characters per column, and run this query, I get similar behavior:

/*------------------------
SELECT 'abcdef' + ', ' + 'bc'
UNION
SELECT 'abcdef                                                           ' + ', ' + 'bc'
------------------------*/

--------------------------------------------------
abcdef, bc
abcdef