Is it possible to create a table on a linked server?
1 Open SQL Server Management Studio, navigate to the Object Explorer pane, and select Server Objects > Linked servers > Providers. 2 Right-click mrOledb. Provider and select Properties. 3 Select allow in process, and then click OK.
Linked servers enable the SQL Server Database Engine and Azure SQL Managed Instance to read data from the remote data sources and execute commands against the remote database servers (for example, OLE DB data sources) outside of the instance of SQL Server.
Unfortunately you cannot link an entire MySQL database to another MySQL database like you can with MS SQL. However, you can link individual tables. A federated table is a local table you create that points to a table on another server. You can run queries and stored procedures just like any other table.
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With