One way of creating a database on a linked server, is to simply jump over to that server and create it locally.

But you’d probably feel a bit cheated if I included that as one of the “2 ways” to create a database on a linked server.

Also, while that option is fine if you’re able and willing to do it, this article shows you how to do it remotely using T-SQL, without having to jump over to the local server. Plus you might find this technique quicker than jumping over to the other server.

Both of the “2 ways” involve the EXECUTE statement (which can also be shortened to EXEC). We can use this statement to execute code on the linked server, and that includes creating a database on it.

Prerequisite

Executing stored procedures on a linked server require that you enable RPC Out (if it isn’t already enabled).

You can check if RPC Out is enabled by querying the sys.servers catalog view.

The following code enables RPC Out on a linked server called Homer:

EXEC sp_serveroption 'MyLinkedServer', 'rpc out', 'true';

Once that’s enabled, we can go ahead and create the database.

Method 1

Here’s the first method for creating a database on the linked server.

EXEC ('CREATE DATABASE Pets') AT Homer;

In this example, I create a database called Pets on a linked server called Homer.

The EXECUTE/EXEC statement can be used to send pass-through commands to linked servers.

To do this, include pass-through command in parentheses after the EXECUTE/EXEC keyword, then follow that up with AT LinkedServerName, where LinkedServerName is the name of the linked server that you want to create the database on.

Therefore, all we need to do is include our CREATE TABLE statement as an argument to this statement.

Note that the linked server name is the name that you gave it when you created the linked server. It’s not the actual server name of the remote server.

Method 2

The second method is similar to the first, except in this case we pass our command to the sp_executesql system stored procedure on the remote server.

EXEC Homer.master.dbo.sp_executesql N'CREATE DATABASE World;';

In order to do this, we need to use the four-part name, which includes the name of the linked server. If we didn’t do this, we would end up executing sp_executesql on our local server instead of the remote server.

Pass the Statement as a Variable

You can also pass the CREATE DATABASE statement as a variable to the EXEC statement.

Like this:

DECLARE @tsql nvarchar(max);
SET @tsql = 'CREATE DATABASE Pets';
EXEC (@tsql) AT Homer;

Although, if you try to then switch to the database and create the tables, you might run into problems, even if you add GO in the usual places. This is probably because the GO keyword is not actually part of T-SQL, and it’s only recognised by certain utilities.

Therefore, you might need to run a separate script to create the tables.