Schema bound views

michaeldye

Afternoon all,

After a quick search I thought I'd better just ask the question - it's probably obvious but...

I've been using SQL compare for years & am now considering SQL source control too; but one of the ongoing hiccups I face is schema bound views; and updates to the tables referenced in the [schema bound] view.

If I try and "just" use SQL compare it'll fail: it tries to update the table and can't (the schema bound stops the table update?).

So; I have a developed a 3-stage process to update a "target" database from a "source" one::

1. A "before" compare script that'll drop my schema bound views in my "target" database
2. Run SQL compare which will both catch the changes between "source" and "target" and also repair the damage done by #1
3. Then an "after" compare script which updates static data (lookups etc.)

This has been ok for years; but is a bit manual and I tend to find (maintaining 10+ "targets" in different places throughout the world) that even though my targets should all be the same I struggle to build a migration script for #2 above and end up doing snapshots of "target"; using SQL compare 'on-line' and often doing a 2 or 3 pass update (e.g. tables first, then views, then SQL SP's) as a single SQL compare pass will often fail. I'll do this for each target as it's reliable and works; but is manual (slow, prone to mistakes etc. etc.).

So: The question (!) - what am I doing wrong? Why do I:

a. Need to manually drop schema-bound views prior to compare?
b. Find that SQL compare can't cope with a 'reasonable' set of changes but needs to do tables, views, SQL SP's in three passes to be reliable?

Ta!

Dr. Michael Dye

Mark J

Thanks for your post - this is indeed something that SQL Compare should be able to handle. I did a quick test with a schemabound view that queries a table with an extra column, and when I got SQL Compare to generate a deployment script it first altered the view to not have schemabinding before changing the table and then reenabled schemabinding on the view.

It would be good to check if you have 'Deploy all dependencies' enabled in the deployment wizard - this behaviour won't work if that checkbox isn't checked.

If you're still having trouble it might be a bug in SQL Compare, in which case a more specific reproduction would be helpful - you can get in touch with support if you need to keep database details private.

michaeldye

Thanks Mark, I need to review the "deploy all dependencies" bit as I tend to have that off as it drags in what seemed to be irrelevant items and I did have problems with it before (long time ago, many versions ago but I'm stuck in my ways!). I'll let you know.

Mark J

Yeah, we currently have a lot of behaviour tied up in that one checkbox and we know users have problems with it bringing in unexpected dependencies. We have some ideas for improving that part of the deployment UI (eg letting you select individual dependencies and telling you exactly why SQL Compare has decided to mark something as a dependency) but it's going to require a lot of backend work and we don't have any definite plans yet. In the meantime hopefully we'll be able to fix any specific bugs that you might encounter, though

michaeldye

Morning Mark, I think you've hit the nail on the head there - it's the "I just want a couple of those" [the schema bound views]; and yes - an explanation as to why might well help as I'm sure there are items I think that are "not required". What I'm going to do is use that checkbox and work out if/when it fails and why; I'll let you know. Ta, Michael.

michaeldye

Good morning again,

Here's a failure I've just got by doing "everything" - i.e. just deploying all changes to the database schema; and including the dependencies



Basically the failure was because I guess it was creating SQL SP's referencing the full text index before said index existed.

So; I took a two-pass approach: select all tables (new and altered); deploy those changes *without* dependencies; then 2nd pass was everything else; again without dependencies. That worked great.

N.B. It also reported differences in an assembly and a certificate; well; there will be (?) I think; especially the certificate... I chose not to deploy them... [OK I could exclude them by a filter; but I want to know they're there; just the differences aren't as such...]

Hope that makes sense and explains a bit why I tend to avoid the "dependencies" checkbox and have to do some manual input into the compare/deploy strategy [which has all worked; which is great; just a bit manual].

Ta, Michael.