Aliasing a linked server?

Question

I have a server which I need to have as a linked server to another server, using sp_addLinkedServer.

My server's name is in the format "DepartmentName.CompanyName.com", which I can usually change to [blah.blah.com], but which I can't use in the SQL Server Management Studio View creator, because it keeps deleting my square brackets...

On the other hand, because the @srvproduct is SQL Server, I can't specify a name for the server to use, either, so I can't just call it "DepartmentName".

I seem to be in a bit of a catch-22 here. Any suggestions?

Answer 1

The View editor in Management studio (and enterprise manager before it) is, shall we say, rather limited, and prone to exploding the number of references to a table/view if there are complex conditions.

It's much to be preferred that you learn to write CREATE/ALTER VIEW statements in query windows (there are options to script VIEWs to a new query window as ALTER, if you're wanting to update an existing view).

Alternatively, you can add the linked server using, say, the "Microsoft OLE DB Provider for SQL Server", "SQL Native Server", or any of a number of other providers, rather than using the "SQL Server" provider, and then you can specify a different name for the linked server. (We do this in my shop so that our test servers refer to their partners using the same names as are used on our production servers)

E.g.:

EXEC master.dbo.sp_addlinkedserver @server = N'ALIAS', @srvproduct=N'ACTUALSERVER', @provider=N'SQLOLEDB', @datasrc=N'ACTUALSERVER'