Compatibility Issue 65 or 80 - SQL Server Forums

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)

Compatibility Issue 65 or 80

Author

Topic

dlohaus
Starting Member

8 Posts

Posted - 2006-10-19 : 12:07:29

First time poster, be gentle!

We are wondering if anyone else has come across this issue……

We are working with an older SQL Server database created under 6.5. SQL Server was upgrade to 2000 but had to be set at the compatibility mode 65 for that last few years due to perceived application issues. We made an attempt to change the compatibility mode to 80 and traced the application issues to the handling of fields that contain NULL and have a datatype of ‘char’. When querying these these char datatype fields, an ADO Recordset returns and ASCII NUL or CHR$(0) in the compatibility mode of 80.

Our select statement attempted to convert NULL to empty string using something like the following:

Select isnull(fldA, ‘’) as fldA from tbl1;

Under compatibility mode 65 this works fine and fldA returns an empty string. Under compatibility mode 80, it returns an ASCII NUL padded field. Which ADO and VB do not recognize as NULL or empty string.

However, in the course of narrowing the issue, if we make a copy of the table using DTS’s import (under either compatibility mode), the Select on the new table returns and empty string under either compatibility mode.

It appears that the original table has some property that causes NULL char fields to be returned differently under different compatibility modes.

We are hoping someone has dealt with this issue and could provide some advice and/or options besides using DTS to make copies of all the tables.

Thanks in advance,
- Dan

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-10-19 : 12:38:21

I guess you can do one of two things.

1. Use this in queries to treat CHR(0) as NULL

CASE fldA WHEN char(0) THEN NULL ELSE fldA END

so your previous code becomes

Select isnull(CASE fldA WHEN char(0) THEN NULL ELSE fldA END, '') as fldA from tbl1;

2. Run updates against your tables to replace NUL with NULL like this

UPDATE mytable SET fldA = CASE fldA WHEN char(0) THEN NULL ELSE fldA END

dlohaus
Starting Member

8 Posts

Posted - 2006-10-19 : 13:45:55

Thanks for the input snSQL,

While I attempt your options, I see that I may need to be more specific. It doesn't appear to be a problem with SQL Server recognizing that the field is NULL. It is how either SQL Server and/or ADO is representing NULL back to VB for char datatype fields. Apparently, SQL Server is passing the Char(0) back for length of the field only on the older tables. (e.g. Char 1 returns "\0"; char 4 returns "\0\0\0\0")

- Dan

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-10-19 : 14:10:33

Well I'm not sure what your ultimate solution is but one thing: You can't cast an "empty string" as char datatype. If you objective is to convert nulls from a char column to an empty string you need to cast the null value as VARchar or rtrim it.


--select 't' + convert(char(10),'') + 'g'
------------ 
t          g

--select 't' + convert(varchar(10),'') + 'g'
------------ 
tg

Be One with the Optimizer
TG

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-10-19 : 14:19:11

That's OK, then it just becomes

1. Use this in queries to treat NUL as NULL

CASE fldA WHEN replicate(char(0), len(fldA)) THEN NULL ELSE fldA END

so your previous code becomes

Select isnull(CASE fldA WHEN replicate(char(0), len(fldA)) THEN NULL ELSE fldA END, '') as fldA from tbl1;

2. Run updates against your tables to replace NUL with NULL like this

UPDATE mytable SET fldA = CASE fldA WHEN replicate(char(0), len(fldA)) THEN NULL ELSE fldA END

dlohaus
Starting Member

8 Posts

Posted - 2006-10-19 : 16:22:18

Thanks TG,

We too thought that trying to return an empty string, '', as a char seems incorrect. We couldn't find anything that states this or recommends a solution. Also, compatibility mode 65 handled it so why does 80, not handle it?

Why if I create a new table with char fields under 80 and 65 modes do they both return empty strings?

- Dan

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-10-19 : 16:34:57

The char type is a fixed length character type, so when you set it to '' it really becomes a string of spaces because it it has to always have the fixed length specified in its declaration. If it didn't do that it would just be a varchar.

In fact though there is an option to make it more like a varchar, and perhaps that's what is causing some of what you are seeing? This from Books Online
"If SET ANSI_PADDING is OFF when CREATE TABLE or ALTER TABLE is executed, a char column defined as NULL is handled as varchar. "

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-10-19 : 17:06:01

That is a good quote from BOL and probably right to the source of your confusion.

I'm not really clear on what problem you're trying to solve. Just in terms of sql server and compatibility modes of 65 and 80, storage and selection of null and non-null values are similar (if not identical).

if you assign a non-null value to a char(n) column the value will be right padded with spaces until the total length is n characters long. A varchar column will not be padded with spaces. Nulls will return...Nulls.

Other technologies (outside sql server like odbc, vb, ado, or whatever) will interperate these values. They may not do it consistantly. It's up to you the developer to deal with that.

So...what's the question again

Be One with the Optimizer
TG

dlohaus
Starting Member

8 Posts

Posted - 2006-10-19 : 17:19:49

If had to phrase a question on what we are trying to do, it would be "what is the proper syntax to consistently return an empty string for nullable char and varchar fields?" As you can see from previous posts, the ISNULL is identifying that the fields are NULL but we are getting different results between compatibility modes for char fields being set to empty string.

