SQLTeam.com | Weblogs | Forums

Linked Servers- One Works, One Don't


I have two server configured with a linked server to a third "master" server. The linked servers both use the same SQL Login security context to get to the master. Both can see and select from the tables. BUT when I try to execute a stored procedure via the linked server, one returns data and the other returns an error message saying that it can't find the stored procedure.

Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'MyRemoteDatabase.dbo.MyRemoteSproc'.

The linked server configurations are the same. Both servers are SQL 2008 R2 (build 4339). The CPU and memory are different between them but I can't see how that would matter. The server configuration (sp_configure) seems to be the same and the MSDTC configuration seems to be the same. Any and all suggestions are welcomed...


permissions? How are you linking to the target server? What account? Is that account able to run that proc on that server?


Found it! (Ugh!) The SSMS front end displays the same name and configuration information for the linked server on each server, however, when I looked in the sys.servers table, the erroneous linked server has a different data_source value. On first blush, the linked servers appear to be referencing the same "master" server. In reality, the second server is actually pointing to a different server which (surprise, surprise) does not have the desired sproc. Thank-you for your response anyway.