# Problems with migrations in sql server

**URL:** <https://www.ruby-forum.com/t/problems-with-migrations-in-sql-server/62768>\
**Category:** Rails\
**Created:** [January 19, 2006, 6:01am UTC](https://www.ruby-forum.com/t/problems-with-migrations-in-sql-server/62768 "2006-01-19T06:01:59Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![stenzer](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/fbc32d/32.png) [@stenzer](https://www.ruby-forum.com/u/stenzer)\
**Post date:** [January 19, 2006, 6:01am UTC](https://www.ruby-forum.com/t/problems-with-migrations-in-sql-server/62768/1 "2006-01-19T06:01:59Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![stenzer](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/fbc32d/32.png) [@stenzer](https://www.ruby-forum.com/u/stenzer)\
**Post date:** [January 19, 2006, 6:27pm UTC](https://www.ruby-forum.com/t/problems-with-migrations-in-sql-server/62768/2 "2006-01-19T18:27:34Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![stenzer](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/fbc32d/32.png) [@stenzer](https://www.ruby-forum.com/u/stenzer)\
**Post date:** [January 19, 2006, 6:33pm UTC](https://www.ruby-forum.com/t/problems-with-migrations-in-sql-server/62768/3 "2006-01-19T18:33:37Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![stenzer](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/fbc32d/32.png) [@stenzer](https://www.ruby-forum.com/u/stenzer)\
**Post date:** [January 19, 2006, 7:09pm UTC](https://www.ruby-forum.com/t/problems-with-migrations-in-sql-server/62768/4 "2006-01-19T19:09:58Z")

</div>

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