Database Professional: project with random connectivity error to Linked servers

Our main database has a number of references to external databases using Linked Servers. Because of these some stored procedures have fully-qualified table names [linked server].[database].[schema].[table].

Initially when i was developing the database project, I created the external databases to simulate the Linked servers by directly them back to the local database server. When the rest of my colleagues picked up the project, they hit errors like 

Error 2 TSD4001: The OLE DB provider "SQLNCLI" for linked server "Extern_Server" does not contain the table ""Extern"."dbo"."HistCurr"". The table either does not exist or the current user does not have permissions on that table. (SQL error = 7314) Acquire.proc.sql 16 1 Database

This happens despite establishing the databases and Linked servers. When one peeks around manually, the Linked server catalogs can be browsed and queried just fine. Each of my colleagues went through a random sequence of service restart, system reboot, re-opening of Visual Studio before resolving the issue. We could not properly pin-point what made the error go away.

Has anybody working on Linked servers ever encountered similar problems?

I have no problems with Linked Servers in my previous company. A find on Google got me these:

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=3054396&SiteID=17 and

http://forums.asp.net/t/998962.aspx

It seems that the casing of naming the linked servers is important. Try using uppercase? You may also want to take a look at this post http://kbalertz.com/294459/Query-Oracle-Table-Contains-Lowercase-Characters.aspx.

These are Linked servers to other SQL Servers. In the development environment, it is the same server instance.

The cases are all the same and not modified between these errors. We do not use a case-sensitive collation either. sysservers and sys.servers definitely show those servers registered.

I tried to search for more answers and saw these posts:

1. http://www.themssforum.com/SQLServer/Intermittent-connection-459558/ which mentioned to turn off the auto close feature for production databases plus some other considerations to look out for when using linked servers.

2. http://www.sqlmag.com/Article/ArticleID/49981/sql_server_49981.html contains a solution that resolve the error in your first post, which still deals with the four-part-name.

Else, post the question to http://sgsql.sqlgroups.com/. There should be more sql servers experts (and heroes) there to help.

bronkman:

http://www.themssforum.com/SQLServer/Intermittent-connection-459558/ 

This pertains to database/network connections and not Linked servers per se. What we see here is the failure to recognise that the Linked server entries, which do exist in sysservers, is reported to be not.

ADD: sorry, i should be explaining that on top of the original message above, sometimes the error message can be along the line of "linked server not found in sys.servers" or "sysservers".

http://www.sqlmag.com/Article/ArticleID/49981/sql_server_49981.html

The database table references are already fully-qualified. As of now, it resolved itself and works. Which we do not really know why and how.

Ok we seem to have found the "solution" to the problem.

Open up the stored procedure that has the "offending" Linked server/table reference and simply edit the file (with nothing) and re-save it.

For some inexplicable reason, Visual Studio cannot update its view of the Linked server - even persisting through service or OS restarts - until the stored procedure is modified.