Boolean Value in MS SQL Server.
-
I am trying to migrate an access database to Microsoft SQL Server 2000 database.
My problem is that there is a program written in visual basic that Insert/updates tables. It does an INSERT Statement that uses a Boolean Value.
Example
ID is defined as a Number, TestF is defined as Yes/No Datatype in Access
Insert into TestTable (ID, TestF) Values (10, True)
How can I get this insert statement to work with Microsoft SQL Server 2000? I CAN NOT change the SQL Statement.
ID is defined as a Number, TestF is defined as ?????Datatype in Microsoft SQL Server
-
Test as bit
* Noel -
I can't get bit to work.
So I guess you should use CHAR(5), although that isn't satisfying at all.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
You cannot do this wthout changing the SQL statement as written because, without quotes, the word True will be seen as an identifier, not a value. Unless SQL is written carefully with expert knowledge of all the RDBMSs that will be supported (and many non-standard but often necessary functions like string manipulation handled at a higher layer than the DBMS), one cannot expect to "convert" from one system to another without rewriting some of the SQL code.
--Jonathan -
to add to this. Access exposure can be treated.--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
What version of Access?
Access 2000 & newer (maybe older too, not sure) has an upsizing wizard that will automatically convert your Access database into either an existing or new SQL Server database. It should be smart enough to be able to convert an Access yes/no field to a SQL Server bit field.
If you want more control over the process, you can create linked tables in Access to the SQL Server database and write your own queries to insert/update data into the linked tables.
If you really want to retain it as a yes/no field in SQL Server, you can create a user-defined data type of yes/no.
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url] -
Yes, the Upsizing Wizard turns yes/no into bit.
However, the problem is, that the insert statement will not work, and I think if that statement can not be changed as pointed out you're pretty much out of luck
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
this is kinda an experience thing.
also, notice that access uses (i think) -1 and 0 (true and false)... and sql server bit uses 0 and 1 (false and true).
Since that time, when writing an app for both access or sql server back end, i have written my queries for
select * from mytable where myField = 0
(for getting false's)
OR
select * from mytable where myfield <> 0
(for getting trues).
this way, it didn't matter which backend db() i was using, my queries were correct.
..
i didn't read all the posts on this on, but you may want to create a dummy field in your access db (a column rather) and put 0 or 1's in it based on the string of the first column ("true" or "false") ... maybe that isn't your issue, just throwing something out there.
..
its a habit, but i still use the =0 or <>0 thing (instead of =0 or =1). haven't had any issues with that for 7 years now.
..
-
Access 2000 works fine as a middleware layer. The program is written in visual basic 6.0 and uses an Access 2000 Database so this solves the problem.
I have found out that if you try to use this SQL Statement from within side of QUERY Analyzer the statement will fail.
Server: Msg 128, Level 15, State 1, Line 1
The name 'true' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted. You would have to change the SQL Statement if the program did not use MS Access.
Thanks for all the help.
-
Interestingly when you link the SQL Server table back into an Access db and run a statement like
INSERT INTO....(TRUE)
from within Access, the rows will be inserted correctly.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
I've been following this thread... was thinking maybe some form of the following may be usable...
-- In T-SQL
Create Table TestTable (ID Int, TestF Bit)
-- Then change:
-- Insert into TestTable (ID, TestF) Values (10, True)
-- to something like:
Insert into TestTable (ID, TestF) SELECT 10, True -- Won't Work, but
Insert into TestTable (ID, TestF) SELECT 10, True From (Select 1 as True, 0 as False) DerivedTB
-- or
Insert into TestTable (ID, TestF) SELECT 10, False From (Select 1 as True, 0 as False) DerivedTB
where all but the new values are "constant" texts.
Once you understand the BITs, all the pieces come together
-
The problem is that the SQL CAN'T BE CHANGED
* Noel -
Yeh Noel, I hear ya. Just wanted to maybe give you some "ammo" or thoughts that might help.
Is the statement hard coded into an app some where, and is now being issued to SQLSrv. instead of Access?
Once you understand the BITs, all the pieces come together -
What happened in this case is that Access is Smart Enough to convert True to 1 when sending the query to sql server
* Noel -
I have no clue.
I didn't post the question
* Noel
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply