Categories
Database Development

Using listener_name in 4 part naming convention gives sys.servers error

app uses cross db connections, the dbs are in different AG’s, so I need them to use 4 part naming convention – in the place of servername would be listener name, but when I run a simple query like:

select * from Listener_Name.DatabaseName.[schemaname].[objectname]

I get:

Could not find server ‘Listener_Name’ in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

Listener_Name is not in sys.servers table.

My current environment has 2 nodes and 3 AGs, each having their own listeners. Do I need to add the 3 listener names as linked servers on both nodes? And does anyone have a script on how best to create the linked server using listener name? I would like it to be similar to the local server which shows up on the sys.servers table with server_id 0.

Leave a Reply

Your email address will not be published. Required fields are marked *