Problem With Bit Fields When Running Against SQL 2005

[david]

Perhaps a compatibility feature in the ODBC driver, which
works differently when connected to 2005 and 7.

It is not on the list of compatibility features which work differently
when connected to 2005 and 7, but since it doesn't seem to be
documented anywhere, that doesn't necessarily prove anything.

(david)

 

[david]

Depends which version of SQL Server/ODBC. Was unsigned.
Now is signed or unsigned. (Which would be the way a C
programmer would think was Godly).

(david)

Good to know. Thanks.

Not tinyint but small integer. Like the Bit field, the tinyint is an
unsigned quantity on SQL-Server and cannot be used to store the value
of -1.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

That's a good point about bits not being portable. Using tinyint
certainly would have avoided this problem. I wonder what is the upside
to using bit instead of tinyint? Just being able to do boolean
comparisons?

Neil


It's not a glitch, it's a fundamental problem with the translation
between VB (8086) Booleans and SQL Server (PDP) bit fields.

I recommend Never Use Bit Fields, because bit fields aren't really
portable. Use small integer fields instead of bit fields. This is not
just VB and SQL Server: bit fields aren't portable even in C.

Of course, bit fields are normally portable if you only use the zero
value: from "Writing Solid Code": "the portable range of a bit field is
0",
but bit fields are still a problem waiting to happen.

So you can write:

strSQL = strSQL & " AND [Field1]=" & Me.Field1CheckBox


strSQL = strSQL & " AND NOT ([Field1]<>" & Me.Field1CheckBox

but unless I was having trouble with database size I wouldn't do that.

By the way, dates have the same problem as Booleans: if you
use a numeric representation for a date, Access will translate for
SQL Server when it recognises a translation is required: if you
put in date 1 you won't have a record with date 1.

(david)

Not quite that simple. For example, I have a form in one place that
has a series of check boxes that represent bit fields. If the user
wants to return records for which that field is true, they check the
box. The code looks something like:

strSQL = strSQL & " AND [Field1]=" & Me.Field1CheckBox

Thus, it takes the value of the check box and uses it in the dynamic
SQL. The values are -1 and 0, which the ODBC driver sent to SQL Server
as True and False. That code would have to be entirely rewritten.
(I've created a wraparound function that converts the checkbox 0
and -1 to 0 and 1; but it'll have to be applied to the entire
database, which is a ton of code.)

The bottom line here, in my opinion, is that the ODBC driver continues
to DISPLAY -1 for True for data retrieved FROM SQL Server, but
requires us to SEND 1 for True for criteria sent TO SQL Server. That
makes no sense, and seems to be a glitch. I was hoping there was some
sort of patch or fix.

Neil


"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam
please)> wrote in message
Instead of testing for 1 or -1, you should test for <> 0; this way,
you won't have to chase a moving target.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


Have an Access MDB (2002/3 format) running against a SQL Server back
end, using ODBC linked tables. Recently upgraded from SQL 7 to SQL
2005.

With the bit fields in SQL Server, even though their values are 0
and 1 for False and True, the ODBC driver displays them as 0 and -1,
which are the Access False and True values. That was true for SQL 7
as well as for SQL 2005.

However, I find that when I run a query (not a pass-through; just a
regular Access query), I have to use "1" in the Where clause to get
True values from the SQL table, even though they are displayed
as -1!

For example, "Select * From MyTable Where SomeBitField=-1" returns
no records; but "Select * From MyTable Where SomeBitField=1" returns
records. However, the records show -1 in the bit field!!!!

If I use True instead of 1 or -1 it works fine. But I have tons of
code that build dynamic SQL that use -1 for true, and I'd have to
change all of those.

Also, since the above is true for saved queries as well as dynamic
SQL, it seems to be some sort of glitch (since the saved query would
have to have "1" in the Criteria field, but would display "-1" in
the results!).

Does anyone know if this is a known problem and if Microsoft
released a patch or whatever for this? I'm running Access 2003, SP3
(v. 11.8166.8221).

Thanks!

Neil

 

[SmartbizAustralia]

Have you set sql compatibility on?

It's amazing how this is off by default and you find little things
like % versus * bite you when you convert to sql

