0

I have 3 SQL servers X, Y, Z and 2 databases X.A and Y.B. There are (many) view in X.A that uses 4-level naming in their definition:

SELECT ... FROM Y.B.SCHEMA.TABLE

From this I infer that X contains a linked server definition for Y. I am restoring X.A and Y.Bfrom backup files into server Z, so I now have Z.A and Z.B available in the same server. But these views obviously fail in Z due to not knowing what Y is.

I would like these views to work in the restored database Z.A, in such a way that they get the data from Z.B (and not from Y.B), without having to change the views (there are many such views). I am not sure if this is possible. I am able to do changes on server Z as needed, but I do not have any access to X and Y. The users of the view also only have access to Z.

I thought about defining a synonym Y in Z to point to localhost but it seems I need to refer at least to a schema or schema.table when creating a new synonym. Or can I have a synonym of just the server name?

I also thought about defining a linked server named Y in Z, but pointing to itself. Here I was only able to make a linked server named localhost pointing to itself, and this does not help solve my problem.

Is this at all possible? Can I convince SQL server to use Z when Y is referred to in the view definition?

1 Answer 1

2

Synonyms will not be useful for your need since that will require changing the view definition to specify 2-part names.

I also thought about defining a linked server named Y in Z, but pointing to itself. Here I was only able to make a linked server named localhost pointing to itself, and this does not help solve my problem.

It is possible to create a loopback linked server with the name Y using SSMS by selecting "Other data source" (instead of SQL Server) and then specifying a SQL Server provider. This can also be done via T-SQL:

EXEC master.dbo.sp_addlinkedserver 
      @server = N'Y'
    , @srvproduct=N''
    , @provider=N'SQLNCLI11'
    , @datasrc=N'.'
    , @catalog=N'YourDatabase';

You'll need to also configure the linked server security context according to your requirements.

2
  • yes, that worked and was exactly what I was looking for - thank you very much! With one fell swoop, now 50+ views are working!
    – pygri
    May 7, 2020 at 12:03
  • Glad it helped, @pygri. I've found using synonyms during initial development is sometimes useful for linked server and database name abstraction. This allows the synonyms to be changed without changing the referencing code but one has to plan ahead to do that.
    – Dan Guzman
    May 7, 2020 at 12:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.