How can we help you today? How can we help you today?

Error Applying Changes from Get Latest

Hello,

When trying to do a Get Latest to apply some recent changes to a view in my database, I am getting an error message involving a different view. This is likely because the database has a large number of dependent views. The changes I am trying to apply with a Get Latest are changes to a view named vwPARENTS. When I try to do a Get Latest on that view, I get the following error message in the "Applying changes to database" step:

Column names in each view or function must be unique. column name 'EMAIL_ID' in view or function 'vwEMAILS_INTERACTIONS" is specified more than once.

Upon reviewing the code for vwEMAILS_INTERACTIONS, the EMAIL_ID column is used in a UNION ALL query, so it IS mentioned twice, but only once in each part of the UNION statement. The vwEMAILS_INTERACTIONS view seems to be working properly, so I am suspecting this may be a bug in SQL Source Control.

Please let me know if there is more information I can provide on this error.

Thanks again,
jfennell
0

Comments

2 comments

Sort by
  • jfennell
    Hi, I think I have tracked this problem down to a problem in a view referenced in the vwEMAILS_INTERACTIONS view that's causing that view not to build properly when SQL Source Control executes its update script.

    Now that the Beta version (which I just installed) allows access to the actual SQL script that it's executing when trying to do the updates (THANK YOU, THANK YOU, THANK YOU!!), I was able to narrow down the problem to the following statement:
    EXEC sp_refreshview N'[dbo].[vwEMAILS_INTERACTIONS]

    Since the view that was being modified by the SQL Source Control "Get Latest" is one of the views underlying vwEMAILS_INTERACTIONS, SQL Source Control tries to refresh it. Unfortunately, there is currently an error in the code for the vwEMAIL_INTERACTIONS view (caused by a different underlying view in the select statement of vwEMAIL_INTERACTIONS). That error causes the sp_refreshview call to fail, which causes the "Get Latest" update to fail and roll back.

    So it looks like SQL Source Control is functioning fine, including rolling back when it encountered the error. In fact, I guess you could say it functioned better than fine since it caught an error that apparently wasn't caught by one of our developers. :oops:

    Now on to a fix. I'll comment on the post again once it's all completely resolved, but I'm pretty certain that SQL Source Control is not to blame here.
    jfennell
    0
  • David Atkinson
    Glad that the feature to let you see the offending script assisted you in finding a bug in your code!

    One of the features we hope to put in SQL Prompt 5 is a tool to list all invalid objects in your database. Invalid objects are those that reference objects that don't exist, or have been renamed, hence rendering the object broken. This feature will allow developers to validate their schemas *before* committing to source control, reducing the likelihood of the repo being in an invalid state. Another idea is to highlight (in red?) any invalid objects in your Commit list, but this feature is certainly not going to make the cut for v1.0!

    David Atkinson
    Product Manager
    David Atkinson
    0

Add comment

Please sign in to leave a comment.