Linked server sql statements

Some time ago, I talked about executing direct sql statements, and now I want to share some sql statements that I used to manage linked server connections.
What follows are 4 sql statements that allow you to add en remove linked servers on a database at runtime.
Some assumptions:
- There is a str variable named “query” that will contain the query
- there is a parm method on the class that return the sql server (“server” or “server\instance”)
- there is a parm method that returns a username
- there is a parm method that returns a password
Check if linked server exist
First check if the linked server doesn’t exist yet, or you will get an error when you try to add one that already exists.

query = strfmt("select top 1 * from sys.servers where name = '%1'", this.parmServer());

Add linked server When the linked server doesn’t exist, add it.

query = strfmt("EXEC sp_addLinkedServer @server = '%1', @srvproduct=N'SQL Server'", this.parmServer());

Add linked server login Optionally, you can add a login that will be used to connect to the linked server.

query = strfmt("sp_addlinkedsrvlogin @rmtsrvname = '%1' ,@useself = FALSE, @locallogin = NULL, @rmtuser = '%2', @rmtpassword = '%3'",
                    this.parmServer(),
                    this.parmUsername(),
                    this.parmPassword());

Remove linked server Optionally, you can remove the linked server.

query = strfmt("EXEC sp_dropserver '%1', 'droplogins'", this.parmServer());