Once connection to linked server fails, SQL Prompt gives up
MikeyC
Posts: 249 Bronze 3
I ran into a problem today when the first time I tried to create a query against a linked server and the server was down SQL Prompt gave me an error. Later once the server was back up I can't find anyway to get SQL Prompt to attempt to connect to the server again. "Refresh Suggestions" would be the obvious solution, but it doesn't help. I assume exiting SSMS and starting again will resolve the problem, but I have a lot of windows open and queries in progress so that isn't really an option.
Is there any way to force SQL Prompt to completely clear it's local cache and start fresh short of re-starting SSMS? If not, I really think it is a very needed option that needs to get added.
Is there any way to force SQL Prompt to completely clear it's local cache and start fresh short of re-starting SSMS? If not, I really think it is a very needed option that needs to get added.
Comments
SQL Prompt's "Refresh cache" option only refreshes the database specified in the current SSMS connection. As I suspect you're already aware, closing down SSMS and reopening it again will refresh other databases and I suppose that also goes for linked servers. We're aware that this is causing some inconvenience and are looking into possible solutions.
Can you confirm if disabling and then re-enabling SQL Prompt would clear the cache, and this particular error?
As far as I know, restarting SSMS is the only way to clear the cache.
It appears to clear the cache for the connected server, but not for linked servers. i.e. I was creating a query in DB1 that referenced tables in DB2, I added a table to DB2 and SQL Prompt didn't see it. After a disable/enable cycle SQL Prompt saw it.
On the other hand it didn't make SQL Prompt refresh the cache from a linked server DB.
So it won't solve this problem.
What version of SQL Prompt are you using, is linked server support enabled in the options, and is the linked server a SQL Server or some other type of data source?
It works, it is just a pain that I have to cancel all running processes, restart SSMS, and then re-open everything and get it back to the same state if I need the cache for the linked server updated.