Forum: Ruby on Rails problems with migrations in sql server

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Cb6f29d740e3a8d7f407ca335031bd32?d=identicon&s=25 Steve Eichert (Guest)
on 2006-01-19 06:01
(Received via mailing list)
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:

1) Why would the schema_info table be created multiple times (at least
attempted?).
2) Why isn't an error be shown after I run rake migrate if the migration
is
failing?  Where can I look for more details?
3) 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
  SQL (0.000000)   DBI::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)
  SQL (0.000000)   DBI::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)
  SQL (0.000000)   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'
  SQL (0.000000)   SELECT TOP 1 version FROM
schema_info
  SQL (0.000000)   SELECT TOP 1 version FROM
schema_info
Migrating to AddFooTable (2)
  SQL (0.000000)   CREATE TABLE Foo ([id] int NOT NULL
IDENTITY(1, 1) PRIMARY KEY, [Name] varchar(50)) 
  SQL (0.000000)   SELECT @@ROWCOUNT AS
AffectedRows
  SQL (0.000000)   UPDATE schema_info SET version =
2
  SQL (0.000000)   SELECT TOP 1 * FROM
schema_info
  SQL (0.000000)   SELECT table_name from
information_schema.tables WHERE table_type = 'BASE TABLE'
  SQL (0.020000)   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'
  SQL (0.000000)   EXEC sp_helpindex Foo
Cb6f29d740e3a8d7f407ca335031bd32?d=identicon&s=25 Steve Eichert (Guest)
on 2006-01-19 18:27
(Received via mailing list)
Note: Tried sending this to the list without success.  Forgive me if it
comes through more then once.

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:

1) Why would the schema_info table be created multiple times (at least
attempted?).
2) Why isn't an error be shown after I run rake migrate if the migration
is
failing?  Where can I look for more details?
3) 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
  SQL (0.000000)   DBI::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)
  SQL (0.000000)   DBI::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)
  SQL (0.000000)   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'
  SQL (0.000000)   SELECT TOP 1 version FROM
schema_info
  SQL (0.000000)   SELECT TOP 1 version FROM
schema_info
Migrating to AddFooTable (2)
  SQL (0.000000)   CREATE TABLE Foo ([id] int NOT NULL
IDENTITY(1, 1) PRIMARY KEY, [Name] varchar(50)) 
  SQL ( 0.000000)   SELECT @@ROWCOUNT AS
AffectedRows
  SQL (0.000000)   UPDATE schema_info SET version =
2
  SQL (0.000000)   SELECT TOP 1 * FROM
schema_info
  SQL (0.000000)   SELECT table_name from
information_schema.tables WHERE table_type = 'BASE TABLE'
  SQL (0.020000)   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'
  SQL (0.000000)   EXEC sp_helpindex Foo
7a966bb1534f0ea863bc9ffe65ae449f?d=identicon&s=25 Jamie Orchard-hays (jamieorc)
on 2006-01-19 18:33
(Received via mailing list)
I've used migrations successfully with SQL Server. I don't see what
is causing you trouble here. Have you dropped the database, recreated
it and then run migrations?

Jamie
Cb6f29d740e3a8d7f407ca335031bd32?d=identicon&s=25 Steve Eichert (Guest)
on 2006-01-19 19:09
(Received via mailing list)
I think the problem is actually do to the fact that I have the following
in
my environment.rb file:

config.active_record.schema_format = :ruby

If I comment out this line I can run migrations.  If I try and do a
db_schema_dump after doing my initial db_schema_import things crap out
so
I'm guessing there's something with the way the database was created via
the
db_schema_dump initially that is causing the problem.

As I find out more information about where and what is cuasing the
problems
I'll probably return to the list to find out why :)

- Steve
This topic is locked and can not be replied to.