Regards,
Tom Bizannes
Microsoft Access and Sql Server Specialist
Sydney, Australia
http://www.macroview.com.au

 

[Beachldy]

This has been a problem for all my clients who upgraded to SQL 2005. All
Access apps had to change the true/false -1/1 0/false code everywhere. I've
also noticed that we have to return queries getting false values to be "false
or null", or it misses the counts also. It's inconsistent on counting
queries, as sometimes "where false" will work, but sometimes requires "where
false or null" or even sometimes "where 0 or false or null". Whatever
changed in SQL 2005, has been a nightmare for Access applications everywhere
concerning bit/true/false queries.

 

[Paul Shapiro]

I think these 3 rules are enough to prevent Access problems when working
with SQL Server bit fields. The problem has been around for a long time. In
all fairness, I believe these problems have been with Access as the front
end, not with SQL Server. You can't blame SQL Server for what the front end
does.

1. Do not allow null for any SQL Server bit fields if you'll be working with
the data in Access.
2. Always test for bitField = 0 or for bitField <> 0, not for true or false.
Zero is false both in SQL Server and in Access.
3. Apply the Access hotfixes that specifically address problems with bit
fields in SQL Server. These vary by Access version, but I know there was at
least one for Access 2003. I think it was a post-SP3 hotfix, but I don't
remember anymore.

 

[Neil]

I think these 3 rules are enough to prevent Access problems when working
with SQL Server bit fields. The problem has been around for a long time. In
all fairness, I believe these problems have been with Access as the front
end, not with SQL Server. You can't blame SQL Server for what the front end
does.

We've had this discussion in this thread, and I don't want to go through it
again. But I encourage you to read what's been written here and see.

And, while you and I would, in theory, say that it's a front end thing,
that's just not the case. I can take the back end, change the compatibility
level to SQL 7, and the problem disappears. Change it back to SQL 2000 or
2005, and the problem comes back. These are changes to how the back end, SQL
Server, handles the data. The front end is staying the same at all times.

This also confirms what I experienced, namely these problems didn't surface
until I upgraded the back end from SQL 7 to SQL 2005.

But, again, this isn't something I want to get into a discussion about,
since it's already been discussed here in this thread at great length.

1. Do not allow null for any SQL Server bit fields if you'll be working
with the data in Access.
2. Always test for bitField = 0 or for bitField <> 0, not for true or
false. Zero is false both in SQL Server and in Access.
3. Apply the Access hotfixes that specifically address problems with bit
fields in SQL Server. These vary by Access version, but I know there was
at least one for Access 2003. I think it was a post-SP3 hotfix, but I
don't remember anymore.

Personally, I think using the smallint field instead of bit fields is a much
better solution. Why use bit fields if they create so many issues with
Access? Just to save disk space? Much simpler to just use smallint instead
of bit, and just be able to test for 0 and -1, and no real upside to using
bits instead.

Neil

 

[a a r o n _ k e m p f]

wow Paul... I blatantly disagree.

your 3 clauses assume that they're jumping through a bunch of Jet
hoops-- I personally prefer to cut the bullshit and use ADP directly.

and I use = 0 and = 1 instead of = 0 and <> 0
(for a zillion reasons)

 

[a a r o n _ k e m p f]

WRONG
this is a problem because you use Jet Linked Tables

This has been a problem for all my clients who upgraded toSQL2005.  All
Access apps had to change the true/false -1/1 0/false code everywhere.  I've
also noticed that we have to return queries getting false values to be "false
or null", or it misses the counts also.  It's inconsistent on counting
queries, as sometimes "where false" will work, but sometimes requires "where
false or null" or even sometimes "where 0 or false or null".  Whatever
changed inSQL2005, has been a nightmare for Access applications everywhere
concerning bit/true/false queries.

Previously Access/ODBC took the native -1, saw it as "True", and sent it to
SQLas 1 ("True" inSQL).

Take heed....I am going to bring this issue up with the access team when I
get a chance. I do think this represents a problem. I not going to holdmy
breath on this, but I really am on your side here.

I *do* agree this should NOT have been broken, and furthermore, I also think
that jet (or who ever messed this up) should make this issue transparent for
access developers....