I am busy experimenting with ADODB as a means to write prepared scripts on customers' databases as it is infinitely better than batch scripting with sqlcmd.
Early on I noticed that sometimes ADODB results would differ from the results given by osql or sqlcmd and on further investigation I found that it always seemed to be in columns that hold a boolean value. If I run the following code I get -1:
declare @test bit
set @test=1
select top 1 @test
When logically it should return 1 as according to the MSDN and w3schools documentation the bit data type on SQL server holds either a 1, 0 or null.
Could anyone explain what is causing this and how to prevent it?
Will exporting information from my database and then importing the -1 rather than a 1 cause issues if the importing is also done with ADODB?
Demonstration Code
ConnectionString = "Provider=SQLNCLI11;"
ConnectionString = ConnectionString & "Server=localhost;"
ConnectionString = ConnectionString & "Database=master;"
ConnectionString = ConnectionString & "Trusted_Connection=yes;"
Dim Conn As Object
Set Conn = CreateObject("ADODB.Connection")
Conn.Open ConnectionString
Set Recordset = Conn.Execute("SELECT convert(bit, 1) as bit")
MsgBox Recordset.Fields("bit").Type, 0, "FieldType"
MsgBox VarType(Recordset.Fields("bit").Value),0,"VarType"
This shows that the SQL Server data type bit maps to 11 which is the enumerated values ADODB.DataTypeEnum.adBoolean https://msdn.microsoft.com/en-us/library/ms675318(v=vs.85).aspx
Which in turn corresponds to a varian of Vartype 11 (VariantType.vbBoolean) https://msdn.microsoft.com/en-us/library/32bbtt2s(v=vs.90).aspx So the bit Values 0 and 1 aren't getting directly translated to the numbers 0 and -1 by ADODB.
They're actually getting translated to False & True which in turn correspond to the integers 0 and -1 when you implicitly convert them to integers.
You can demonstrate this by issuing the following commands in the immediate window.
Debug.Print CInt(False)
Debug.Print CInt(True)
Quite where this leaves you in terms of practical advice I'm not sure but you should probably be treating these values as boolean variables in your code as opposed to numbers.
Luckily SQL Server will implictly convert -1 back to 1 on the database side so you don't need to worry about that
DECLARE @table table (b bit)
INSERT @table (b) VALUES(-1)
SELECT b from @table
Gives you
(1 row(s) affected)
b
-----
1
(1 row(s) affected)
Hopefully this resolved more confusion than it caused