Create database on remote server via Linked Server

Question

I am automating deployments of our admin tools. When a new server is added to our monitoring system I have a trigger that creates a linked server.

I am trying to get my code to create database to work via the linked server from the central server.

Any tip and tricks on accomplishing this task?

Answer 1

Typically, you can't execute DDL against a linked server. There is a trick though. You can call sp_executesql remotely.

You'll need to set the Linked Server's RPC Out property to True before attempting.

EXEC [yourlinkedserver].tempdb.dbo.sp_executesql N'CREATE DATABASE Test;';