[SOLVED] Create alias for SQL server to use in query

Create alias for SQL server to use in query

My SQL server instance name is in the format: 'My-Server\InstanceName'

I want to give the server the alias 'InstanceName'. I did this in the Configuration Manager. When I connect to the server, I'm able to connect using that alias name. However, when I want to fully qualify objects in a query (eg. select * from InstanceName.Database.dbo.Table), it does not work. It says it cannot find that server in sys.servers. Why is this? It seems like it is not carrying the alias name down into the query after it connects.

Thanks!

Solution

InstanceName and alias are two different things.

For local server use Database.dbo.Table

To connect to another server (or another instance) you should add Linked Server https://learn.microsoft.com/en-us/sql/relational-databases/linked-servers/linked-servers-database-engine.

Usually you do not need any alias, but if you really want to do this use example:

EXEC sp_addlinkedserver     
   @server=N'S1_instance1',   
   @srvproduct=N'',  
   @provider=N'SQLNCLI',   
   @datasrc=N'S1\instance1'