smallmoney data type in sql server

General Discussion
1

Hello all,
    I have an initial changelog generated (using generateChangeLog command) from our production sqlserver database and I am trying to run this against a new local sqlserver instance using the update command.  Here’s the problem:  The production db contains a table that has a column with the smallmoney datatype.  The generateChangeLog command did not see this as an invalid datatype but the update command is complaining that it IS invalid.  Is this is known issue or is this something I should file a JIRA for?  Any help would be greatly appreciated!

BTW: I’m sorry I missed out on the chat this afternoon.  Thanks for posting the transcript!

-Ryan

2

Are you getting a stacktrace error you could post?  If not, run liquibase with a logLevel=FINEST.  It is not a known issue, if you could file a jira issue, that would be great.

Nathan

3

Sure thing, here’s the stack trace from running update on my initial changelog.  I’ll post this to JIRA in a moment.

Migration Failed: Error executing SQL CREATE TABLE [dbo].[yit] ([id] VARCHAR(50), [famly] INT, [child] smallint, [startdate] DATETIME,
[enddate] DATETIME, [district_code] CHAR(1), [caseworker_id] VARCHAR(50), [client_contribution] SMALLMONEY(10,4), [contribution_start]
DATETIME, [daily_rate] smallmoney(10,4), [payeeId] INT, [agreements] TEXT, [created] DATETIME, [createdby] VARCHAR(50), [modified] DATE
TIME, [modifiedby] VARCHAR(50), [denied] DATETIME, [deniedby] VARCHAR(50), [approved] DATET
IME, [approvedby] VARCHAR(50), [terminated] DATETIME, [terminatedby] VARCHAR(50), [type] smallint).  For more
information, use the --logLevel flag)
Jun 25, 2009 3:59:36 PM liquibase.commandline.Main main
SEVERE: Error executing SQL CREATE TABLE [dbo].[yit] ([id] VARCHAR(50), [famly] INT, [child] smallint, [startdate] DATETIME, [enddate]
DATETIME, [district_code] CHAR(1), [caseworker_id] VARCHAR(50), [client_contribution] SMALLMONEY(10,4), [contribution_start] DATETIME,
[daily_rate] smallmoney(10,4), [payeeId] INT, [agreements] TEXT, [created] DATETIME, [createdby] VARCHAR(50), [modified] DATETIME, [mod
ifiedby] VARCHAR(50), [denied] DATETIME, [deniedby] VARCHAR(50), [approved] DATETIME, [appr
ovedby] VARCHAR(50), [terminated] DATETIME, [terminatedby] VARCHAR(50), [type] smallint)
liquibase.exception.MigrationFailedException: Migration failed for change set CHANGELOGS/SRSDSS.root.changelog.xml::1245943587171-146::
ryan.connolly (generated):
    Reason: liquibase.exception.JDBCException: Error executing SQL CREATE TABLE [dbo].[yit] ([id] VARCHAR(50), [famly] INT, [child] sm
allint, [startdate] DATETIME, [enddate] DATETIME, [district_code] CHAR(1), [caseworker_id] VARCHAR(50), [client_contribution] SMALLMONE
Y(10,4), [contribution_start] DATETIME, [daily_rate] smallmoney(10,4), [payeeId] INT, [agreements] TEXT, [created] DATETIME, [createdby
] VARCHAR(50), [modified] DATETIME, [modifiedby] VARCHAR(50), [denied] DATETIME, [deniedby] VARCHAR(50), [approved] DATETIME, [approvedby] VARCHAR(50), [terminated] DATETIME, [terminatedby] VARCHAR(50)
, [type] smallint):
          Caused By: Error executing SQL CREATE TABLE [dbo].[yit] ([id] VARCHAR(50), [famly] INT, [child] smallint, [startdate] DATETIM
E, [enddate] DATETIME, [district_code] CHAR(1), [caseworker_id] VARCHAR(50), [client_contribution] SMALLMONEY(10,4), [contribution_star
t] DATETIME, [daily_rate] smallmoney(10,4), [payeeId] INT, [agreements] TEXT, [created] DATETIME, [createdby] VARCHAR(50), [modified] D
ATETIME, [modifiedby] VARCHAR(50), [denied] DATETIME, [deniedby] VARCHAR(50), [approved] DA
TETIME, [approvedby] VARCHAR(50), [terminated] DATETIME, [terminatedby] VARCHAR(50), [type] smallint):
        Caused By: Parameter or variable ‘client_contribution’ has an invalid data type.
        at liquibase.ChangeSet.execute(ChangeSet.java:260)
        at liquibase.parser.visitor.UpdateVisitor.visit(UpdateVisitor.java:26)
        at liquibase.parser.ChangeLogIterator.run(ChangeLogIterator.java:41)
        at liquibase.Liquibase.update(Liquibase.java:112)
        at liquibase.commandline.Main.doMigration(Main.java:650)
        at liquibase.commandline.Main.main(Main.java:97)
