SQL Server 2005 Synonyms not supported in 3.5?

DavidHerbst

I just upgraded to 3.5 but my table Synonyms are not showing up in the autocomplete candidate lists after the FROM or JOIN keywords are typed.

In addition, typing table Synonyms does not bring up an autocomplete candidate list of column names.

BTW these Synonyms are pointing to tables located in another database on the same server.

Is there some setting I'm missing which enables Synonyms support?

Are SQL Server 2005 Synonyms supported by SQL Prompt? If not, when will this support be added?

JasonC

I'm afraid synonyms are not supported in SQL Prompt 3. Synonyms, like you're using them, are usually used for objects on different DBs or Servers. A such, supporting them would create many issues of authentication and security. But we would like to support them in future versions of SQL Prompt, but we can't say whether we will support this in the next version.

Thanks for your input, we always like to know what people are using SQL Prompt for.

DavidHerbst

Thanks for your response Jason and I understand that you there may be difficulties that prevent this from being implemented in the near future.

What I don't understand is how the authentication and security issues would be different than the ones you've already solved for the "Cross database queries" support you added in the 3.5 release.

Synonyms are just aliases, authentication and security is still based on the underlying object they are pointing at. See code example below.

Am I missing something here?

E.g.

USE OtherDb

CREATE SYNONYM dbo.CreditCard FOR AdventureWorks.Sales.CreditCard

-- Either both of the following queries will fail or both of them will succeed based on authentication and security settings.

SELECT *
FROM CreditCard

SELECT *
FROM AdventureWorks.Sales.CreditCard

JasonC

SQL Prompt would need to parse and use synonyms that could potentially be much higher up in the code, and prompt the user for authentication details accordingly. You are correct that the abilities to do these tasks is largely already in Prompt, but wiring it all together in a usable way will probably have to wait for the next major release.

In your code example, I see what you're saying. But say the user opened this query from a file. He would need to immediately be prompted for authentication details for Adventureworks before Prompt could give meaningful intellisense. The problem we need to solve is that when opening a large file with lots of synonyms, the user would be immediately prompted with dozens of dialogs asking for passwords. So in some ways it's more of a usability issue than an engineering one.

Thanks again for your suggestions - this is precisely the sort of feedback we need to help build the next version!

sirbasku

I love intellisense! We use synonyms for easy configuration between stage and production database linking and we sure would vote for synonym support in intellisense! Almost all our new BI development systems use synonyms so source systems are transparent to the developers and testers. All the procs now just reference a synonym and it is the DBA's job to make sure the synonyms are pointing at the proper system (develop/stage/production). It has really helped our small team stay agile, but now all our cool new BI projects are making me code with an explicit reference if I want the intellisense then I have to go back and change the explicit objects with the synonym names. It would be great if synonyms were just treated like the other database objects and security just worked the same as it does for accessing all the stuff it caches now.