Unable to execute a remote stored procedure over a linked server

I ran into an interesting situation when executing a Remote Stored Procedure on Linked Server using four part query.  Vista-3139 which is a SQL Server 2005 instance is linked to the SERV2003\SQLDUMMY which is a SQL Server 2000 named instance.


When I tried to execute a stored procedure:

EXEC [SERV2003\SQLDUMMY].SQLAdmin.dbo.sp_ddlevents Got the following error message.

Could not connect to server SERV2003\SQLDUMMY ' because '' is not defined as a remote login at the server. Verify that you have specified the correct login name.

I checked the Security settings of the linked server and it was set to “Be made using the login's current security context", which is valid considering that the user has all the permissions to login to the remote SQL server.


Then I changed the security context to use a SQL authentication, but still it failed with the similar error message as described above. To troubleshoot this further, I ran the query select * from master..sysservers and here's how the output looked like.


Now this field isremote caught my attention. So what does this value isremote mean ?

Well, According to Books Online the column isremote is 1 if server is a remote server, else 0 if server is a linked server.  Linked servers are a superset of "remote servers." Remote servers allowed the running of stored procedures on distributed SQL Server machines. SQL 2000 BOL states, "Support for remote servers is provided for backward compatibility only. New applications that must execute stored procedures against remote instances of SQL Server should use linked servers instead."

What does this mean in terms of a valid setting to execute a stored procedure over a linked server?

isremote = 1 indicates that Execute Query Fails

isremote = 0 indicates that Execute Query Works

Since isremote value is a part of the system tables of the Master Database, we cannot directly modify it to change its value. Dropping the linked server (SERV2003\SQLDUMMY) and re-creating the linked server resolved the issue.  The isremote value for the linked server which had been set to 1, after dropping and recreating the linked server the isremote value now was set to Zero.


This is applicable in both SQL Server 2005 and SQL Server 2008 environments. SQL Server 2005, 2008 equivalent of sysservers is sys.servers which has a column is_linked.  You can check this by running this

       Query select * from sys.servers

Another interesting that I stumbled across while working on this issue is that in a SQL Server 2000 scenario, the isremote column values are inverted.


Once again running the query, select * from master..sysservers shows the results as below :


Thus as seen above the value of isremote value of the linked server (VISTA-3139) = 1 is valid in a SQL Server 2000 linked server.


Author : Ajay (MSFT), SQL Developer Engineer , Microsoft

Reviewed by : Jason(MSFT), SQL Escalation Services, Microsoft

Comments (6)
  1. kaat2001@gmail.com says:

    Thanks a lot, your post was very helpfull!

  2. A says:

    What if this linked server is created by replication ? dropping linked server will necessitate dropping subscribers 1st ;

  3. Terrence Holmes says:


    try this first, especially if your linked server was created during creation of replication.

  4. Johnny says:

    Thank god you were suspicious of that isremote flag

  5. Av111 says:

    very helpful

  6. After 3 hours of fiddling around, I finally found your post and was able to resolve my problem.
    Thanks a lot! As the remote server I was having problems with, was created as part of the replication wizard, it’s probably fair to assume that the “wrong” value was set from there. Recreating the server (and dropping all the replications before) was not acceptable. I thus create an alias DNS entry and created a new linked server based on that…

Comments are closed.

Skip to main content