In SQL Server, the OPENQUERY rowset function enables you to execute a pass-through query on a linked server.
OPENQUERY is commonly referenced in the FROM clause of a query as if it were a table, but it can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement.
This article presents an example of using OPENQUERY to do an DELETE pass-through query.
In SQL Server, you can use OPENQUERY to execute a pass-through query on a linked server.
OPENQUERY is commonly referenced in the FROM clause of a query as if it were a table, but it can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement.
This article presents an example of using OPENQUERY to do an INSERT pass-through query.
In SQL Server, the OPENQUERY rowset function enables you to execute a pass-through query on a linked server.
OPENQUERY is commonly referenced in the FROM clause of a query as if it were a table, but it can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement.
This article presents an example of using OPENQUERY to do an UPDATE pass-through query.
If you’ve previously created a linked server, but you now want to update one or more of its server options, you can do that either via the SQL Server Management Studio GUI or with T-SQL.
If you choose to do it with T-SQL, the sp_serveroption system stored procedure is what you need.
If you’ve encountered error Msg 7411, Level 16 in SQL Server, it’s because you need to enable “RPC out” on the linked server that you’re trying to execute code on.
I was trying to set up a up a linked server from SQL Server to PostgreSQL when I got error Msg 7222, Level 16 “Only a SQL Server provider is allowed on this instance”.
The message is reasonably self explanatory, but it still didn’t tell me what it was about my instance that prevented it from being allowed.
It didn’t take long to find out.
RPC stands for Remote Procedure Calls. It needs to be enabled before you can execute stored procedures on a linked server.
If you’re not sure whether it’s enabled on a linked server, you can check it’s setting by querying the sys.servers system catalog view.
You may occasionally need to enable the “RPC Out” option on a linked server. This option enables RPC to the given server.
RPC stands for Remote Procedure Calls. RPC is basically a stored procedure being run remotely from Server 1 to linked Server 2.
If you don’t enable this and you try to execute a stored procedure on the linked server, you’ll probably get error Msg 7411 telling you that the server is not configured for RPC.
Anyway, you can enable/disable this option either using SQL Server Management Studio (SSMS) or with T-SQL.
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.
If you need to create a table on a linked server, you can do this either directly on the remote server, or you can do it by running a script from your local server.
Here are two ways of using T-SQL to create a table on a linked server.
Both methods use the EXECUTE/EXEC statement. But each uses a different approach.
