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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2003-02-24 : 19:44:26
|
| Tony S writes "SQL Server 7.0 allowed the use of an Access-like query that referred to a boolean true value as -1. Even though SQL 7 stored the boolean true in the bit field as 1, a query like select * from Table1 where bitfield = -1, would return all the records that bitfield was true. In SQL 2000, no records are returned. Does anyone know why, and can this behavior be modified? We have dozens of former Access apps that use the assumption that boolean true = -1. Any help would be appreciated. Thanks." |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-02-24 : 19:46:44
|
| In SQL Server, a bit column is either 0 or 1, not -1. You'll have to rewrite your apps on the SQL Server end. BTW, that was a serious flaw in MS Access, although it's not entirely an Access problem either. Also realize that the concept of true boolean values does not exist in SQL Server either. You can't say:SELECT * FROM myTable WHERE BooleanColumnYou'd have to write it to have an actual comparison:SELECT * FROM myTable WHERE BooleanColumn=1 |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-02-25 : 12:11:04
|
| You can create views for all the tables which convert the bit value from 1 to -1. Rename the tables and give the views the old table names - or put them in another database.Or better - correct the code.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-02-25 : 12:12:00
|
| You can create views for all the tables which convert the bit value from 1 to -1. Rename the tables and give the views the old table names - or put them in another database.Or better - correct the code.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
Query fails in clause where bit = -1