This type is actually a user-defined stored procedure that resides on a remote server. The only challenge implicit in this type of stored procedure is that the local server has to be set to allow the remote use of stored procedures.
Run the following code on the local server:
EXEC sp_addlinkedserver LocalServer, N'SQL Server' EXEC sp_addlinkedserver RemoteServer EXEC sp_configure 'remote access', 1 RECONFIGURE
Stop and restart the local server.
To set up access for a login "sa" from the local server to remote server, run the following code on the remote server. It is critical that you log in using SQL Server Authentication and it is assumed that the logins on different servers have the same password.
EXEC sp_addlinkedserver RemoteServer, local EXEC sp_addlinkedserver LocalServer EXEC sp_configure 'remote access', 1 RECONFIGURE GO EXEC sp_addremotelogin LocalServer, sa, sa GO
Stop and restart the remote server.
Using the "sa" login, you can now execute a stored procedure on the remote server from the local server.
Tip | Microsoft, in fact, considers this mechanism as a legacy of older versions of SQL Server. Heterogeneous queries are the recommended way to execute stored procedures or access tables on other servers. |