April 18, 2006 at 11:43 am
I have two 2005 servers that are linked. Replication is working between them. When I run the following in a query window on the localserver, it works fine:
SELECT
* FROM [linkedserver].WMS.dbo.tblStatus
But when I run the statement below to execute a stored procedure, I get the error message that follows it.
EXECUTE
[linkedserver].WMS.dbo.spUpdateWmsStatus 'Available','123456','04/18/06'
Msg 18482, Level 14, State 1, Line 1
Could not connect to server 'linkedserver' because 'localserver' is not defined as a remote server. Verify that you have specified the correct server name. .
I found KB217395 but when going through the directions, I found that my 'localserver' is already in sysservers with srvid = 0. I cannot tell from the KB article if srvstatus must also be 0; it currently is NOT. But I don't want to restart SQL Server as an experiment to see if it will fix the problem, since it would affect many users.
April 18, 2006 at 12:24 pm
I fixed it....yeah!
First I had to set up a linked server from the remote server pointing back to the local server.
Then I was getting an error message telling me that my username "is not defined as a remote login at the server".
Looking around the forum, I found an entry by psribeiro and I followed his instructions to run this on the remote server:
exec
sp_AddRemoteLogin 'localserver','username'
Now all is well with the world.
September 9, 2008 at 4:14 am
Brilliant! I've been looking for this and it's solved all my problems. Nice one.
September 26, 2008 at 8:45 pm
AweSome.. thank U.. 🙂
October 10, 2008 at 8:16 am
Excellent! Worked for me as well.
June 24, 2009 at 6:21 am
I just wanted to point out that when moving to SQL 2008, this will no longer work. As per the BOL:
This feature will be removed in the next version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible. Use linked servers and linked server stored procedures instead.
Something to keep in mind...
June 24, 2009 at 7:36 am
Mike (6/24/2009)
I just wanted to point out that when moving to SQL 2008, this will no longer work. As per the BOL:This feature will be removed in the next version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible. Use linked servers and linked server stored procedures instead.
It is still in 2008, but deprecated for removal in the next version (R2 presumably).
INSERT...EXEC using a loop-back linked server is now supported in 2008, so long as MARS is not used. See http://msdn.microsoft.com/en-us/library/ms143359.aspx
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
October 1, 2009 at 3:44 pm
Lets say we can't use sp_addremotelogin, how do you do this in SQL 2008 using both SQL command and SQL management tool? I like to see both method.
August 13, 2010 at 9:28 am
I was having this problem as well on a sql2008 box. From the local server (where the linked server is defined), I executed SELECT * FROM master..sysservers and noticed that my linked server was showing up as isremote = 1, so it appears that my linked server was actually configured as a remote server.
The interesting thing is that I scripted out the linked (remote?) server, dropped it, and then executed the script (with no changes) which changed the entry in sysservers to isremote = 0 and resolved the issue with no other changes. I did not need to add a remote login.
Not sure why that was the case, but it may be something you want to look into before adding the remote login on the remote server since, as they say, sp_AddRemoteLogin is deprecated.
Tim Januario
October 26, 2010 at 12:58 pm
i have that same result. if you script the linked server that doesnt work, drop it, then run the script -- it will work.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply