Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Could not execute procedure on remote server

Author  Topic 

chulheekim
Starting Member

46 Posts

Posted - 2010-09-09 : 17:34:52
I have used this linked server for many years to access to tables and run procedures remotely from sql 2005 to sql 2000. Recently, I found out that it doesn't work if it tries to run a stored procedure remotely. The error msg is "Could not execute procedure on remote server because SQL Server is not configured for remote access. ... " I have no problem with accessing tables remotely. I double-checked things on the linked server, logins, and etc. I couldn't find anything wrong. I don't understand why stored procedure is not ok and table is ok. I recently had to reboot the sql server 2005. I don't know whether it is related. Any idea?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-09 : 17:41:07
Rebooting shouldn't affect it. You have to modify the linked server to allow data access. You do this change through the linked server GUI in SSMS.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

chulheekim
Starting Member

46 Posts

Posted - 2010-09-10 : 00:47:23
The linked server has been there many years with no problem. I noticed that it still works good if I try to access to tables remotely. However, why not stored procedures?
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-10 : 16:16:55
Is there any possibility that someone dropped/recreated the linked server? The default for the data access is OFF.

If not, then maybe you've encountered a bug. Make sure you are on the latest build for each product. What does SELECT @@VERSION show on each?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-09-10 : 16:40:46
for stored procedures, in addition to data access, you need to enable RPC and RPC out in the linked server properties
Go to Top of Page

chulheekim
Starting Member

46 Posts

Posted - 2010-09-10 : 20:50:12
Somebody suggested to reboot the server. I did it. and the linked server is fixed. Unbelievable !!!
Thank you so much.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-10 : 23:17:33
Hmmmm, interesting...

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page
   

- Advertisement -