SQL Server migration result differs depending on client

rubyonrails-talk
1

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](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]