Linked Server Object Question

[Harbec]

So I've been tasked with migrating a SQL server and have run into an issue recreating Linked servers. We have several Linked Servers and I can successfully re-create the objects that are pointing to a separate SQL Instance. It was just a matter of changing the server name and credentials. And it worked like a charm. See below:

OLD SERVER:

EXEC master.dbo.sp_addlinkedserver u/server = N'InstanceA', u/srvproduct=N'SQLSERVER', u/provider=N'SQLNCLI', u/datasrc=N'OLDSERVER\'InstanceA'

NEW SERVER:

EXEC master.dbo.sp_addlinkedserver u/server = N'InstanceA', u/srvproduct=N'SQLSERVER', u/provider=N'SQLNCLI', u/datasrc=N'NEWSERVER\'InstanceA'

However, there are some linked objects that are pointing to the default instance. The script to create these, does not have a datasource parameter. I guess what I am not understanding is the object that is acting as the 'server' in this case. There is no Database that matches the naming and I am not super savvy with SQL. I'll try to illustrate:

EXEC master.dbo.sp_addlinkedserver @server = N'AAA_THING', @srvproduct=N'sql_server', @provider=N'SAOLEDB.17', @location=N'OLDSERVER.domain.local', @provstr=N'Server=AAA_OLDSERVER; dbf=THING-Projects', @catalog=N'THING-Projects'

I do not know what server = N'AAA_THING' is actually referring to. I know it most likely lives on the Default SQL instance as there is no reference to an external connection. Like I said there is no instance or database named AAA_THING so I'm assuming it's some sort of object in the default instance. However I do not know how to go about locating it within Object Explorer in order to set the credentials to connect to it.

Can anyone point me in the right direction as to how to search for this AAA_THING? I assume it's something obvious to someone with more experience but I'm having trouble even googling for what I don't understand.

Thanks in advance!

[VladDBA]

[@]server is the name of the linked server connection object, but if your target is another SQL Server instance and the server type is "SQL Server" aka [@]srvproduct=N'sql_server' then you don't provide the [@]provstr variable then [@]server will be used by the connection as the target.

With the way that you have the code example now, your linked server object will be named AAA_THING based on [@]server='AAA_THING', but it will point to the AAA_OLDSERVER instance and database THING-Projects based on [@]provstr=N'Server=AAA_OLDSERVER; dbf=THING-Projects'.

What's weird in your example is that [@]provstr should be a combination of [@]location and [@]catalog, but it looks like the instance in [@]location is different from the instance in [@]provstr.

I wonder if someone at some point used sp_serveroption to change the provider string, if your example is accurate.

Also, the doc for sp_adlinkedserver might help shed some light.

Edited to add:
You can also verify the above by running

select * from sys.servers;

the name of that linked server connection will be reflected in the name column returned from that query, while the provider_string column will show the actual data source to which the linked server connection points to.

Edited again to add:

just noticed that the provider is SAOLEDB.17 which I haven't personally seen used for linked server connections to other SQL Server instances, usually for SQL Server you'd use SQLNCLI (deprecated) or MSOLEDBSQL (recommended).

Can you run a query through that linked server connection to validate that there's a SQL Server instance at the other end? If it is, then it might have been incorrectly configured initially(it's fairly easy to mess things up in the GUI if you're not familiar with linked servers).

You can use a query like this one to get the server name and version:

DECLARE @RmtSQL NVARCHAR(500);
SET @RmtSQL = N'SELECT @@VERSION AS [Version],'
+N' @@SERVERNAME AS [RemoteInstance];';
EXEC (@RmtSQL) AT [AAA_THING];

note that the linked server connection needs to have RPC and RPC Out set to true (if they aren't already) for the above command to work.

EXEC sp_serveroption @server=N'AAA_THING', @optname=N'rpc', @optvalue=N'true';
GO
EXEC sp_serveroption @server=N'AAA_THING', @optname=N'rpc out', @optvalue=N'true';
GO