Red Gate forums :: View topic - Error Applying Changes from Get Latest
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Source Control EAP
SQL Source Control EAP forum

Error Applying Changes from Get Latest

Search in SQL Source Control EAP forum
This forum is locked: you cannot post, reply to, or edit topics.   This topic is locked: you cannot edit posts or make replies.
Jump to:  
Author Message
jfennell



Joined: 01 Apr 2010
Posts: 32

PostPosted: Wed May 19, 2010 5:17 pm    Post subject: Error Applying Changes from Get Latest Reply with quote

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,
_________________
~ Jim Fennell
Back to top
View user's profile Send private message
jfennell



Joined: 01 Apr 2010
Posts: 32

PostPosted: Thu May 20, 2010 9:35 pm    Post subject: Reply with quote

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:

Code:
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. Embarassed

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.
_________________
~ Jim Fennell
Back to top
View user's profile Send private message
David Atkinson



Joined: 05 Dec 2005
Posts: 1120
Location: Twitter: @dtabase

PostPosted: Thu May 20, 2010 10:34 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
Display posts from previous:   
This topic is locked: you cannot edit posts or make replies. All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group