Is anyone using migrations with a SQL Server database who might be able
to
lend me a hand? I’ve used migrations with MySql in the past and
haven’t
run into any issues so forgive When I run rake migrate nothing is
actually
getting updated in my database. I created a migration using
./script/generate migration AddFooTable and updated the migration to
look as
follows:
class AddFooTable < ActiveRecord::Migration
def self.up
create_table “Foo” do |t|
t.column “Name”, :string, :limit => 50
end
end
def self.down
drop_table “Foo”
end
end
When I run rake migrate the database doesn’t get updated. My new table
doesn’t show up and my schema_info version doesn’t get updated. I have
a
couple questions:
- Why would the schema_info table be created multiple times (at least
attempted?). - Why isn’t an error be shown after I run rake migrate if the migration
is
failing? Where can I look for more details? - Does anyone have any ideas as to what might be going wrong?
FYI, I have the following in my environment.rb file: ActiveRecord::
Base.connection.instance_variable_get("@connection")[“AutoCommit”] =
false
The trace when I run rake migrate --trace is as follows:
** Invoke migrate (first_time)
** Invoke environment (first_time)
** Execute environment
** Execute migrate
** Invoke db_schema_dump (first_time)
** Invoke environment
** Execute db_schema_dump
The following SQL is executed:
Audit Login – network protocol: TCP/IP
set quoted_identifier on
set implicit_transactions off
set cursor_close_on_commit off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set language us_english
set dateformat mdy
set datefirst 7
54
SQL:BatchCompleted set implicit_transactions on 54
SQL:BatchCompleted SELECT 1 54
SQL:BatchCompleted CREATE TABLE schema_info (version int) 54
SQL:BatchCompleted select * from CREATE TABLE schema_info (version
int) 54
SQL:BatchCompleted CREATE TABLE schema_info (version int) 54
SQL:BatchCompleted select * from CREATE TABLE schema_info (version
int) 54
SQL:BatchCompleted CREATE TABLE schema_info (version int) 54
SQL:BatchCompleted select * from CREATE TABLE schema_info (version
int) 54
SQL:BatchCompleted CREATE TABLE schema_info (version int) 54
SQL:BatchCompleted select * from CREATE TABLE schema_info (version
int) 54
SQL:BatchCompleted SELECT COLUMN_NAME as ColName, COLUMN_DEFAULT as
DefaultValue, DATA_TYPE as ColType, COL_LENGTH(‘schema_info’,
COLUMN_NAME)
as Length, COLUMNPROPERTY(OBJECT_ID(‘schema_info’), COLUMN_NAME,
‘IsIdentity’) as IsIdentity, NUMERIC_SCALE as Scale FROM
INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ‘schema_info’ 54
SQL:BatchCompleted SELECT TOP 1 version FROM schema_info 54
SQL:BatchCompleted SELECT TOP 1 version FROM schema_info 54
SQL:BatchCompleted CREATE TABLE Foo ([id] int NOT NULL IDENTITY(1, 1)
PRIMARY KEY, [Name] varchar(50)) 54
SQL:BatchCompleted UPDATE schema_info SET version = 2 54
SQL:BatchCompleted SELECT @@ROWCOUNT AS AffectedRows 54
SQL:BatchCompleted SELECT TOP 1 * FROM schema_info 54
SQL:BatchCompleted SELECT table_name from information_schema.tables
WHERE
table_type = ‘BASE TABLE’ 54
SQL:BatchCompleted SELECT COLUMN_NAME as ColName, COLUMN_DEFAULT as
DefaultValue, DATA_TYPE as ColType, COL_LENGTH(‘Foo’, COLUMN_NAME) as
Length, COLUMNPROPERTY(OBJECT_ID(‘Foo’), COLUMN_NAME, ‘IsIdentity’) as
IsIdentity, NUMERIC_SCALE as Scale FROM INFORMATION_SCHEMA.COLUMNS WHERE
TABLE_NAME = ‘Foo’ 54
SQL:BatchCompleted EXEC sp_helpindex Foo 54
SQL:BatchCompleted IF @@TRANCOUNT > 0 ROLLBACK TRAN 54
And finally the log file contains the following:
Logfile created on Wed Jan 18 23:10:19 Eastern Standard Time 2006 by
logger.rb/1.5.2.4
e[4;36;1mSQL (0.000000)e[0m e[0;1mDBI::DatabaseError: Execute
OLE error code:80040E14 in Microsoft OLE DB Provider for SQL Server
There is already an object named ‘schema_info’ in the database.
HRESULT error code:0x80020009
Exception occurred.: CREATE TABLE schema_info (version int)e[0m
e[4;35;1mSQL (0.000000)e[0m e[0mDBI::DatabaseError: Execute
OLE error code:80040E14 in Microsoft OLE DB Provider for SQL Server
There is already an object named ‘schema_info’ in the database.
HRESULT error code:0x80020009
Exception occurred.: CREATE TABLE schema_info (version int)e[0m
e[4;36;1mSQL (0.000000)e[0m e[0;1mSELECT COLUMN_NAME as ColName,
COLUMN_DEFAULT as DefaultValue, DATA_TYPE as ColType,
COL_LENGTH(‘schema_info’, COLUMN_NAME) as Length,
COLUMNPROPERTY(OBJECT_ID(‘schema_info’), COLUMN_NAME, ‘IsIdentity’) as
IsIdentity, NUMERIC_SCALE as Scale FROM INFORMATION_SCHEMA.COLUMNS WHERE
TABLE_NAME = 'schema_info’e[0m
e[4;35;1mSQL (0.000000)e[0m e[0mSELECT TOP 1 version FROM
schema_infoe[0m
e[4;36;1mSQL (0.000000)e[0m e[0;1mSELECT TOP 1 version FROM
schema_infoe[0m
Migrating to AddFooTable (2)
e[4;35;1mSQL (0.000000)e[0m e[0mCREATE TABLE Foo ([id] int NOT NULL
IDENTITY(1, 1) PRIMARY KEY, [Name] varchar(50)) e[0m
e[4;36;1mSQL (0.000000)e[0m e[0;1mSELECT @@ROWCOUNT AS
AffectedRowse[0m
e[4;35;1mSQL (0.000000)e[0m e[0mUPDATE schema_info SET version =
2e[0m
e[4;36;1mSQL (0.000000)e[0m e[0;1mSELECT TOP 1 * FROM
schema_infoe[0m
e[4;35;1mSQL (0.000000)e[0m e[0mSELECT table_name from
information_schema.tables WHERE table_type = 'BASE TABLE’e[0m
e[4;36;1mSQL (0.020000)e[0m e[0;1mSELECT COLUMN_NAME as ColName,
COLUMN_DEFAULT as DefaultValue, DATA_TYPE as ColType, COL_LENGTH(‘Foo’,
COLUMN_NAME) as Length, COLUMNPROPERTY(OBJECT_ID(‘Foo’), COLUMN_NAME,
‘IsIdentity’) as IsIdentity, NUMERIC_SCALE as Scale FROM
INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Foo’e[0m
e[4;35;1mSQL (0.000000)e[0m e[0mEXEC sp_helpindex Fooe[0m
