Synonyms - Suggestions are not working

itsmikeyd

Heya all,

I've got a very strange scenario on my hands. I have two servers, SERVER1 and SERVER2. They both run SQL Server 15.0.2095.3. I am running SQL Prompt 10.13.4.32349 in SSMS 18.12.1.

SERVER1 hosts the database SourceDatabase, and DestinationDatabaseDev. SERVER2 hosts DestinationDatabaseTest. 

Both Dev and Test databases reference SERVER1.SourceDatabase using synonyms. SERVER2 uses a linked server to address SERVER1.SourceDatabase. 

When connected to SERVER1 in DestinationDatabaseDev, the suggestions for any column in SourceDatabase do not work at all. All synonyms are underlined red, and aliasing does not work at all. When connected to SERVER2, in DestinationDatabaseTest, everything is fine. 

Both servers are hosted on the same Windows server (SERVER1 sharing the hostname, SERVER2 being hostname\test). I am connecting remotely from home via VPN. 

I've tried turning suggestions on and off, restarting SSMS, restarting the PC but I always end up in the same place. 

Any ideas / suggestions? Is there some sort of corrupted cache that I need to clean down somewhere? I'd rather not continue to develop on SERVER2 as linked servers are crappy for performance. 

Thanks!

Kurt_M

Hi @itsmikeyd

Would you be able to check the SQL Prompt > Options > Connections tab to see if you are using the 'Specify the databases you want to load suggestion for' option? Also try enabling the check boxes at the top as well, should they be disabled.

itsmikeyd

Thanks for the suggestion @Kurt_M, but I have those three items selected, and am not using the option to "Only load suggestions for certain databases/schemas".

Kurt_M

Hi @itsmikeyd

Thanks for checking that.

Could I ask you to do a test where you connect to Server 1 over remote desktop and attempt to load the suggestions directly on that server, as opposed to using the VPN and connecting through SSMS? 

Apart from modifying those settings I mentioned, it should show the suggestions. I'm thinking it might be something to do with the VPN, but I can't be certain. 

If you can try remoting in that one and locally running SSMS on that server, that would be an ideal test. 

Let me know how it goes.

Harman

Hi

I have the same issue. That is, the suggestions for the Synonyms is not working.
I am using a simple setup with 2 Databases on 1 Server.

When using a SELECT statement from the Synonym table it shows the following on hovering.

But upon execution works perfectly fine to display the records.

I checked the settings as you mentioned above.


Please help in resolving this issue.
Thank you for your support in advance.

Harman

itsmikeyd

Harman said:

Hi

I have the same issue. That is, the suggestions for the Synonyms is not working.
I am using a simple setup with 2 Databases on 1 Server.

When using a SELECT statement from the Synonym table it shows the following on hovering.

But upon execution works perfectly fine to display the records.

I checked the settings as you mentioned above.


Please help in resolving this issue.
Thank you for your support in advance.

Harman

Hey Harman,

The problem was how I connected to the SQL instance in SSMS and how the synonyms were configured.

If you have synonyms pointing to DATABASE.SCHEMA.TABLE, but you connect to your SQL instance using DATABASE.DOMAIN_NAME, this issue occurs.

You must connect to your SQL instance using the same name that your synonyms reference. 

Hope this helps. @Kurt_M, can this be handled in SQL Prompt at all? 

Kurt_M

Hi @itsmikeyd

There's nothing specifically within SQL Prompt that can handle this I'm afraid. It's all determined by how you connect into your instances. Just connecting normally, or how you've mentioned, will have SQL Prompt working perfectly fine. 

The process of adding VPN's or connecting to SQL via other methods like what you've described in your issue, can cause these problems.