SQL View not showing as changed

TroyHusker

I'm using SQL 2000 and I added a field to a table. But I also had to recompile a view that includes all of the fields of that table using the "*" to get all fields because it wasn't picking up the new field.

When I ran SQL Compare, it didn't see the view as having been changed and therefore didn't update the other database that was the recipient of the original changes. Is there some sort of setting I need to select so that it can pick up those view changes?

Hopefully I'm making a little bit of sense. I'm not sure how else to explain it as I'm not very technical in my speach.

Thanks so much for a great product!

Brian Donahue

Hello Troy,

The view doesn't need to be changed, really, because it's still a SELECT * FROM... in both databases. But it does need to be recompiled so that internally, SQL Server knows specifically which columns are part of the *. This is done using sp_refreshview. SQL Compare is designed in version 5 to script an sp_refreshview as part of the synchronization, which is important if you have a view or stored procedure that depends on another view that is a SELECT *.

Please let me know if the issue is that sp_refreshview isn't being scripted where it needs to be.

TroyHusker

OK, I'll have to look at the script and see if sp_refreshview did not get scripted out. I may make a dummy change to the table so that it sees that change. I'll let you know once I get the chance to do that.

Thanks!

TroyHusker

OK, I just had the opportunity to test whether or not a SQL view with a SELECT * FROM... gets recompiled when a change is made to the table. I had to change the name of one of the fields in the table that the view is based on and that was it. When I ran SQL Compare 5, it noticed that both the table and the view had changed but when I looked at the script that was created during synchronization, the only change that was scripted to be made to the view was an extended properties change of some sort. There was no sp_refreshview command in the script anywhere. I went ahead and ran the synchronization anyway and then check the synchronized database. The table had indeed been changed but when running the view, the old field name still existed. So the view had not been refreshed.

I hope that helps some.

Again, thanks for a great product. It has saved me hours and hours of time.

JonathanWatts

Hi Troy,

Yep it looks like you have found an issue here; if the view is identical but there is a difference in a permission or an extended property then the view will not be refreshed correctly.

There is a workaround for your problem: if you run with the ignore permissions and ignore extended properties options enabled the views will refresh correctly. Then if you run another synchronisation with these options now disabled you can then sync up the permissions and extended properties.

I understand that this is not perfect, I have raised this with the development team but I don't have an outlook as to when this issue will resolved at the moment.

Regards,

Jonathan

TroyHusker

Jonathan Watts wrote:

Hi Troy,

Yep it looks like you have found an issue here; if the view is identical but there is a difference in a permission or an extended property then the view will not be refreshed correctly.

There is a workaround for your problem: if you run with the ignore permissions and ignore extended properties options enabled the views will refresh correctly. Then if you run another synchronisation with these options now disabled you can then sync up the permissions and extended properties.

I understand that this is not perfect, I have raised this with the development team but I don't have an outlook as to when this issue will resolved at the moment.

Regards,

Jonathan

Cool! So I'm NOT going crazy. :-)

I also actually found that as long as I don't go into the view (after changing the table) and change something around causing an extended properties change, and also be sure to choose "Include Dependencies" when synchronizing, it will actually run the recreate/recompile command on the view. It's only when there is some difference in the extended properties or permissions (as you said) that it WON'T run the recompile/recreate command. So, if I will just leave well enough alone, and not try to do too much, I'll be alright. :lol:

Anyway, as you said, this product is not perfect, but it sure is awfully close!

Have a GREAT day!