Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
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? –  Aaron Bertrand Feb 23 '12 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 Feb 23 '12 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? –  Aaron Bertrand Feb 23 '12 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 Feb 23 '12 at 19:55
1  
Fix your question: don't waste our time –  gbn Feb 24 '12 at 15:06
show 3 more comments

2 Answers

up vote 3 down vote accepted

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 
share|improve this answer
    
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 Feb 24 '12 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 Feb 24 '12 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 Feb 24 '12 at 15:53
    
@WernerCD Where was gbn being rude? –  Aaron Bertrand Feb 24 '12 at 16:14
add comment

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 
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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