web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Blogs / Klaas Deforche's Microsoft Dynamics AX Deve... / Linked server sql statements

Linked server sql statements

Views (2880)
Klaas Deforche Profile Picture Klaas Deforche 2,433

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

Comments

*This post is locked for comments