Remote Stored Procedures


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.




Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL &  .NET
Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL & .NET
ISBN: 0072262281
EAN: 2147483647
Year: 2006
Pages: 165

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net