January 24, 2018 at 7:10 am
pdanes - Wednesday, January 24, 2018 2:24 AMScottPletcher - Tuesday, January 23, 2018 9:27 AMHave you considered instead of binding them just running a:
sp_refreshview
across all the views when a base table changes? (or across all views that could possibly be affected, if you can limit it to that)Thanks for the suggestion, but as As Ron pointed out, that doesn't get me anywhere. In the first place, I *-can't-* make a table change if there is a schema-bound view on the table. If I unbind the views, removing or changing a column in the underlying table will make the dependent views crash, and sp_refreshview will do nothing to correct that. That's kind of the whole point of schemabinding - to prevent you from breaking views that depend on a certain table structure.
But the dependent views won't "crash" if you refresh them before they are used, right? How does refreshing them not resync them. And if you were planning on undoing and then redoing the binding anyway so you could make a change, why is simply foregoing the binding and automatically refreshing worse?
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 24, 2018 at 7:11 am
MMartin1 - Tuesday, January 23, 2018 10:20 PMNot sure how much this would help, as I've not done this, but when dealing with the order of dependencies there is the a system procedure ,sp_depends, that may be of use. Try exporting the results to a temp table. See if this give something useful to the logic.
Hm, that looks useful. It doesn't appear to directly address the issue of cascaded views, but just knowing what all views are impacted is a huge step in the right direction. What I've been doing so far is just trying to make a change, and reacting to error messages - slow and frustrating. Maybe I can iterate this procedure to arrange the list into order by dependency sequence - that would do exactly what I need.
Many thanks for the tip - I've got some experimenting to do.
sys.sql_expression_dependencies should help with that also.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 24, 2018 at 12:03 pm
ScottPletcher - Wednesday, January 24, 2018 7:10 AMpdanes - Wednesday, January 24, 2018 2:24 AMScottPletcher - Tuesday, January 23, 2018 9:27 AMHave you considered instead of binding them just running a:
sp_refreshview
across all the views when a base table changes? (or across all views that could possibly be affected, if you can limit it to that)Thanks for the suggestion, but as As Ron pointed out, that doesn't get me anywhere. In the first place, I *-can't-* make a table change if there is a schema-bound view on the table. If I unbind the views, removing or changing a column in the underlying table will make the dependent views crash, and sp_refreshview will do nothing to correct that. That's kind of the whole point of schemabinding - to prevent you from breaking views that depend on a certain table structure.
But the dependent views won't "crash" if you refresh them before they are used, right? How does refreshing them not resync them. And if you were planning on undoing and then redoing the binding anyway so you could make a change, why is simply foregoing the binding and automatically refreshing worse?
Yes, they WILL crash. Or maybe the 'refresh' attempt will crash first - I've never tried it. If a view refers to a column in a table, and that column is deleted or renamed, any attempts to materialize that view will bomb, refresh or no. Try it yourself - make a view on a table, then remove a column referenced in that view, then try to open the view. No amount of refreshing or resyncing attempts will 'fix' the fact that the view is trying the read a column that simply isn't there.
January 24, 2018 at 12:12 pm
pdanes - Wednesday, January 24, 2018 2:51 AMMMartin1 - Tuesday, January 23, 2018 10:20 PMNot sure how much this would help, as I've not done this, but when dealing with the order of dependencies there is the a system procedure ,sp_depends, that may be of use. Try exporting the results to a temp table. See if this give something useful to the logic.Hm, that looks useful. It doesn't appear to directly address the issue of cascaded views, but just knowing what all views are impacted is a huge step in the right direction. What I've been doing so far is just trying to make a change, and reacting to error messages - slow and frustrating. Maybe I can iterate this procedure to arrange the list into order by dependency sequence - that would do exactly what I need.
Many thanks for the tip - I've got some experimenting to do.
sys.sql_expression_dependencies should help with that also.
Also looks useful, although not quite as easy to read directly, since it has IDs. Again, thank you.
January 24, 2018 at 12:46 pm
pdanes - Wednesday, January 24, 2018 12:03 PMScottPletcher - Wednesday, January 24, 2018 7:10 AMpdanes - Wednesday, January 24, 2018 2:24 AMScottPletcher - Tuesday, January 23, 2018 9:27 AMHave you considered instead of binding them just running a:
sp_refreshview
across all the views when a base table changes? (or across all views that could possibly be affected, if you can limit it to that)Thanks for the suggestion, but as As Ron pointed out, that doesn't get me anywhere. In the first place, I *-can't-* make a table change if there is a schema-bound view on the table. If I unbind the views, removing or changing a column in the underlying table will make the dependent views crash, and sp_refreshview will do nothing to correct that. That's kind of the whole point of schemabinding - to prevent you from breaking views that depend on a certain table structure.
But the dependent views won't "crash" if you refresh them before they are used, right? How does refreshing them not resync them. And if you were planning on undoing and then redoing the binding anyway so you could make a change, why is simply foregoing the binding and automatically refreshing worse?
Yes, they WILL crash. Or maybe the 'refresh' attempt will crash first - I've ever tried it. If a view refers to a column in a table, and that column is deleted or renamed, any attempts to materialize that view will bomb, refresh or no. Try it yourself - make a view on a table, then remove a column referenced in that view, then try to open the view. No amount of refreshing or resyncing attempts will 'fix' the fact that the view is trying the read a column that simply isn't there.
Well, yeah, but if you're removing/renaming columns, rather than adding them, you should adjust those views first anyway,
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 24, 2018 at 1:38 pm
And just to add a quick note : If you employ these dependency functions ... I would first create a sandbox of schema binded view , upon view yet small enough to keep track of. That way you know for sure what is what dependent on what . There you can test your logic with confidence in knowing if the results panned out or not.
----------------------------------------------------
January 24, 2018 at 5:15 pm
ScottPletcher - Wednesday, January 24, 2018 12:46 PMpdanes - Wednesday, January 24, 2018 12:03 PMScottPletcher - Wednesday, January 24, 2018 7:10 AMpdanes - Wednesday, January 24, 2018 2:24 AMScottPletcher - Tuesday, January 23, 2018 9:27 AMHave you considered instead of binding them just running a:
sp_refreshview
across all the views when a base table changes? (or across all views that could possibly be affected, if you can limit it to that)Thanks for the suggestion, but as As Ron pointed out, that doesn't get me anywhere. In the first place, I *-can't-* make a table change if there is a schema-bound view on the table. If I unbind the views, removing or changing a column in the underlying table will make the dependent views crash, and sp_refreshview will do nothing to correct that. That's kind of the whole point of schemabinding - to prevent you from breaking views that depend on a certain table structure.
But the dependent views won't "crash" if you refresh them before they are used, right? How does refreshing them not resync them. And if you were planning on undoing and then redoing the binding anyway so you could make a change, why is simply foregoing the binding and automatically refreshing worse?
Yes, they WILL crash. Or maybe the 'refresh' attempt will crash first - I've ever tried it. If a view refers to a column in a table, and that column is deleted or renamed, any attempts to materialize that view will bomb, refresh or no. Try it yourself - make a view on a table, then remove a column referenced in that view, then try to open the view. No amount of refreshing or resyncing attempts will 'fix' the fact that the view is trying the read a column that simply isn't there.
Well, yeah, but if you're removing/renaming columns, rather than adding them, you should adjust those views first anyway,
I'm beginning to wonder if you really use SQL Server.
If you add a column to a table, you do not need to remove schemabinding from a view that references the table with an added column - the view will continue to work just fine, ignoring the new column, and the table can be saved without unbinding the view.
If you add a column to a view, referencing a field in the table that doesn't exist yet, you can't save the view - you will get an 'Invalid column name' error. It's impossible to 'adjust the views first'.
Have you actually tried any of the suggestions you've offered here?
January 24, 2018 at 5:20 pm
MMartin1 - Wednesday, January 24, 2018 1:38 PMAnd just to add a quick note : If you employ these dependency functions ... I would first create a sandbox of schema binded view , upon view yet small enough to keep track of. That way you know for sure what is what dependent on what . There you can test your logic with confidence in knowing if the results panned out or not.
Sorry, I don't understand what you mean by 'create a sandbox of schema binded view'.
January 24, 2018 at 9:45 pm
pdanes - Wednesday, January 24, 2018 5:20 PMMMartin1 - Wednesday, January 24, 2018 1:38 PMAnd just to add a quick note : If you employ these dependency functions ... I would first create a sandbox of schema binded view , upon view yet small enough to keep track of. That way you know for sure what is what dependent on what . There you can test your logic with confidence in knowing if the results panned out or not.Sorry, I don't understand what you mean by 'create a sandbox of schema binded view'.
Simply means create test environment containing schema binded views. Create schema binded views upon the views with a small amount of objects. Keep track with a piece of paper if you must. That way you can test your scripted solution here, where you know what your dependencies actually are. And compare that versus what your automated script tells you. Then you'll know if you are on the right track with the automation. Maybe I misread but I got the impression your were not aware of all the dependencies in your actual system. A automated attempt may spew out results... how do you know they are accurate and all encompassing ?
----------------------------------------------------
January 24, 2018 at 9:55 pm
pdanes - Wednesday, January 24, 2018 5:15 PMScottPletcher - Wednesday, January 24, 2018 12:46 PMpdanes - Wednesday, January 24, 2018 12:03 PMScottPletcher - Wednesday, January 24, 2018 7:10 AMpdanes - Wednesday, January 24, 2018 2:24 AMScottPletcher - Tuesday, January 23, 2018 9:27 AMHave you considered instead of binding them just running a:
sp_refreshview
across all the views when a base table changes? (or across all views that could possibly be affected, if you can limit it to that)Thanks for the suggestion, but as As Ron pointed out, that doesn't get me anywhere. In the first place, I *-can't-* make a table change if there is a schema-bound view on the table. If I unbind the views, removing or changing a column in the underlying table will make the dependent views crash, and sp_refreshview will do nothing to correct that. That's kind of the whole point of schemabinding - to prevent you from breaking views that depend on a certain table structure.
But the dependent views won't "crash" if you refresh them before they are used, right? How does refreshing them not resync them. And if you were planning on undoing and then redoing the binding anyway so you could make a change, why is simply foregoing the binding and automatically refreshing worse?
Yes, they WILL crash. Or maybe the 'refresh' attempt will crash first - I've ever tried it. If a view refers to a column in a table, and that column is deleted or renamed, any attempts to materialize that view will bomb, refresh or no. Try it yourself - make a view on a table, then remove a column referenced in that view, then try to open the view. No amount of refreshing or resyncing attempts will 'fix' the fact that the view is trying the read a column that simply isn't there.
Well, yeah, but if you're removing/renaming columns, rather than adding them, you should adjust those views first anyway,
I'm beginning to wonder if you really use SQL Server.
If you add a column to a table, you do not need to remove schemabinding from a view that references the table with an added column - the view will continue to work just fine, ignoring the new column, and the table can be saved without unbinding the view.
If you add a column to a view, referencing a field in the table that doesn't exist yet, you can't save the view - you will get an 'Invalid column name' error. It's impossible to 'adjust the views first'.
Have you actually tried any of the suggestions you've offered here?
Couple things here : If I remember correctly, sp_refreshView works to reorganize the ordinal position of he columns when you have a select * in your view. Be careful coding this way to begin with, schema binding or not.
If you add a column to a table, you do not need to remove schemabinding from a view that references the table with an added column
Scott refers to the action of removing/renaming , not adding columns. If you are to remove a column from the table , it needs to be removed from the referencing view as well (schema binding or not).
----------------------------------------------------
January 25, 2018 at 12:49 am
MMartin1 - Wednesday, January 24, 2018 9:45 PMpdanes - Wednesday, January 24, 2018 5:20 PMMMartin1 - Wednesday, January 24, 2018 1:38 PMAnd just to add a quick note : If you employ these dependency functions ... I would first create a sandbox of schema binded view , upon view yet small enough to keep track of. That way you know for sure what is what dependent on what . There you can test your logic with confidence in knowing if the results panned out or not.Sorry, I don't understand what you mean by 'create a sandbox of schema binded view'.
Simply means create test environment containing schema binded views. Create schema binded views upon the views with a small amount of objects. Keep track with a piece of paper if you must. That way you can test your scripted solution here, where you know what your dependencies actually are. And compare that versus what your automated script tells you. Then you'll know if you are on the right track with the automation. Maybe I misread but I got the impression your were not aware of all the dependencies in your actual system. A automated attempt may spew out results... how do you know they are accurate and all encompassing ?
I see. Yes, I do have test environments of my DBs. In fact, I generally make a copy of my entire database, occasionally run scripts to empty the data and copy in ALL of the data from the production version, so I have current data to test on, and when all the bugs are ironed out (yeah, right), I take everything offline, back up the production, do one last copy of data, back up the development, and switch the development copy over to become the current production.
As for being 'aware of' - I don't know if that's exactly the language I'd use. I am the sole developer and maintainer of everything here - I've written it all, so in that sense, I am 'aware of' everything in every database. Nonetheless, I do forget things, as the databases get more complicated, and certain parts work with no hassles for years, so they do not get much attention from me, then suddenly someone thinks of something new they'd like, or requirements change, or I discover some subtle bug that only surfaces during an odd combination of circumstances that had not arisen earlier. Then I need to make changes, and sometimes I've long since forgotten everything impacted by the change, hence my desire for something automated.
As it is, I make a change to a table, try to save it and get the usual error warning me of schemabound views. But the message only tells me about one view, and it doesn't include the schema of the offending view, so tracking down the view, fixing it, trying to save again, getting another warning, over and over, is kind of tedious. Then rebinding them all, in the correct order is another place where it's easy to make a mistake, get them out of order and have to spend much time unbinding again, and making sure I get the order correct. I'd like to write something that will track down all views impacted by a change to a table, unbind them in the correct order and stop. I would then make the necessary changes to the views, test them, and when everything was working properly, rebind them back in reverse order. Ideally, it would also list views and procedures that are impacted, but not schemabound. Sometimes I overlook one of those in a rush to get something done before a deadline, and then it really does crash in production.
The overall environment here is such that it's not a huge ordeal, but it's a nuisance, and a little embarrassing. I'd like to come up with a better way to deal with it all.
January 25, 2018 at 1:10 am
MMartin1 - Wednesday, January 24, 2018 9:55 PMpdanes - Wednesday, January 24, 2018 5:15 PMScottPletcher - Wednesday, January 24, 2018 12:46 PMpdanes - Wednesday, January 24, 2018 12:03 PMScottPletcher - Wednesday, January 24, 2018 7:10 AMpdanes - Wednesday, January 24, 2018 2:24 AMScottPletcher - Tuesday, January 23, 2018 9:27 AMHave you considered instead of binding them just running a:
sp_refreshview
across all the views when a base table changes? (or across all views that could possibly be affected, if you can limit it to that)Thanks for the suggestion, but as As Ron pointed out, that doesn't get me anywhere. In the first place, I *-can't-* make a table change if there is a schema-bound view on the table. If I unbind the views, removing or changing a column in the underlying table will make the dependent views crash, and sp_refreshview will do nothing to correct that. That's kind of the whole point of schemabinding - to prevent you from breaking views that depend on a certain table structure.
But the dependent views won't "crash" if you refresh them before they are used, right? How does refreshing them not resync them. And if you were planning on undoing and then redoing the binding anyway so you could make a change, why is simply foregoing the binding and automatically refreshing worse?
Yes, they WILL crash. Or maybe the 'refresh' attempt will crash first - I've ever tried it. If a view refers to a column in a table, and that column is deleted or renamed, any attempts to materialize that view will bomb, refresh or no. Try it yourself - make a view on a table, then remove a column referenced in that view, then try to open the view. No amount of refreshing or resyncing attempts will 'fix' the fact that the view is trying the read a column that simply isn't there.
Well, yeah, but if you're removing/renaming columns, rather than adding them, you should adjust those views first anyway,
I'm beginning to wonder if you really use SQL Server.
If you add a column to a table, you do not need to remove schemabinding from a view that references the table with an added column - the view will continue to work just fine, ignoring the new column, and the table can be saved without unbinding the view.
If you add a column to a view, referencing a field in the table that doesn't exist yet, you can't save the view - you will get an 'Invalid column name' error. It's impossible to 'adjust the views first'.
Have you actually tried any of the suggestions you've offered here?
Couple things here : If I remember correctly, sp_refreshView works to reorganize the ordinal position of he columns when you have a select * in your view. Be careful coding this way to begin with, schema binding or not.
If you add a column to a table, you do not need to remove schemabinding from a view that references the table with an added column
Scott refers to the action of removing/renaming , not adding columns. If you are to remove a column from the table , it needs to be removed from the referencing view as well (schema binding or not).
Couple things here : If I remember correctly, sp_refreshView works to reorganize the ordinal position of he columns when you have a select * in your view. Be careful coding this way to begin with, schema binding or not.
I try to not use Select * in production work, and I've never used this sp_refreshView procedure.
Scott refers to the action of removing/renaming , not adding columns. If you are to remove a column from the table , it needs to be removed from the referencing view as well (schema binding or not).
Well, yes - I know. That is the whole point of this thread, and why I posted my question in the first place. I need to do that from time to time, and I occasionally get lost in the ensuing complications.
January 25, 2018 at 6:56 am
pdanes - Wednesday, January 24, 2018 5:15 PMScottPletcher - Wednesday, January 24, 2018 12:46 PMpdanes - Wednesday, January 24, 2018 12:03 PMScottPletcher - Wednesday, January 24, 2018 7:10 AMpdanes - Wednesday, January 24, 2018 2:24 AMScottPletcher - Tuesday, January 23, 2018 9:27 AMHave you considered instead of binding them just running a:
sp_refreshview
across all the views when a base table changes? (or across all views that could possibly be affected, if you can limit it to that)Thanks for the suggestion, but as As Ron pointed out, that doesn't get me anywhere. In the first place, I *-can't-* make a table change if there is a schema-bound view on the table. If I unbind the views, removing or changing a column in the underlying table will make the dependent views crash, and sp_refreshview will do nothing to correct that. That's kind of the whole point of schemabinding - to prevent you from breaking views that depend on a certain table structure.
But the dependent views won't "crash" if you refresh them before they are used, right? How does refreshing them not resync them. And if you were planning on undoing and then redoing the binding anyway so you could make a change, why is simply foregoing the binding and automatically refreshing worse?
Yes, they WILL crash. Or maybe the 'refresh' attempt will crash first - I've ever tried it. If a view refers to a column in a table, and that column is deleted or renamed, any attempts to materialize that view will bomb, refresh or no. Try it yourself - make a view on a table, then remove a column referenced in that view, then try to open the view. No amount of refreshing or resyncing attempts will 'fix' the fact that the view is trying the read a column that simply isn't there.
Well, yeah, but if you're removing/renaming columns, rather than adding them, you should adjust those views first anyway,
I'm beginning to wonder if you really use SQL Server.
If you add a column to a table, you do not need to remove schemabinding from a view that references the table with an added column - the view will continue to work just fine, ignoring the new column, and the table can be saved without unbinding the view.
If you add a column to a view, referencing a field in the table that doesn't exist yet, you can't save the view - you will get an 'Invalid column name' error. It's impossible to 'adjust the views first'.
Have you actually tried any of the suggestions you've offered here?
Interesting.
I'm sorry I didn't have time to do a full inexperienced-developer-level (IDL) explanation before. I admit I also didn't realize it would be necessary.
Without schemabinding, as I suggested as a possibility, you need to run sp_refreshview anyway, as per MS docs:
"If a view is not created with schemabinding, sp_refreshview should be run when changes are made to the objects underlying the view that affect the definition of the view. "
Further hints for IDLs:
1) Run refreshview in a loop with TRY and CATCH, capturing any errors (IDL-hint: while logging successes, of course)
2) Attempt to autocorrect them (IDL-help: that means scan for the removed column name and the first following ',', then comment out or remove it (our proc for this has an option to do either))
3) Run that view back thru the whole process
I really don't have time to flesh this out more, but I'm sure many others here can fill in the code for you.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 25, 2018 at 7:17 am
ScottPletcher - Thursday, January 25, 2018 6:56 AMpdanes - Wednesday, January 24, 2018 5:15 PMScottPletcher - Wednesday, January 24, 2018 12:46 PMpdanes - Wednesday, January 24, 2018 12:03 PMScottPletcher - Wednesday, January 24, 2018 7:10 AMpdanes - Wednesday, January 24, 2018 2:24 AMScottPletcher - Tuesday, January 23, 2018 9:27 AMHave you considered instead of binding them just running a:
sp_refreshview
across all the views when a base table changes? (or across all views that could possibly be affected, if you can limit it to that)Thanks for the suggestion, but as As Ron pointed out, that doesn't get me anywhere. In the first place, I *-can't-* make a table change if there is a schema-bound view on the table. If I unbind the views, removing or changing a column in the underlying table will make the dependent views crash, and sp_refreshview will do nothing to correct that. That's kind of the whole point of schemabinding - to prevent you from breaking views that depend on a certain table structure.
But the dependent views won't "crash" if you refresh them before they are used, right? How does refreshing them not resync them. And if you were planning on undoing and then redoing the binding anyway so you could make a change, why is simply foregoing the binding and automatically refreshing worse?
Yes, they WILL crash. Or maybe the 'refresh' attempt will crash first - I've ever tried it. If a view refers to a column in a table, and that column is deleted or renamed, any attempts to materialize that view will bomb, refresh or no. Try it yourself - make a view on a table, then remove a column referenced in that view, then try to open the view. No amount of refreshing or resyncing attempts will 'fix' the fact that the view is trying the read a column that simply isn't there.
Well, yeah, but if you're removing/renaming columns, rather than adding them, you should adjust those views first anyway,
I'm beginning to wonder if you really use SQL Server.
If you add a column to a table, you do not need to remove schemabinding from a view that references the table with an added column - the view will continue to work just fine, ignoring the new column, and the table can be saved without unbinding the view.
If you add a column to a view, referencing a field in the table that doesn't exist yet, you can't save the view - you will get an 'Invalid column name' error. It's impossible to 'adjust the views first'.
Have you actually tried any of the suggestions you've offered here?
Interesting.
I'm sorry I didn't have time to do a full inexperienced-developer-level (IDL) explanation before. I admit I also didn't realize it would be necessary.
Without schemabinding, as I suggested as a possibility, you need to run sp_refreshview anyway, as per MS docs:
"If a view is not created with schemabinding, sp_refreshview should be run when changes are made to the objects underlying the view that affect the definition of the view. "Further hints for IDLs:
1) Run refreshview in a loop with TRY and CATCH, capturing any errors (IDL-hint: while logging successes, of course)
2) Attempt to autocorrect them (IDL-help: that means scan for the removed column name and the first following ',', then comment out or remove it (our proc for this has an option to do either))
3) Run that view back thru the whole processI really don't have time to flesh this out more, but I'm sure many others here can fill in the code for you.
No need to start trading insults. Over 50 years as a computer geek of various sorts qualifies me to ignore your IDL comment, and your suggestion that I try something that even an IDL person knows doesn't work (add a non-existent column to a view) gives rise to suspicion that you might be offering advice that is not all that useful.
I'll take a look at the parts of your comments that might have some merit.
January 25, 2018 at 7:25 am
pdanes - Thursday, January 25, 2018 7:17 AMScottPletcher - Thursday, January 25, 2018 6:56 AMpdanes - Wednesday, January 24, 2018 5:15 PMScottPletcher - Wednesday, January 24, 2018 12:46 PMpdanes - Wednesday, January 24, 2018 12:03 PMScottPletcher - Wednesday, January 24, 2018 7:10 AMpdanes - Wednesday, January 24, 2018 2:24 AMScottPletcher - Tuesday, January 23, 2018 9:27 AMHave you considered instead of binding them just running a:
sp_refreshview
across all the views when a base table changes? (or across all views that could possibly be affected, if you can limit it to that)Thanks for the suggestion, but as As Ron pointed out, that doesn't get me anywhere. In the first place, I *-can't-* make a table change if there is a schema-bound view on the table. If I unbind the views, removing or changing a column in the underlying table will make the dependent views crash, and sp_refreshview will do nothing to correct that. That's kind of the whole point of schemabinding - to prevent you from breaking views that depend on a certain table structure.
But the dependent views won't "crash" if you refresh them before they are used, right? How does refreshing them not resync them. And if you were planning on undoing and then redoing the binding anyway so you could make a change, why is simply foregoing the binding and automatically refreshing worse?
Yes, they WILL crash. Or maybe the 'refresh' attempt will crash first - I've ever tried it. If a view refers to a column in a table, and that column is deleted or renamed, any attempts to materialize that view will bomb, refresh or no. Try it yourself - make a view on a table, then remove a column referenced in that view, then try to open the view. No amount of refreshing or resyncing attempts will 'fix' the fact that the view is trying the read a column that simply isn't there.
Well, yeah, but if you're removing/renaming columns, rather than adding them, you should adjust those views first anyway,
I'm beginning to wonder if you really use SQL Server.
If you add a column to a table, you do not need to remove schemabinding from a view that references the table with an added column - the view will continue to work just fine, ignoring the new column, and the table can be saved without unbinding the view.
If you add a column to a view, referencing a field in the table that doesn't exist yet, you can't save the view - you will get an 'Invalid column name' error. It's impossible to 'adjust the views first'.
Have you actually tried any of the suggestions you've offered here?
Interesting.
I'm sorry I didn't have time to do a full inexperienced-developer-level (IDL) explanation before. I admit I also didn't realize it would be necessary.
Without schemabinding, as I suggested as a possibility, you need to run sp_refreshview anyway, as per MS docs:
"If a view is not created with schemabinding, sp_refreshview should be run when changes are made to the objects underlying the view that affect the definition of the view. "Further hints for IDLs:
1) Run refreshview in a loop with TRY and CATCH, capturing any errors (IDL-hint: while logging successes, of course)
2) Attempt to autocorrect them (IDL-help: that means scan for the removed column name and the first following ',', then comment out or remove it (our proc for this has an option to do either))
3) Run that view back thru the whole processI really don't have time to flesh this out more, but I'm sure many others here can fill in the code for you.
No need to start trading insults. Over 50 years as a computer geek of various sorts qualifies me to ignore your IDL comment, and your suggestion that I try something that even an IDL person knows doesn't work (add a non-existent column to a view) gives rise to suspicion that you might be offering advice that is not all that useful.
I'll take a look at the parts of your comments that might have some merit.
I didn't start the insults. I simply responded to your extraordinary condescension, which continues apace.
I don't understand the "add a non-existent...". My comments concern programmatically removing it. It does work, I use it. I'd rather have the machine do corrections like this whenever possible than do them by hand myself, including running searches ahead of time.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 15 posts - 16 through 30 (of 34 total)
You must be logged in to reply to this topic. Login to reply