CODEWITHSUNDEEP
sql-serversql-server-2005view
freefaller
freefaller

Reputation: 19953

Create view to non-existent table

Is there some way to create a view in a database that links to non-existent tables?

We've just discovered that a data import routine has been failing for a period of time because of a rather large upgrade that accidentally removed a view.

The view (in the database for our product, hosted on a client server) was linked to a table in one of our customers databases, and created by our client for the purpose.

The accidental removal of this view was due to the quantity of database changes (including the creating and dropping of views), and the fact the SQL comparison tool was going to drop the client-specific view was simply missed.

So it would be really useful to be able to create a copy of this view on our local development databases (with full qualified names to the client tables that we obvious don't have access to outside of the live environment) so this doesn't happen again.

(The customer in question is running SQL Server 2008, however this is a product that is also run on 2005 environment for other customers. So an answer for 2005 would be preferable, but if it's 2008 specific that isn't a problem.)

Update:

To respond to @Damien's comment...

I understand what you're saying, and creating an empty table that is being linked to in the view would make a lot of sense.

Unfortunately the table in question is hosted not only in a different database, but on a different SQL server. That would mean that I would have create a new server instance on all our development machine, in order to host this single empty table. And I would have to do this on quite a number of our clients, resulting in a LOT of practically unused server instances.

I'm really hoping to be able to avoid this, and instead be able to create the view (which obviously will fail in our development environment) but will not be accidentally removed again in the future.

Update 2

I've taken on board what @Damien has said and implemented this using Linked Server... see my answer for more details

Upvotes: 4

Views: 6516

Answers (1)

freefaller
freefaller

Reputation: 19953

As @Mark says in his comment under my question, it doesn't appear to be possible to do this with a view. ("Deferred Name Resolution" exists for stored procedures, but not for views).

The way I've gotten around it is to roughly follow @Damien's suggestion by creating a new instance on my development machine, which is purely going to be used for these "client specific empty tables".

Then I created a Linked Server object to this new instance with an alias of the clients server name (using this answer on SO in order to create the Linked Server object), and in the instance I created the appropriately named database and appropriately named table.

The service for this new instance has been set to Manual start, so it shouldn't be taking up and resources - and I can get it up and running when necessary for any future work.

The result is that I now have the view in question in my development database, and it "compares" exactly with the view in our client database... so it will never (theoretically) be deleted as part of an upgrade again.

Upvotes: 2

Related Questions