Schema issue

SQL Server Administration
1

I have a view defined as

CREATE VIEW
AS
SELECT name from LINKEDServer.DBName.dbo.tablename

Recently, DBNAME on the target server was upgraded by the vendor and changed the schema of all tables from [dbo] to [appUser] - which now breaks all of my views.

Is there anything that can be done that can allow my existing code to access the new schema without having to change all of my view definitions?

Thanks in advance

2

Since the View is hard wired to use "dbo", I don't see a way. The good news is that the change should be a simple find and replace.
EDIT: I guess you could create an additional set of equivalent views in the dbo schema on the LINKEDServer.DBName that just pulled the data from the new appUser schema. Your original view would point to these new views. That does seem like much more trouble than it would be worth. Plus, your maintenance costs could increase dramatically. If it was me, I'd bite the bullet and modify the existing views.

3

None that I can think of. Your only option appears to be to modify your view definitions.

4

You may want to consider setting up and using synonyms to avoid this issue in the future. You can then use the synonym directly - or use it in your views.

Example:

Create Synonym dbo.SomeName For LINKEDserver.DBName.dbo.tablename;
Go

Create View dbo.MyView
As
Select Name From dbo.SomeName;
Go

If the schema changes - then all you need to do is update the synonyms and all of your code doesn't need to be touched.

5

USE LINKEDServer
CREATE SYNONYM dbo.tablename FOR appUser.tablename;

6

It looks like there synonyms on target servers are not permitted/recognized by linked server - ugh- was almost there....

7

Synonyms on target servers are not permitted, but you can create a synonym for a remote object on the local server. For example,

Create synonym dbo.TableName for LINKEDServer.DBName.dbo.tablename

Then you can refer to dbo.TableName rather than LINKEDServer.DBName.dbo.tablename in your view definition. Even so, you would need to modify your view definition to remove the "LINKEDServer.DBName." from your view definitions.

8

We (now!) use sysnonyms for everything that is either 3 or 4 part named. experience tells us that the name of the database / server WILL change! and its a right royal pain to change the code to match ...

We tend not to use [dbo] schema for those - for a 3-part name we use e.g.

CREATE SYNONYM MyDatabaseName.MyTableName
FOR MyDatabaseName.dbo.MyTableName

and then

CREATE VIEW MyView
AS
SELECT name 
FROM MyDatabaseName.MyTableName

for a linked server I would make up a composite name to indicate Server + Database name - perhaps this (although usually I would abbreviate the Server Name, unless it was short and pithy already

CREATE SYNONYM MyRemoteServer_MyDatabaseName.MyTableName
FOR MyRemoteServer.MyDatabaseName.dbo.MyTableName

However, historically we have very rarely directly referenced remote servers using 4-part naming, and instead we have used OpenQuery() as we have either had more control over the process, or better performance.

9

D'OH, sorry about that. You'll have to use a view on the linked server rather than a synonym.

--while connected to LINKEDServer
USE DBName
GO
CREATE VIEW dbo.tablename AS SELECT * FROM appUser.tablename
GO

10

Good info everyone - thanks for all of the help!