Once connection to linked server fails, SQL Prompt gives up

MikeyCMikeyC Posts: 249 Bronze 3
edited April 21, 2010 11:44AM in SQL Prompt Previous Versions
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.

Comments

  • Brian DonahueBrian Donahue Posts: 6,590 New member
    Hello Michael,

    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.
  • MikeyCMikeyC Posts: 249 Bronze 3
    Brain,

    Can you confirm if disabling and then re-enabling SQL Prompt would clear the cache, and this particular error?
  • Brian DonahueBrian Donahue Posts: 6,590 New member
    Hello Michael,

    As far as I know, restarting SSMS is the only way to clear the cache.
  • MikeyCMikeyC Posts: 249 Bronze 3
    I just confirmed that disabling SQL Prompt and re-enabling it causes it to refresh a portion of 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.
  • Brian DonahueBrian Donahue Posts: 6,590 New member
    Hi Michael,

    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?
  • MikeyCMikeyC Posts: 249 Bronze 3
    I am using SQL Prompt 4.0.4.14, yes linked server support is enabled, and the linked server in question is SQL Server.

    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.
Sign In or Register to comment.