Order by (boolean) acces-sqlserver

I have migrated an Access database to SQLserver,

all seems ok,
but I found that an "order by" on a boolean Access field, is reverse under
SQLserver ?
I had to add "desc" to my code "order by field desc" to retrieve the correct display.

Could someone confirm this ? I am very surprised.

Thanks, Pierre.Boolean values are stored differently in different systems. The intuitive "1 = true, 0 = false" is very common, but some systems use a more traditional "-1 = True, 0 = false", which I guess simplifies some boolean arithmatic. If you really want to know, I could ask my father, who taught statistics at Indiana University and who specialized in boolean statistics.
Be aware also, that SQL server does not have a "boolean" datatype. It has a "bit" datatype which can only take the values 1 and 0. MS Access has a true boolean datatype, so perhaps it usess -1 and 0, and this is where the translation error comes in.|||MS Access has a true boolean datatype, so perhaps it usess -1 and 0, Confirmed. Access does indeed use -1 and 0.|||yes , I understand that if it is -1,
the order is reverse,
But, in access database, the value seen is 1 and not -1,
do you mean that the real value stored is a -1 ?

Pierre.|||But, in access database, the value seen is 1 and not -1,
do you mean that the real value stored is a -1 ?Hi Pierre
Don't know where you got the first bit from (try typing ?(Cint(true)) and hit return in the immediate window - the answer is -1) but yes - True in Access is stored as -1.
Another gotcha is that the SQL Server Bit allows nulls which is an even more significant difference. If you are linking to SQL Server then you need to take account of this - Access throws a wobbly if it encounters a Null Bit.

Oracle to SQL Server

I've got a commercial Oracle database that I need to reverse engineer
to SQL server.
Using ER/Studio, I've generated the DDL for the tables, but one of the
lines references setting an image datatypes default to Empty_Blob, not
Null, and Query Analyzer gives me an error when it tries to interpret
that portion "Empty_Blob" of the DDL.
Any ideas how I should handle this?
CREATE TABLE abcdefg (
CRL image DEFAULT EMPTY_BLOB() NOT
NULL,
LAST_DOWNLOAD datetime NULL,
NEXT_DOWNLOAD datetime NULL,
SOURCE_URL varchar(1000) NULL,
THUMBPRINT varchar(60) NOT NULL
)
GO
--
HTH
Dale FyeI have no idea what EMPTY_BLOB is (maybe a user defined type?) but how about
'' instead.
--
Andrew J. Kelly
SQL Server MVP
"Dale Fye" <dale.fye@.nospam.com> wrote in message
news:OWcP3%23xnDHA.2244@.TK2MSFTNGP12.phx.gbl...
> I've got a commercial Oracle database that I need to reverse engineer
> to SQL server.
> Using ER/Studio, I've generated the DDL for the tables, but one of the
> lines references setting an image datatypes default to Empty_Blob, not
> Null, and Query Analyzer gives me an error when it tries to interpret
> that portion "Empty_Blob" of the DDL.
> Any ideas how I should handle this?
> CREATE TABLE abcdefg (
> CRL image DEFAULT EMPTY_BLOB() NOT
> NULL,
> LAST_DOWNLOAD datetime NULL,
> NEXT_DOWNLOAD datetime NULL,
> SOURCE_URL varchar(1000) NULL,
> THUMBPRINT varchar(60) NOT NULL
> )
> GO
> --
> HTH
> Dale Fye
>
>|||Dale,
There is no concept of EMPTY_BLOB in SQL Server. You can default the same to
NULL or as '' when using the same ...
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
"Dale Fye" <dale.fye@.nospam.com> wrote in message
news:OWcP3%23xnDHA.2244@.TK2MSFTNGP12.phx.gbl...
> I've got a commercial Oracle database that I need to reverse engineer
> to SQL server.
> Using ER/Studio, I've generated the DDL for the tables, but one of the
> lines references setting an image datatypes default to Empty_Blob, not
> Null, and Query Analyzer gives me an error when it tries to interpret
> that portion "Empty_Blob" of the DDL.
> Any ideas how I should handle this?
> CREATE TABLE abcdefg (
> CRL image DEFAULT EMPTY_BLOB() NOT
> NULL,
> LAST_DOWNLOAD datetime NULL,
> NEXT_DOWNLOAD datetime NULL,
> SOURCE_URL varchar(1000) NULL,
> THUMBPRINT varchar(60) NOT NULL
> )
> GO
> --
> HTH
> Dale Fye
>
>