2008 bit data type problem

[addntox]

I restored a backup from SQL 2000 to SQL 2008R2, now in 2000 the bit value was 0 and 1, but 2008 designates it true/false. The software i'm using for this (it was specially developed) is returning errors because of this.

unfortunatly the original source code is no longer available so i cant make the changes at the source code level. Is there any way to set 2008 to use the old bit value, if not is there any alternatives you can recommend that will produce the same result using other data types. From what i can gather the application is loading items into combo boxes based on whether the cells in this column have a bit val of 0 or 1

[honeybee]

I don't know if you can change the behaviour of the bit columns, but I guess using a short/tiny integer data type to store the 0 and 1 values should be fine. Expanding the scope usually doesn't break anything.

.

[addntox]

thank you sir, i was thinking along the same lines myself, just wanted to get anothers opinion, i'm sending the current db over to the development server now and i'm going to convert everything there and run the program to see what happens

[jmcilhinney]

I restored a backup from SQL 2000 to SQL 2008R2, now in 2000 the bit value was 0 and 1, but 2008 designates it true/false. The software i'm using for this (it was specially developed) is returning errors because of this.

That's not true. In SQL Server Management Studio, which was introduced with SQL Server 2005, bit values are displayed as True or False, but the data is still stored as 1 or 0 and, if you write a query in SSMS, you must use 1 or 0 for bit data. Even ADO.NET requires you to use 1 and 0 to represent bit literals.

[addntox]

hrmm its looping through the table and returning an EOF BOF error saying current record doesnt exist is it possible originally it was using 1/-1 for true false?

[honeybee]

One thing I know: It couldn't possibly use 1 and -1 to store true/false.

.

[jmcilhinney]

I think it would be a good idea to show us the actual code, both VB and SQL that you're using. Presumably it's VB6, which I can't help with, but presumably there are others who can.

[addntox]

example of a part where it errors

Sub Combo_Trucker(Curform As Form, Index As Integer)
    On Error GoTo errH
    Dim strSQL As String
    Dim lngsqlerr As Long
    Dim rs As adodb.Recordset
    Dim lp As Integer
    
    strSQL = "Select [INDEX],NAME from SUPPLIER Where TRUCKER=-1 and COMPID = " & AppComp & " order by ID"
    lngsqlerr = Exec_SQL_RS_SRV(strSQL, rs)
    
    With Curform
    For lp = 1 To Index
        rs.MoveFirst
        Do Until rs.EOF
            .ComboTrucker(lp).AddItem rs!Name
            .ComboTrucker(lp).ItemData(.ComboTrucker(lp).NewIndex) = rs![Index]
            rs.MoveNext
        Loop
    Next lp
    End With
    
    rs.Close
    Set rs = Nothing
    Exit Sub

[jmcilhinney]

What exactly does 'AppComp' contain? What data type is the TRUCKER column?

[addntox]

trucker is the column that is set to bit type in SQL 2008

COMPID is company ID (there's two companys operating in one separated by these flags/values)

appcomp is which company app is running these are 0 (root admin) 1 (company 1)
2 (company 2)

so from what i can gather its filtering the what appears here depending on the corresponding flags the user has when logged in trucker is the only column where the data is different after importing to SQL 2008

This thing has swallowed a week of my life this company went dead because they were running a 2000 server, had nothing backed up...so now i'm reconstructing this evil thing and this is the last thing standing in my way

[honeybee]

I am still not able to understand why you are using -1 in the comparison.

Try replacing -1 with either a 0 or with 'false' and see if the query then runs. For e.g.

"Trucker=0"

or

"Trucker='false'"

.

[techgnome]

when it comes to a bit type, 0 = false, 1= true. So using -1 shouldn't work (but does because True is generally defined as !0 ... which is why we can usually get away with things like myboolVal = recordset.Recordcount ... if RecordCount is 0, myBoolVale is assigned False... if it contains anything else, it is assigned True.

My recommendation, if you want to check for false value in a bit field, use and explicit " = 0 " in the comparison. If you want true values, use an explicit not-zero comparison: " <> 0 "

strSQL = "Select [INDEX],NAME from SUPPLIER Where TRUCKER<>0 and COMPID = " & AppComp & " order by ID"

Ovoid bit confusion.

-tg

[addntox]

I didnt write the original program, and -1 worked in SQL2000, so god only knows. I've been brought in to get the app working. And since i've had little experience with VB6, i started out in .net there has been a learning curve...but i'm getting it. The plot thickens though i was able to get the comboboxes populate correctly setting it "=1" but here's the odd thing the -1 was returning the true state in the original program, so i dont know whether to just accept it and move on, or to figure out why the logic all seems backwards for my own peace of mind. Thanks for the help though guys i really appreciate it. I'm going to leave this unresolved for a little longer, the app is currently being put through its paces so i'm going to wait until they confirm it's fixed in operation i was working on a virtualization of their new domain as a safe environ, its on their main system today.

[techgnome]

-1 IS a true state... ANY non-Zero value IS A TRUE STATE VALUE... which is why I use the method I mentioned above... when looking for false I use =0 ... looking for true values, I use <>0 ... then it doesn't matter. Now... in VB... I think there was a change from VB6 to .NET... where False was (and still is) 0... but in VB6, I think True was -1, while in .NET it's defined as 1 ... I may have that backwards... but for SQL, a bit is a bit... it can be only 0 or 1... so I don't know...

-tg

[addntox]

ahh heheh i get it, so when the old program is sending -1 value to the new server running on the .net framework the server doesnt understand how to return that value, so its looping through the records and returning nothing. I can see why they changed it in .net. Much appreciated.