SQL Server migration result differs depending on client

Hi all!

I hope somebody can help me. I have very little hair left, and it is
decreasing more rapidly the longer I have this problem :slight_smile:

I am connecting to a MS SQL Server 9.0.2047 from Fedora 7 linux, using
freetds, unixodbc and rubyodbc. Most things work fine, just not the
following:

I have a schema/migration like this:

create_table “users”, :force => true do |t|
t.column “username”, :string, :limit => 50,
:null => false
t.column “password”, :string, :limit => 50,
:null => false
t.column “enabled”, :boolean,
:null => false
t.column “firstName”, :string, :limit => 50
t.column “lastName”, :string, :limit => 50
t.column “email”, :string, :limit => 50
t.column “userService”, :integer
t.column “accessProfile_id”, :integer
t.column “salted_password”, :string, :limit => 40
t.column “salt”, :string, :limit => 40
t.column “security_token”, :string, :limit => 40
t.column “token_expiry”, :datetime
t.column “deleted”, :boolean, :default =>
false, :null => false
t.column “verified”, :boolean, :default =>
false
end

When I run db:schema:load using the sqlserver adapter I get the
following DDL (just added newlines for readability):

DROP TABLE users
CREATE TABLE users (
[id] int NOT NULL IDENTITY(1, 1) PRIMARY KEY,
[username] varchar(50) NOT NULL,
[password] varchar(50) NOT NULL,
[enabled] bit NOT NULL,
[firstName] varchar(50) DEFAULT NULL,
[lastName] varchar(50) DEFAULT NULL,
[email] varchar(50) DEFAULT NULL,
[userService] integer DEFAULT NULL,
[accessProfile_id] integer DEFAULT NULL,
[salted_password] varchar(40) DEFAULT NULL,
[salt] varchar(40) DEFAULT NULL,
[security_token] varchar(40) DEFAULT NULL,
[token_expiry] datetime DEFAULT NULL,
[deleted] bit DEFAULT 0 NOT NULL,
[verified] bit DEFAULT 0
)

Running this using the SQL Server Management Studio gives the wanted
result. Running the same DDL using a migration script either using the
migration statements above or using an “execute” call, gives the wrong
result. A dump of the table after running using ActiveRecord looks like
this:

/****** Object: Table [dbo].[users] Script Date: 10/22/2007 13:03:30
******/
CREATE TABLE [dbo].[users](
[id] [int] IDENTITY(1,1) NOT NULL,
[username] varchar COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL,
[password] varchar COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL,
[enabled] [bit] NOT NULL,
[firstName] varchar COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL DEFAULT (NULL),
[lastName] varchar COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
DEFAULT (NULL),
[email] varchar COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
DEFAULT (NULL),
[userService] [int] NOT NULL DEFAULT (NULL),
[accessProfile_id] [int] NOT NULL DEFAULT (NULL),
[salted_password] varchar COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL DEFAULT (NULL),
[salt] varchar COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
DEFAULT (NULL),
[security_token] varchar COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL DEFAULT (NULL),
[token_expiry] NOT NULL DEFAULT (NULL),
[deleted] [bit] NOT NULL DEFAULT ((0)),
[verified] [bit] NOT NULL DEFAULT ((0)),
PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]


Notice the change of NULL to NOT NULL.


What am I doing wrong? I dump the SQL in the
file /usr/lib/ruby/site_ruby/1.8/DBD/ODBC/ODBC.rb