# SQL Server migration result differs depending on client **URL:** \ **Category:** Rails\ **Created:** [October 23, 2007, 12:08pm UTC](https://www.ruby-forum.com/t/sql-server-migration-result-differs-depending-on-client/118022 "2007-10-23T12:08:36Z")\ **Posts on this page:** 4\ **Page:** 1
**Author:** ![donv](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/0ea827/32.png) [@donv](https://www.ruby-forum.com/u/donv)\ **Post date:** [October 23, 2007, 12:08pm UTC](https://www.ruby-forum.com/t/sql-server-migration-result-differs-depending-on-client/118022/1 "2007-10-23T12:08:36Z")
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 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 ---
**Author:** ![donv](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/0ea827/32.png) [@donv](https://www.ruby-forum.com/u/donv)\ **Post date:** [October 23, 2007, 1:55pm UTC](https://www.ruby-forum.com/t/sql-server-migration-result-differs-depending-on-client/118022/2 "2007-10-23T13:55:53Z")
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 ---
**Author:** ![donv](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/0ea827/32.png) [@donv](https://www.ruby-forum.com/u/donv)\ **Post date:** [October 23, 2007, 2:29pm UTC](https://www.ruby-forum.com/t/sql-server-migration-result-differs-depending-on-client/118022/3 "2007-10-23T14:29:13Z")
On 10/23/07, Uwe K. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) 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/](http://talklikeaduck.denhaven2.com/) ---
**Author:** ![donv](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/0ea827/32.png) [@donv](https://www.ruby-forum.com/u/donv)\ **Post date:** [October 30, 2007, 11:57am UTC](https://www.ruby-forum.com/t/sql-server-migration-result-differs-depending-on-client/118022/4 "2007-10-30T11:57:35Z")
Rick Denatale wrote: > On 10/23/07, Uwe K. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) 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