SQL Prompt Not Showing Accurate View Code-Behind

ZachBerr

Greetings,

I have a view running on Microsoft SQL Server 2017. Whenever I or others in the office use SQL Prompt to view the code-behind, it shows inaccurate code-- it lists the table name as "etl.RELATIONSHIPS_TABLE_V" rather than "etl.RELATION_TABLE_V".

I've tried restarting SSMS.
I've tried upgrading SQL Prompt to the newest version.
Others in the office are able to replicate the problem.
EDIT: I've also tried refreshing Intellisense and the SQL Prompt Cache; I forgot to mention that in the original post.

The incorrect code, when I view the code-behind through SQL Prompt, showing the object to be created as "RELATIONSHIPS" rather than "RELATION":


The correct code, when I view the code-behind through Object Explorer:



Thanks for your help!

Jon_Kirkwood

Thank you for posting on the Redgate forums.
I am sorry that you are having trouble with SQL Prompt.

With the information provided, I have not been able to reproduce this issue. 
Would it be possible for you to share a snapshot of your database to help troubleshoot further

This link provides more information on creating the database snapshot 

Note - this snapshot does not contain any table data. It contains information about the database structure.

https://documentation.red-gate.com/sc/working-with-other-data-sources/working-with-snapshots

Can you also please enable Verbose Logging and provide a copy of the log files so we can see if there are any insights there on what is occurring

https://documentation.red-gate.com/sc/getting-more-from-sql-compare/logging-and-log-files

If you do not wish to share the DB snapshot through the forum you can upload files directly to our support team through the link provided below.

https://files.red-gate.com/requests/OZCMdThMTRnFyP4rB1h4Ac

Thank you in advance for the snapshot and log files and moving forward to a good resolution on this issue.

ZachBerr

Thank you, @Jon_Kirkwood. I'd be glad to, but I don't believe that we're currently licensed for SQL Compare; we have SQL Prompt and SQL Search. Do I need to sign up for the 14-day free trial? 

Jon_Kirkwood

If you are happy to do the 14-day trial that should be fine. It would be plenty of time to solve this inquiry.

ZachBerr

Thank you! I sent the snapshot through the link provided. Appreciate your assistance.

ZachBerr

Hi there, 

It's been a week, so I wanted to check back in; let me know if you need more from me.

Thanks!

Jon_Kirkwood

Thank you for the snapshot file and patience whilst we investigate. 

I've tried deploying your snapshot in a variety of ways to try and best recreate this scenario and in no instance was I able to get the RELATIONSHIPS text to appear. It was always the expected RELATIONS view

Are you aware if that view (etl.RELATIONSHIPS_TABLE_V) exists within your infrastructure at all perhaps in another DB?

I am wondering if perhaps it is making a join with another server and bringing back a bad hit.

In your SQL Prompt settings, can you check if Load Suggestions for Linked Servers & Synonyms are enabled and test if disabling them changes this code-behind being displayed incorrectly?

​

ZachBerr

Thanks, Jon! I'll do a numbered list below to help keep my response organized.

1. I checked the other DBs on the instance, and none of them appear to have that object.
2. I unchecked the values in Suggestions > Connections and restarted SSMS (to be safe). Same thing happens-- still getting the incorrect prompt.
   
3. I had another SQL Prompt update to install, but that didn't do the trick. Part of the installation was restarting SSMS.
4. I tried refreshing suggestions again.
5. Our SQL Instance was restarted Thursday of last week and it didn't resolve the issue (in case that's relevant)
6. Querying sys.views and sys.all_objects for relationships_table_v returns no hits.

It's possible that, when the view was originally created, the developer incorrectly used etl.relationships_table_v , and then dropped/recreated the view using the correct nomenclature etl.relation_table_v. I'm not sure.
 
Other ideas / next steps?