SQL Script To Add A Linked Server

After doing a little work linking databases together for a report, I ended up creating a script to be used within a stored procedure to quickly and easily re-add the linked server (which is required after a reboot of the SQL Server.

To run the script, change the three highlighted fields (server-name, username and password) and click Execute in SQL Server Management Studio:

DECLARE @Server AS VARCHAR(50)
DECLARE @Username AS VARCHAR(50)
DECLARE @Password AS VARCHAR(50)

SET @Server = 'server-name'
SET @Username = 'username'
SET @Password = 'password'

CREATE TABLE #linkedservers
	(SRV_NAME VARCHAR(50)
	,PROV_NAME VARCHAR(50)
	,SRV_PROD VARCHAR(50)
	,SRV_DATA VARCHAR(50)
	,SRV_STRING VARCHAR(50)
	,SRV_LOC VARCHAR(50)
	,SRV_CAT VARCHAR(50))

INSERT INTO #linkedservers
EXEC sp_linkedservers

IF (SELECT COUNT(*) FROM #linkedservers WHERE SRV_NAME = @Server) < 1
	EXEC sp_addlinkedserver @Server, 'SQL Server'

DROP TABLE #linkedservers

EXEC sp_addlinkedsrvlogin @Server, 'false', NULL, @Username, @Password