Caused by: liquibase.exception.JDBCException: Error executing SQL CREATE TABLE [dbo].[yit] ([id] VARCHAR(50), [famly] INT, [child] smal
lint, [startdate] DATETIME, [enddate] DATETIME, [district_code] CHAR(1), [caseworker_id] VARCHAR(50), [client_contribution] SMALLMONEY(
10,4), [contribution_start] DATETIME, [daily_rate] smallmoney(10,4), [payeeId] INT, [agreements] TEXT, [created] DATETIME, [createdby]
VARCHAR(50), [modified] DATETIME, [modifiedby] VARCHAR(50), [isdenied] smallint, [denied] DATETIME, [deniedby] VARCHAR(50), [isapproved
] smallint, [approved] DATETIME, [approvedby] VARCHAR(50), [isterminated] smallint, [terminated] DATETIME, [terminatedby] VARCHAR(50),
[type] smallint)
        at liquibase.database.template.JdbcTemplate.execute(JdbcTemplate.java:55)
        at liquibase.database.template.JdbcTemplate.execute(JdbcTemplate.java:86)
        at liquibase.change.AbstractChange.execute(AbstractChange.java:247)
        at liquibase.change.AbstractChange.executeStatements(AbstractChange.java:93)
        at liquibase.ChangeSet.execute(ChangeSet.java:236)
        … 5 more
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Parameter or variable ‘client_contribution’ has an invalid data type.
        at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown Source)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(Unknown Source)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(Unknown Source)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(Unknown Source)
        at com.microsoft.sqlserver.jdbc.TDSCommand.execute(Unknown Source)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(Unknown Source)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(Unknown Source)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(Unknown Source)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.execute(Unknown Source)
        at liquibase.database.template.JdbcTemplate$1ExecuteStatementCallback.doInStatement(JdbcTemplate.java:78)
        at liquibase.database.template.JdbcTemplate.execute(JdbcTemplate.java:48)
        … 9 more

4

Nathan:
    Looking into this a little closer, it appears that Liquibase is adding params to the smallmoney datatype wherein there should not be any:  lb is generating the column definition as smallmoney(10,4) NULL  when SQLServer expects the definition to be smallmoney NULL.  I hope this helps.  Is there anything I can do to help track this down?

Thanks!
-Ryan

5

That is all I needed, I put a snapshot of 1.9 at http://www.liquibase.org/liquibase-SNAPSHOT.jar.  Could you let me know if that fixes the problem?  It had been handling “MONEY” but not “SMALLMONEY”

Nathan

6

Hey, Nathan… that SNAPSHOT isn’t packaged (just compiled) like a distribution so I’m not quite sure how to run this… I’ve been simply updating my LIQUIBASE_HOME environment variable to toggle between versions.  I’d be more than happy to give this a go if you could post one w/ the bat file, jar, etc.

Thanks!

7

I uploaded www.liquibase.org/liquibase-1.9.4.zip, give it a try.

Nathan

8

Works like a charm, Nathan.  Thanks alot.  Is this going to be in a 1.9.5 release?

9

Good to hear.  Thanks for checking it out. 

It will be in the 1.9.5 release (not date scheduled yet) as well as the 2.0 release (late summer)