Error when changing datatype of column in SQL Server DB - Java

Question

I made a query in Java which changes one column's datatype in another. It works fine until it tries to change type. It finds all columns in DB with specified datatype, but cannt change it.

Here is my code:

        st = conn.createStatement();
        rs = st.executeQuery("SELECT a.name as ColName, o.name AS TableName"
                + "FROM sys.syscolumns AS a"
                + "INNER JOIN sys.systypes AS b ON a.xtype = b.xtype AND b.name = 'char' AND a.length = 255"
                + "INNER JOIN sys.objects AS o ON a.id = o.object_id WHERE (o.type = 'u') AND (o.schema_id = 1)");
        ResultSet rsPom;
        while (rs.next()){
            String tName=rs.getString("TableName");
            String cName=rs.getString("ColName");
            System.out.println(tName+"  "+cName);
            rsPom=st.executeQuery("ALTER TABLE "+ tName+" ALTER COLUMN "+cName+" nvarchar(255)");
        }

And here is my Exception:

com.microsoft.sqlserver.jdbc.SQLServerException: The object 'CK_TimeInstant_frame_default' is dependent on column 'frame'.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1515)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:792)
at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:689)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeQuery(SQLServerStatement.java:616)
at DataTypeChanger.changeDataType(DataTypeChanger.java:50)
at DataTypeChanger.main(DataTypeChanger.java:36)

Does anyone knows what is all about, and what can I do?

Your alter script isn't taking any action with respect to constraints. Columns which are the target of constraints (Unique, Foreign Key, Default, etc) can't be modified unless the constraint is first dropped. In which case you'll probably need to add the constraints back afterwards. — StuartLC
– StuartLC, Commented Apr 29, 2014 at 17:26
@StuartLC I did it all, and works fine. Thanks. But now I have other problem. It changes some columns, not all. Link to my question is stackoverflow.com/questions/23366393/… , because here would be long question. If You mind. Thanks. — Aleksandar
– Aleksandar, Commented Apr 30, 2014 at 15:36

StuartLC · Accepted Answer · 2014-04-29 18:41:25Z

Firstly, my apologies about the sarky comment above.

The reason you receive this error is because your alter script isn't taking any action with respect to constraints. Columns which are the target of constraints (Unique, Foreign Key, Default, etc) can't be modified unless the constraint is first dropped. ~~In which case you'll probably need to add the constraints back afterwards.~~

I've assumed your earlier (deleted) comment still holds, viz that you do not require to create the constraints again after they have been dropped.

Re : How do I drop all constraints

Disclaimer : Back up your database before you try this, but the following MIGHT work. It is a destructive one way operation.

declare @sql nvarchar(2000);
while(exists(select 1 from sys.objects WHERE type_desc LIKE '%CONSTRAINT%'))
begin
    BEGIN TRY
      SELECT TOP 1 @sql=('ALTER TABLE ' + SCHEMA_NAME(schema_id) + '.[' + OBJECT_NAME(parent_object_id)
      + '] DROP CONSTRAINT [' + OBJECT_NAME(OBJECT_ID) + ']')
      FROM sys.objects
      WHERE type_desc LIKE '%CONSTRAINT%'
      ORDER BY NEWID();
      exec (@sql);
    END TRY
    BEGIN CATCH
    END CATCH
end;
GO

Rationale: The TRY CATCH is required because there is no guarantee that we will get the order of dependencies correct when dropping constraints (e.g. FK dependent on PK), so we basically squash the error and try drop another random constraint (ORDER BY NEWID())

Reference : Based on this query here, but extended to all constraints

SqlFiddle Here

Collectives™ on Stack Overflow

Error when changing datatype of column in SQL Server DB - Java

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related