I recently did a post on testing a linked server where I said I would explain why I wanted to make the test. Basically I needed to scan a few hundred instance names and do the following

• Check if the instance is one we have access to or even exists? If not make a note of the error so we can tell the difference.
• Collect information like instance size (total size of all databases), CPU count, memory count etc.
• Collect a list of database names on the instance, their status, size, etc.

So the first thing I did was throw the list of instance names into a cursor and then put the code from my last post inside the loop.

-- Create MyLinkedServer using the current server so that it exists and
-- the code will compile.
EXEC master.dbo.sp_addlinkedserver @server = N'MyLinkedServer', @srvproduct=N'', @provider=N'SQLNCLI', @Datasrc = @@SERVERNAME; 
GO

-- Create temp table to hold instance names. 
-- You'll probably want a permanent table.
CREATE TABLE #InstanceList (InstanceName NVARCHAR(256));
INSERT INTO #InstanceList VALUES 
	('InstanceA'), 
	('InstanceB'), 
	('InstanceC');

-- Declare vars
DECLARE @sql NVARCHAR(max);
DECLARE @InstanceName NVARCHAR(256);

-- setup cursor to loop through servers
DECLARE InstList CURSOR FOR 
 SELECT InstanceName
 FROM #InstanceList;

OPEN InstList
FETCH NEXT FROM InstList INTO @InstanceName
WHILE @@FETCH_STATUS = 0
BEGIN

	BEGIN TRY  
		EXEC master.dbo.sp_addlinkedserver @server = N'MyLinkedServer', @srvproduct=N'', @provider=N'SQLNCLI', @Datasrc = @InstanceName; 
		-- Test the linked server.
		EXEC sp_testlinkedserver @server = N'MyLinkedServer'  

		EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'MyLinkedServer',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL  
	END TRY
	BEGIN CATCH

		INSERT INTO dbo.[LinkedServerLog] VALUES (
			@InstanceName   
			,ERROR_NUMBER()  
			,ERROR_SEVERITY()  
			,ERROR_STATE()  
			,ERROR_PROCEDURE()  
			,ERROR_LINE()  
			,ERROR_MESSAGE());
	END CATCH
   
 FETCH NEXT FROM InstList into @InstanceName;
END
CLOSE InstList;
DEALLOCATE InstList;

-- Cleanup
IF EXISTS (SELECT * FROM sys.servers WHERE name = 'MyLinkedServer')
 EXEC master.dbo.sp_dropserver @server=N'MyLinkedServer', @droplogins='droplogins';

I now have a piece of code that loops through a list of instances and creates a linked server for each one. It then tests that linked server to make sure I can connect and if I can’t store the error into an error table. From there I can see which instances I couldn’t connect to and which I could connect but couldn’t log into.

Now, all I have to do is add code into the try block that uses the linked server to collect information.

-- Create MyLinkedServer using the current server so that it exists and
-- the code will compile.
EXEC master.dbo.sp_addlinkedserver @server = N'MyLinkedServer', @srvproduct=N'', @provider=N'SQLNCLI', @Datasrc = @@SERVERNAME; 
GO
-- Create temp table to hold instance names. 
-- You'll probably want a permanent table.
CREATE TABLE #InstanceList (InstanceName NVARCHAR(256));
INSERT INTO #InstanceList VALUES 
	('InstanceA'), 
	('InstanceB'), 
	('InstanceC');

-- Create temp table to hold database sizes.
CREATE TABLE #DBList (
	InstanceName NVARCHAR(256), 
	DatabaseName NVARCHAR(256), 
	DatabaseSize DECIMAL(17,5)
	);

-- Declare vars
DECLARE @sql NVARCHAR(max);
DECLARE @InstanceName NVARCHAR(256);

-- setup cursor to loop through servers
DECLARE InstList CURSOR FOR 
 SELECT InstanceName
 FROM #InstanceList;

OPEN InstList
FETCH NEXT FROM InstList INTO @InstanceName
WHILE @@FETCH_STATUS = 0
BEGIN

	BEGIN TRY  
		IF EXISTS (SELECT * FROM sys.servers WHERE name = 'MyLinkedServer')
			EXEC master.dbo.sp_dropserver @server=N'MyLinkedServer', @droplogins='droplogins' 

		EXEC master.dbo.sp_addlinkedserver @server = N'MyLinkedServer', @srvproduct=N'', @provider=N'SQLNCLI', @Datasrc = @InstanceName; 
		-- Test the linked server.
		EXEC sp_testlinkedserver @server = N'MyLinkedServer'  

		EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'MyLinkedServer',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL  
		INSERT INTO #DBList
		SELECT @InstanceName, dbs.name, SUM(size)/128/1024.0
		FROM MyLinkedServer.master.sys.databases dbs
		JOIN MyLinkedServer.master.sys.master_files dbfiles
			ON dbs.database_id = dbfiles.database_id
		GROUP BY dbs.Name, dbs.database_id;
  
	END TRY
	BEGIN CATCH

		INSERT INTO dbo.[LinkedServerLog] VALUES (
			@InstanceName   
			,ERROR_NUMBER()  
			,ERROR_SEVERITY()  
			,ERROR_STATE()  
			,ERROR_PROCEDURE()  
			,ERROR_LINE()  
			,ERROR_MESSAGE());
	END CATCH
   
 FETCH NEXT FROM InstList into @InstanceName;
END
CLOSE InstList;
DEALLOCATE InstList;

-- Cleanup
IF EXISTS (SELECT * FROM sys.servers WHERE name = 'MyLinkedServer')
 EXEC master.dbo.sp_dropserver @server=N'MyLinkedServer', @droplogins='droplogins' ;

