Calling Remote Stored Procedures
Calling stored procedures across a linked server requires the four-part naming convention described earlier for direct SQL access of remote objects. The options of RPC and RPC Out must also be enabled in the configuration of the linked server. Stored procedures will run on the linked server just like queries using the OPENQUERY function.
Using stored procedures offers many benefits. Databases can be made more secure by a carefully implemented stored procedure design that gives users direct access only to stored procedures and not to tables. Also, if multiple applications need access to the same objects, stored procedures need only be written once; the applications can use the stored procedures rather than requiring their own versions of DML. (Data modification language is a subset of SQL that actually changes data in the objects with INSERT, UPDATE, and DELETE. SELECT isn't truly part of it.)
Text fields become a problem, however, because SQL variables cannot be declared to have the datatype text. Also, stored procedures are a great solution for data inserts, updates, and deletes, but cannot pull back rowsets to be combined in local SQL statements, as with OPENQUERY. So, while stored procedures are good for most requirements, they're not a total solution for remote data access.