Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ktasch
Starting Member
2 Posts |
 Posted - 2004-10-27 : 09:38:17
|
| Hi!I am running into a problem using ISNULL. I am using ISNULL in a SELECT statement. SQL Server returns garbage instead of recognizing nulls like I would expect. Here is my SELECT statement:SELECT TVFC_SPE_DET.SPE_MML_C, ISNULL(TVFC_SPE_DET.FND_MML_C, '') FND_MML_C, TVFC_SPE_DET.SBU_C, ISNULL(TVFC_SPE_DET.SPE_SBU_ALT_001_C,'') SPE_SBU_ALT_001_CFROM TVFC_SPE_DETWhen coded as above, I get garbage text in my two columns using ISNULL. If I change those lines to have a space in the text(i.e. ISNULL(TVFC_SPE_DET.FND_MML_C, ' ')), it works fine. I am using SET QUOTED_IDENTIFIER OFF and SET ANSI_NULLS OFF in my database, so I would think that this should work without the space, and it does, when I run it in Query Analyzer. It just returns garbage when I run it through a VB program using an SQLOLEDB connection.Does anyone have any ideas as to what might cause ISNULL to return garbage text?Thanks! |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-10-27 : 12:12:04
|
maybe you should provide us with sample data to work with and table definitions...,Go with the flow & have fun! Else fight the flow  |
 |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-10-27 : 22:15:59
|
quote:
Originally posted by spirit1
maybe you should provide us with sample data to work with and table definitions...,Go with the flow & have fun! Else fight the flow
yap, and try coalesce instead. and what do you mean by "garbage"?--------------------keeping it simple... |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2004-10-28 : 04:11:41
|
| I think the VB program creates the "garbage",it doesn't handle '' correctly, but handles ' ' ok.rockmoose |
|
|
|
ktasch
Starting Member
2 Posts |
Posted - 2004-10-28 : 11:07:11
|
| I'm working at getting some sample data together. I don't actually have authority to view the data myself -- one of the "fun" aspects of working where I do. :-) Stay tuned...I'm not familiar with coalesce; I'll read up on it. Thanks for the suggestion.The suggestion that VB is handling the data incorrectly could be true, but it's working correctly in 2 out of 3 environments. That is, we have the same code running in development, QA and production. The devices that the code is running on are configured identically. In each environment, they're running against a different SQL Server instance. Two of these work fine, but the third does not. I've talked to the database people about how the databases are set up, and they tell me the setup is the same between the three environments. But something has to be different; otherwise it would work (or not work) in all three environments.I'll keep digging and post anything else I can find. Thanks for the suggestions! |
|
|
|
|
|
|
|
|
ISNULL returns garbage text