CODEWITHSUNDEEP
sql-serversql-server-2005
user329592
user329592

Reputation: 141

Is it possible to create a table on a linked server?

Is it possible to create a table on a linked server?

Upvotes: 15

Views: 26451

Answers (3)

Mikkel Tronsrud
Mikkel Tronsrud

Reputation: 747

The solution from Arpit works fine.

exec

('CREATE TABLE DatabaseName.dbo.TableName
(
  column1 datatype,
  column2 datatype,
  column3 datatype
)') at [LinkedServer];
go

How ever, when you get the error "Msg 7411, Level 16, State 1, Line 1 Server 'MyLinkedServer' is not configured for RPC." you need to change the RPC-parameters on the linked server connection. As default RPC is set to false. It needs to be set to true.

Linked server options

This allows you to run procedures on the linked server. You must allow this because the solution does not send the "Create table" statement as a SQL command to the linked server. It sends the statement as a string which in turn is executed as a procedure on the remote server.

Hope this helps.

Upvotes: 5

Vince Horst
Vince Horst

Reputation: 5308

If you're using SQL Server Management Studio and the SQLCMD mode:

-- To enable "SQLCMD mode" in SQL Server Management Studio (SSMS):
-- In the menu toolbar: Query > SQLCMD mode

:setvar LinkedServer "YourLinkedServerNameHere"
:setvar DestinationDb "YourDestinationDatabaseNameHere"
GO

EXEC ('
CREATE TABLE $(DestinationDb).dbo.YourTableNameHere (
    [Field1] INT NOT NULL,
    [Field2] INT NOT NULL
)
') AT $(LinkedServer)
GO

EXEC ('DROP TABLE $(DestinationDb).dbo.YourTableNameHere') AT $(LinkedServer)
GO

Upvotes: 2

user1668112
user1668112

Reputation: 221

exec

('CREATE TABLE DatabaseName.dbo.TableName
(
  column1 datatype,
  column2 datatype,
  column3 datatype
)') at [LinkedServer]

Upvotes: 22

Related Questions