September 9, 2010 at 10:54 pm
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 recently had to reboot the sql server 2005. I don't know whether it is related. Any idea?
September 10, 2010 at 12:12 am
This seems to be a problem that remote access is not properly configured.
exec sp_configure 'remote access', 1
go
reconfigure
go
Commit to Never Ending Improvement
September 10, 2010 at 4:13 am
Check that you have Remote Procedure Calls enabled in your linked server properties. In SSMS, expand Server -> Server Objects ->Linked Servers -> Right Click on your linked server and go to properties -> select Server Options -> Check RPC and RPC Out are enabled (TRUE).
September 10, 2010 at 8:28 am
According to your recommendation, I doublechecked the linked server is set properly. and I also ran that command. No difference. Any other idea please?
September 10, 2010 at 8:52 am
are you getting any kerbos errors in your application log after reboot?
If so you can try to reconfigure your linked server once again after deleting it properly
----------
Ashish
September 10, 2010 at 1:44 pm
I don't see any of those.
September 10, 2010 at 1:49 pm
ok what if you configure the same linked server again with different name? you still having the problem after this?
----------
Ashish
September 10, 2010 at 1:54 pm
Pardon me if I misunderstood your question. My understanding is that you can have only one linked server per each remote server. In other words, the linked server name has to be the name of the remote server name. You can't have two linked servers for that remote server.
September 10, 2010 at 1:57 pm
you can have multiple linked server with same server name but yes their name must be unique. So like server1,server2,server2 but in connection string, it must be pointing to same server.
----------
Ashish
September 10, 2010 at 2:20 pm
I don't quite understand. But are you saying we can have second and even third one by choosing Other data source from the Server type? Can you please explain me how to set that up? Thank you so much.
September 10, 2010 at 2:26 pm
what i am saying is you can have different linked server pointing to same server but linked server name must be different and for all these linked server if they need to be linked to same server then data source and server name will be same.
try creating linked server pointing to same server with same data source but linked server name which you creating must be different name then existing one
----------
Ashish
September 10, 2010 at 3:15 pm
Ashish is correct.
Select a New Linked Server Name and then select the Option "Other Data Source" and then complete the remaining feilds.
Thank You,
Best regards,
SQLBuddy
September 10, 2010 at 3:21 pm
I'm getting very close. However, I'm having trouble with what to put on Provider string. Here's what I have so far. Thank you so much.
Provider: Microsoft OLE DB Provider for SQL Server
Product name: SQL Server
Data source: TheSQLServerName
Provider string:
Thank you again.
September 10, 2010 at 3:36 pm
Just specify the SQL Server Name and then chose a security option.
Thank You,
Best Regards,
SQLBuddy
September 10, 2010 at 3:59 pm
I tried the both. Putting the sql server name on it and leaving it blank. After putting login information at the Security tab and setting Rpc/Rpc Out to True, I'm getting error saying
"The linked server has been created but failed a conection test.
...
An Exception occurred while executing a Transaction-SQL statement or batch.
..
Named Pipes Provider: Could not open a connection to SQL Server.
OLE DB provider "SQLNCLI" for linked server returned message "Login timeout expired".
... "
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply