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

Any help is greatly appreciated.

Uwe K.
Datek Wireless A/S
Norway

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 K.
Datek Wireless AS
Norway

On 10/23/07, Uwe K. [email protected] wrote:

Hi again!

My deepest apologies for answering my own post. However, I would like
to share the solution to my problem:

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!


Rick DeNatale

My blog on Ruby
http://talklikeaduck.denhaven2.com/

Rick Denatale wrote:

On 10/23/07, Uwe K. [email protected] wrote:
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.

I completely agree with Rick, I’ve been looking at get an application
setup using SQL Server and was encountering the same issue but couldn’t
find much information on the solution, until I found your post. So
thanks for saving me a lot of time and effort Uwe!!

Regards,
iRubyist