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 
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](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[password] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[enabled] [bit] NOT NULL,
[firstName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL DEFAULT (NULL),
[lastName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL DEFAULT (NULL),
[email] [varchar](50) 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](40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL DEFAULT (NULL),
[salt] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL DEFAULT (NULL),
[security_token] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL DEFAULT (NULL),
[token_expiry] [datetime] 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]
Hi again!
My deepest apologies for answering my own post. However, I would like to share the solution to my problem:
There is an option in MS SQL Server 2007 (9.0.2047), "ANSI NULL default", that sets the default nullable state for new columns. This option is default set to "false" which sets new columns to "NOT NULL" if not specified otherwise.
When using "Microsoft SQL Server Management Studio", or the RadRails Query view in the Data perspective (uses JDBC) to run DDL, this option is ignored.
Running the same exact DDL using ActiveRecord::Base.connection.execute the option is honored, and the resulting table columns are set to "NOT NULL" when not specified.
Setting the MS SQL Server option to "true" solved the problem.
The problem can also be solved by specifying "NULL" in the DDL. This however must be done in the sqlserver adapter.
Sorry for the noise.
Uwe Kubosch Datek Wireless AS Norway
No apologies necessary. When someone solves their own problem and keeps it to himself, only he learns from the experience.
When you answer your own question on a mailing list. We all learn.
Thanks!