query not returning correct results in enterprise

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

query not returning correct results in enterprise

Author

Topic

SQLIsTheDevil
Posting Yak Master

177 Posts

Posted - 2007-12-17 : 12:21:25

here's the query in enterprise manager:

select isnull(column1_num + '-' + cast(column2_num as varchar), 'no result') as Exp1 from Numbers

The query returns:

998-1
994-2
772-4
no result
550-1

Now, in my IDE, the SAME EXACT query returns:

998-1
994-2
772-4
-
550-1

I believe the latter is right. The question is why is there a discrepency and why is enterprise manager returning "no rseult" isntead of "-"?

Thank you.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-12-17 : 12:30:54

Is data same on both servers for that particular record?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

SQLIsTheDevil
Posting Yak Master

177 Posts

Posted - 2007-12-17 : 12:35:04

it is one server where this data is concerned.. From the IDE, I am querying sql server with the exact same query and it returns the result I stated. From the sql server itself, the query returns "no result" instead of "-". I find this rather strange.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-12-17 : 12:55:21

Does your IDE have the "SET concat_null_yields_null" option turned off?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

SQLIsTheDevil
Posting Yak Master

177 Posts

Posted - 2007-12-17 : 13:42:17

Wow, I was unaware of that variable. It's obviously set to ON on sql server 2000. The IDE I'm employing at the moment is called CodeCharge. I don't know if there is a way to set sql variables in this IDE; maybe some research will yield a fruitful solution. I do find it fascinating that my IDE appears to ignore the concat_null_yields_null option.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-12-17 : 14:20:55

Try setting to ON manually and see if it helps:

SET CONCAT_NULL_YIELDS_NULL ON

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

SQLIsTheDevil
Posting Yak Master

177 Posts

Posted - 2007-12-18 : 08:39:15

Yeah, I can try setting it to on making a query from the IDE. I read that it's a bad idea to use it in a procedure, as each call will actually re-compile the the procedure itself. I didn't have a chance, but I'm fairly certain I have to modify the connection string I'm using in my IDE to connect to SQL 2000. Anyway, I never said it before, but I really appreciate your help.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-18 : 09:14:43

select isnull(cast(column1_num AS VARCHAR(10)), '') + '-' + isnull(CAST(column2_num AS VARCHAR(10)), '') as Exp1 from Numbers

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources