Any Way To Turn Off Error Checking?

Question

Wow. I've got a doozy.

OK, I'm in the process of cloning an existing database to another server under a new name. Currently, this new database - until it is populated after it goes on line - has to refer to the data in the original; read only, no writes. So, instead of creating tables, I'm creating views in the clone based on each of the tables in the original database. That way, when the clone needs data from a table, it looks at the view which sends it to the original table.

I also have to bring over many of the original's views, too.

Not really a hard problem, I thought. Just make some CREATE scripts to construct the various views.

When I first did it, this worked fine; all the necessary views were created and the database worked fine. But, when cleaning up some naming conventions, I discovered that the views that were cloned over via CREATE refered to tables in the original database that are being replaced in the clone by views. OK, again, no problem, I just change the references to the clone's views rather than the original's tables. I ran a parse and everything passed. So, I tried to execute the script.

Well, that was a total bomb. I had errors out the gazoober. Everything that had worked, ceased to do so, because of the name changes.

Debugging and some manipulation of the order of the CREATE statement showed me that, though the code was good, when SQL is actually executing the script, it would be helpful and check to ensure that anything that is referenced by the view being created already exists. Well, not everything does when a particular view is being created.

What I've got is a 15K line script that creates just under 100 views. But, if View 14 tries to SELECT from View 32, View 14 crashes and fails. So, OK, I move the code that creates View 32 above View 14; say, in spot 1.

One error down.

Unfortunately, View 32 refers to View 6, so I have to make sure View 6 is created before 32, so that 32 exists for 14. So, that causes a new error. But, I still have the errors caused by Views 16, 28 and 76 not existing for #14 to reference. And, I have to make sure that 4 precedes 7 but comes after 12 which has to come before 3 that comes before 66 that comes after 9 ... and so on.

Basically, it would be a total bore to go through all these CREATES to get their order proper so that no view being created refers to a view that hasn't been created yet.

Thing is, once the views are all made, then the order won't matter.

So, now we come to my question; given the order of events I've listed above, it would be EXCELLENT it it were possible to tell SQL not to do any error checking to see if something exists before it can be referenced. That way, I could go ahead, CREATE all the necessary views, and then SQL can go on its merry way being helpful and all.

Is it possible to have SQL simply build the views without any reference checks?

I really really hope so...

Answer 1

Hi Adam,

Please try creating views in a proper sequence may help you to avoid this issue. Create all master(primary) tables/views first and then secondary or dependant views. Also, check the link below that may be helpful.

http://sqlblogcasts.com/blogs/tonyrogerson/archive/2008/01/15/with-check-option-on-create-view-what-is-it-how-s-it-work-what-other-options-are-available-for-referntial-integrity-ri-part-1.aspx

please check the link below to execute large scripts(refer "use SQLCMD to execute the large script "),

http://blog.sqlauthority.com/2011/05/07/sql-server-2008-2008-r2-create-script-to-copy-database-schema-and-all-the-objects-data-schema-stored-procedure-functions-triggers-tables-views-constraints-and-all-other-database-objects/

---

Thanks,
Suresh
Please use Mark or Propose as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 2

Hi,

I don't think it is possible to stop the reference checks. 

On the plus side when you use the generate scripts wizard in SQL Server Management Studio, it should try place the views in the correct order and therefore help with this type of issue.

Answer 3

I agree, you can't really do this in SQL (procedures work that way, even when you don't want them to). You might try using Red-Gate SQL Compare or SQL Server Data TOols, but the references in the view sound like a mess, and when views reference views things get crazy. It sounds like, if your views aren't a total mess, they will do until the actual mess arrives. Compare your database to an empty database to build your initial script by choosing the objects you are trying to create.

If possible, I would search your views, remove database name references where it is to the local database, change all references to external databases to synonyms, then it will be easier.

I don't want to sound like a Red-Gate shill, but they have a great dependency tool that could help you decipher the references.  Depending on the version you are using, you might be able to use sys.sql_expression_dependencies or sys.dm_sql_referenced_entities to help out.

Then, I would just execute the script, find the first error that is about dependencies (SSDT or RedGate SC should order things pretty well, and they will put transactions around your code so it won't build until it is right), and start sorting and fixing code from there. 100 view will take a while, but it probably should. 

I might be missing some of the point too, so I am not sure that this is at all helpful :)

---

Louis

Answer 4

Adam, see my blog might help you

http://sqlblog.com/blogs/uri_dimant/archive/2010/07/13/find-dependency-task-again.aspx

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 5

Hi,

I don't think it is possible to stop the reference checks. 

On the plus side when you use the generate scripts wizard in SQL Server Management Studio, it should try place the views in the correct order and therefore help with this type of issue.

I used the wizard and I'm guessing that my views are simply nested in too much of a complexity for the wizard to compensate.

Things have changed and the clone may have to be online earlier than anticipated. So, I may have to work as I was originally - when it worked - and have the views reference the original tables, hoping that nothing major happens to them, before the clone can be made production and all the data is properly stored in local tables.

Answer 6

Well, I got sort of an answer from a coworker. Essentially, there is no way to turn off the error-checking, but she said, just run the query several times until the errors go away. Once a particular view is created from a prior run of the query, subsequent runs will simply say that the view has already been created and skip over the code relevant to it. So, after enough runs, all the referential errors would become duplication errors. Once that happens, the views have all been made and properly referenced.

Answer 7

True. That is a fairly brute force solution, and if you are just doing this once that is a very valid method.  It is very common to do that when deleting data from a bunch of tables as well. 

delete from parent
delete from child

The first will fail if there are related rows in the child table, but the second time you run it it will succeed.

I should probably have thought of that, but my assumption was that you wanted to be able to do this in a clean, error free manner if not the first time, then when you migrate the file. 

---

Louis

Answer 8

No prob-lemo.

I had asked about your Red Hat solution and was rebuffed with a, "That's a grand for a license - ain't gonna happen."

But, then, suddenly, funding seems to have magically appeared to proceed, so the task went from "back-burner so it can be done neatly and cleanly" to "get it done yesterday, if not last week." So, the brute force solution was acceptable. And, it is a one-shot - as far as I know - so, no one's going to care about neatness.

Thanx for your help!