I hope I am making sense....

- Dan

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-10-19 : 17:40:13

Perfect sense, and in that case, this should do exactly what you want

isnull(CASE fldA WHEN replicate(char(0), len(fldA)) THEN NULL ELSE fldA END, '')

That is, assuming that the NUL characters are actually in your data (which they seem to be, based on your previous posts). If they are not then the issue lies with your client access and you'll have to basically do this but in your client code.

If the NUL characters are in your data then replace once off using the update I gave before and you'll never have the problem again - you said you could DTS all the tables, updating would surely be easier.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-10-19 : 18:02:42

I don't think he needs to update anything because he said:
>>"fields that contain NULL and have a datatype of ‘char’...ADO Recordset returns and ASCII NUL"

>>what is the proper syntax to consistently return an empty string for nullable char and varchar fields?
I think this should do it:

select isnull(<varcharcolumn>, '') as varcharColumnName
,rtrim(isNull(<charColumn>,'')) as charColumnName


select	'start-' + isnull([varcharcolumn], '') + '-end' as varcharColumn
	,'start-' + rtrim(isNull([charColumn],'')) + '-end' as charColumnTrimmed
	,'start-' + isnull([charColumn],'') + '-end' as charColumnNotTrimmed
from	(
	select	cast('tg' as char(10)) as [charcolumn], cast('tg' as varchar(10)) as [varcharcolumn] union all
	select	cast(null as char(10)), cast(null as varchar(10))
	) a

output:
varcharColumn        charColumnTrimmed    charColumnNotTrimmed 
-------------------- -------------------- -------------------- 
start-tg-end         start-tg-end         start-tg        -end
start--end           start--end           start-          -end

Be One with the Optimizer
TG

dlohaus
Starting Member

8 Posts

Posted - 2006-10-20 : 12:35:02

I adapted your suggestion to the table I am working with and a new table in the same database using compatibility mode 65 (mode 65) and mode 80.

Table A is an old table (circa 2002) in the database.
Table B is a new table (circa 2006) in the database.
fldA is the varchar field
fldB is the char field

Using Table A with the following query:

select isnull([fldA], '') as fldA, rtrim(isNull([fldB],'')) as fldBTrimmed, isnull([fldB],'') as fldBNotTrimmed from TableA where [fldB] is null and [fldA] is null

the results as VB see it(comments)
fldA fldBTrimmed fldBNotTrimmed
mode 80: "" "" "\0\0\0\0" (\0 is ASCII NUL)
mode 65: " " Null " "

the results for the same query except with table B (new table)
mode 80: "" "" " "
mode 65: " " Null " "

- Dan

dlohaus
Starting Member

8 Posts

Posted - 2006-10-20 : 12:42:05

Sorry about that...
I'm inexperienced in formatting the data using this interface.

- Dan

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-10-20 : 12:56:19

>>I'm inexperienced in formatting the data using this interface.
see the forum faq for how to use [code] tags to leave formatting intact.

So it looks like if you use the trimmed method for char datatypes you get consistant results (tableA and tableB), yes?
(I only included the NotTrimmed column to prove the Trimmed method would work.)

Be One with the Optimizer
TG

dlohaus
Starting Member

8 Posts

Posted - 2006-10-23 : 09:45:14

TG,

Thank you for your patience.

I applied the formatting help and to answer you question, not quite.
I was hoping of a way to handle the 65 to 80 issue. As you can see, it still reports these differently.

I adapted your suggestion to the table I am working with an old and a new table in the same database using compatibility mode 65 (mode 65) and mode 80.

Table A is an old table (circa 2002) in the database.
Table B is a new table (circa 2006) in the database.
fldA is the varchar field
fldB is the char field

Using Table A with the following query:

select isnull([fldA], '') as fldA, rtrim(isNull([fldB],'')) as fldBTrimmed, isnull([fldB],'') as fldBNotTrimmed from TableA where [fldB] is null and [fldA] is null


the results as VB see it(comments)
          fldA     fldBTrimmed     fldBNotTrimmed
mode 80:   ""          ""            "\0\0\0\0"    (\0 is ASCII NUL)
mode 65:   " "         Null          " "

the results for the same query except with table B (new table)
mode 80:   ""          ""            " "
mode 65:   " "         Null          " "

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-10-23 : 22:47:09

My suggestion was just this:

quote:
(from way up the thread)
select isnull(<varcharcolumn>, '') as varcharColumnName
,rtrim(isNull(<charColumn>,'')) as charColumnName

The column "fldBNotTrimmed" should not be included in the solution. That was just there to illustrate that RTRIM was necessary for the char columns.
In other words, just ISNULL the varchar columns and RTRIM-ISNULL the char columns.

Be One with the Optimizer
TG

dlohaus
Starting Member

8 Posts

Posted - 2006-10-24 : 16:41:34

I'm not sure I follow. I don't see how the RTRIM worked.

I was hoping to find a consistent solution to for fldA and fldB for both modes (65 and 80). The RTRIM, as you can see, returns NULL not empty string when TRIMMED under mode 65.

- Dan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources