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.

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.

  • New server instance = 9.0.1399 - might want to consider a service pack there. Where are you seeing this output? Do you have any code that plays with the CONCAT_NULL_YIELDS_NULL setting? Is it possible the "table" is a view? Commented Feb 23, 2012 at 19:43
  • If it ain't broke, don't fix it... nothing public facing. Might consider it if it might be the actual issue.
    – WernerCD
    Commented Feb 23, 2012 at 19:50
  • How do you know it isn't? As far as you're aware, the only difference between these two systems is the service pack, right? Commented Feb 23, 2012 at 19:54
  • Coalesce, IsNull, LTrim/RTrim, etc doesn't affect output. This is working with fields that have data in them. It's all tables, no views. I installed this instance, someone else installed the other instance so there could be tons of settings that differ. "CONCAT_NULL_YEILDS_NULL" is false, and shouldn't be relevant since the fields are all NOT NULL.
    – WernerCD
    Commented Feb 23, 2012 at 19:55
  • 1
    Fix your question: don't waste our time
    – gbn
    Commented Feb 24, 2012 at 15:06

2 Answers 2


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

SET @ID = 1396779;
  • The result list has 700k IDs, each with ID, Last, First, MI. I'm not expecting Schmoe to turn into Jack. I'm expecting "Frost, Jack T" and I'm getting "Frost, Jack T"... but on the next "row" I'm expecting "Blow, Joe" and I'm getting "Blow"... Line after that I'm expecting "Kirk, James T" and I'm getting "James". Same result set. Same query. Same data source. Different results on each line. I know about Nulls - these are all NOT NULL columns, so A + null = null isn't an issue. It's also not a server issue. I know distinctly which server I'm working on (Only 3 for this problem).
    – WernerCD
    Commented Feb 24, 2012 at 14:59
  • @WernerCD: in which case, your question is misleading. Your data shows both Schmoe and Frost for ID 1396779. This answer matches your actual question...
    – gbn
    Commented Feb 24, 2012 at 15:06
  • Ahh.. Sorry about that. Was trying to be descriptive and made an error. No need to be rude. Will fix it momentarily. I did say "When I switch it to a different ID...", so mistake aside question should still be at-least descriptive enough to explain the issue.
    – WernerCD
    Commented Feb 24, 2012 at 15:53
  • @WernerCD Where was gbn being rude? Commented Feb 24, 2012 at 16:14

Stab in the dark:

What does this get you?

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'
SELECT 'abcdef                                                           ' + ', ' + 'bc'

abcdef, bc

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.