A couple of notes here. I have a piece of code at the top that adds MyLinkedServer to make sure it exists when the code starts. Otherwise it won’t compile. Also, because of the connection time for the test, particularly if you have a bunch of instances that you can’t log into/don’t exist, this script is going to take a while just to handle the loop. Make sure that the data collection code is hitting system tables and/or is as quick as you can make it.

Like I said in the title, this is pretty quick and dirty. This is the kind of thing you throw together because your manager wants some data collected from a bunch of sources ASAP and T-SQL is by far your best language. There are a lot of better ways to handle this.

BEGIN TRY  
	EXEC master.dbo.sp_addlinkedserver @server = N'MyLinkedServer', @srvproduct=N'', @provider=N'SQLNCLI', @Datasrc = @ServerName  
	EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'MyLinkedServer',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL  
END TRY
BEGIN CATCH
	INSERT INTO dbo.[LinkedServerLog] VALUES (
		@ServerName       
		,ERROR_NUMBER()  
		,ERROR_SEVERITY()  
		,ERROR_STATE()  
		,ERROR_PROCEDURE()  
		,ERROR_LINE()  
		,ERROR_MESSAGE());    
END CATCH

Unfortunately, if there is a problem with the server / server name the sp_addlinkedserver doesn’t seem to throw an error and sp_addlinkedsrvlogin throws an error that breaks me out of the try block. So after my Google-fu failed me I did what I frequently do and asked on #sqlhelp. And I was given an answer that led me to the solution.

https://twitter.com/Cynergistic1/status/1319753358513557504

It turns out there is a function sp_testlinkedserver, and that was the answer. Once the linked server was created I could test it and if it failed it jumped into the catch block and I could log what happened. I would still have a problem if sp_addlinkedsrvlogin failed, but in all of the cases where I tried it it ran just fine.

BEGIN TRY  
	EXEC master.dbo.sp_addlinkedserver @server = N'MyLinkedServer', @srvproduct=N'', @provider=N'SQLNCLI', @Datasrc = @ServerName  
	-- Test the linked server.
	EXEC sp_testlinkedserver @server = N'MyLinkedServer'  

	EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'MyLinkedServer',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL  
END TRY
BEGIN CATCH
	INSERT INTO dbo.[LinkedServerLog] VALUES (
		@ServerName       
		,ERROR_NUMBER()  
		,ERROR_SEVERITY()  
		,ERROR_STATE()  
		,ERROR_PROCEDURE()  
		,ERROR_LINE()  
		,ERROR_MESSAGE());    
END CATCH

What exactly was I doing? Well, you’ll have to wait a bit to find out.

And what those are is pretty simple. It’s an alias for the local server you are on. Note, that is not the instance, just the server. That means if you are using a named instance you need to include the instance name, and of course if you are using the default instance (or port TCP 1433) you don’t. So for example for the instance MyInstance you would use the following regardless of the server name.

(local)\MyInstance

I do want to repeat one important fact. This only works if the instance you are connecting to is actually on the machine you are connecting from. I’ve run a few tests and this also works with Azure Data Studio and connection strings. I should probably point out that I don’t like using these. I would rather actually see the name of the server listed. Now, maybe if you have a connection string and you plan on moving it up the SDLC (systems development life cycle) stack (dev, test etc) and don’t want to change the file when it’s being moved I could see it but that’s about it.

All it really means is, “Click Execute again, and you will be fine.”

FYI you can also get the same error when connecting to an on-premises instance and it still means the same thing.

Also along those lines here is another similar error that also means the same thing:

Translated as: “Click Execute again, and you will be fine.”

You can get additional information here.

I do want to give one warning though. When you see these errors trying again will almost always re-connect you. However, unless your connection is told to go directly to the database you need (as would happen with an Azure SQL DB connection) you may end up back at your default database.

It turns out that udl files are mapped to something called OLE DB Core Services.

This neat little tool will let you test or create connection strings.

Create

I haven’t found a way to just open the tool but if you create a udl file and double click on it then it will open.

Currently, it’s blank, and the first step is to confirm the provider on the provider tab. I’m switching from the OLE DB provider for MS SQL to SQL Server Native Client 11.0.

Next fill in the server, login (trusted or SQL Id) and the initial database if any.

Last but not least you have the Advanced options (only the connection time in this case) and then you can hit Test Connection. Assuming it tests correctly you can now close the tool and open the udl file with a text editor. In this case here are the contents:

[oledb]
; Everything after this line is an OLE DB initstring
Provider=SQLNCLI11.1;Integrated Security=SSPI;Persist Security Info=False;User ID="";Initial Catalog=&&o;Data Source=KENNETH-LAPTOP\SQL2016CS;Initial File Name="";Server SPN=""

And you’ll see that line 3 of the file is the connection string. Of course, there are other ways to create connection strings but this is pretty handy.

Now, my favorite part of this is the ability to test them.

Test

First, create a UDL file just like before and open it with a text editor. Here’s where things got weird. Those first two lines? I had to copy them exactly into the new file. I’m guessing there are other options here but I don’t know them and every letter had to be exact for this to work. Once that was done however I was able to put my connection string in with very limited information.

[oledb]
; Everything after this line is an OLE DB initstring
Provider=SQLNCLI11.1;Integrated Security=SSPI;Data Source=KENNETH-LAPTOP\SQL2016CS

I save the file, then double click on it and the editor comes back up. And this time I was able to just hit the connection test button and confirm that it works! I can of course also make changes, test them and then look in the file to see the results.

Additional reading: Timeless and Handy: The Microsoft Data Link File