Linked server sql statements

July 23, 2010 at 18:29
filed under Dynamics AX
Tagged , , ,

Hi all, hope you are well.

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());

no comments

RSS / trackback

respond