none
Adding new columns to tables with calculated fields: Juneau rejects, T-Sql accepts.

    Question

  • An issue with adding columns to tables, if the tables already have calculated columns: I can add a column easily to such a table using one line of t-sql, but Juneau (its table designer) rejects it with this message after clicking "Update Database":

    Error SQL72014 .Net SqlClient Data Provider: Msg 271, Level 16, State 1, Procedure sp_refreshsqlmodule_internal, Line 75 The column "fldCalc" cannot be modified because it is either a computed column or is the result of a UNION operator.

    Note that fldCalc is NOT the column that I am adding (and I am not altering it in any way).  Thanks.

     



    Wednesday, August 24, 2011 12:49 AM

Answers

All replies

  • I don't know what is happening, and I don't have the time to try it now, but it would be interesting to look at the script that Juneau generates. That could give more details what goes wrong. The error message comes from SQL Server - it may not necessarily be Juneau that is at fault.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, August 24, 2011 9:55 PM
  • It gets worse: trying to make the same change I mentioned in the first post, on the same database, but from a networked location caused Juneau to fail to perform the update without any feedback at all (no error message or any indication of failure).  It went to a place from which none return.  

    As far as the script, I don't see anything off, but there are a lot of calls being taken to sp_refreshsqlmodule, and it's failing on one of them.  One possibility is that one has to do something special to purify the database for juneau, but it passes all integrity checks I know of. 

    I think in this case you may be right it's a broader sql server issue, since Juneau and SSMS are both choking on a simple add column (SSMS with an exception); only t-sql is letting me do it.  Of course in SSMS you now get the problem that you have to exit SSMS on such a failure, otherwise it will never ever forget the column it tried to add (but which does not exist).   Juneau also has (milder) "refresh" issues: e.g. it sometimes tries to create the same primary key on a table twice, or thought it was doing that (and sometimes reversed the order of multi-column primary keys).  Even I start to lose track of what's real and what's not.

    (As a sidenote they may be supposing a worst case (e.g. assuming "SELECT *" references to the table elsewhere) and wanting to unbind and rebind all schemabindings (is there even a need to unbind when no columns in the view definition are affected?), or refresh metadata where I think they don't need to.  On the other hand, that should not do any harm, though it does make what would otherwise be a simple change more involved.)

    The problems in table designer are still too quickly discoverable at least in ctp3 for me to spend a lot of time on; I mean, I assume development is aware of them.  I like the organization and design of Juneau, and it's aiming for a cleaner more foolproof operation than ssms, but the table designer still has issues. 

     

    Thursday, August 25, 2011 8:00 PM
  • As far as the script, I don't see anything off, but there are a lot of calls being taken to sp_refreshsqlmodule,

    Presumably to keep dependeny information up to date.

    I think in this case you may be right it's a broader sql server issue, since Juneau and SSMS are both choking on a simple add column (SSMS with an exception); only t-sql is letting me do it.  

    Because if you run a simple ALTER TABLE, you don't call sp_refreshsqlmodule. (And there would be no need to do this, unless you drop and recreate the table.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, August 25, 2011 9:40 PM
  • Thanks,  added it as bug to connect (since a simple and unremarkable table design change should go through, even if it thinks it has to call sp_refreshsqlmodule):

    https://connect.microsoft.com/SQLServer/feedback/details/687301/adding-new-columns-to-tables-with-calculated-fields-generates-sp-refreshsqlmodule-internal-error

     

    Monday, September 05, 2011 9:41 PM