I have an issue when I try to add a synonym object to a Query Builder: "The object cannot be added to the Query Builder. UNKNOWN SYNONYM type objects cannot be modeled." our IT staff have tried with all account configuration levels from db owner on down. Always the same message/error.
What version of Oracle are you connecting to? And are you using oracle client?
I am connecting to a SQL Server dbase.
Hey Paul...
Check to see that your synonym is pointing to an object that contains/represents data, such as tables or views. TDP's Visual Query Builder won't accept synonymns that point to any other object type, and if you try to double-click the syn to force into the Query Builder, you'll get the message you posted. See snap below.
I will say (and Quest Dev take note) that it would be a VERY NICE enhancement request if the Query Builder DID accept synonyms to procedures that return a data result set.
Script seems fine: /****** Object: Synonym [SageSecured].[Item] Script Date: 10/10/2024 3:46:24 PM ******/
USE [Navigator];
GO
CREATE SYNONYM [SageSecured].[Item] FOR [WSAGESQL01D\WSAGESQL02D].[Estimates].[Secured].[Item];
GO
Wait, is the .[item] a table or a column?
-G-
Hey Gary, This synonym points to an entire table that happens to be named item. I know, not the greatest table name.
OK, thanks... just making sure the syn is pointing to a regular table and not something "special"...
No, just a set of table synonyms so we don't do cross instance joins.
I think this is due to the fact that this synonym points to a different SQL Server instance, and when creating the synonym you cannot specify the object type. This causes it to be of an "UNKNOWN" type and thus the TDP QueryModeler cannot model it. I confirmed this by creating my own new synonym to the origrinal object. I also tested this on a second database on the same SQL Server instance with no issues. This might be a limitation of TDP.
Ah, so akin to the DB link idea.
So, if you connect to the "other" SQL Server instance where your synonym is pointing, you should be able to drag Synonym1 from Database1 into your Query Builder, and then Synonym2 (or the table/view if you have access to it) from Database2 into the same Query Builder and join the two together. Would be considered a Cross-Connect Query, but is do-able, assuming you have the Professional Edition of TDP.
Just asking @pdrake if this resolved the issue?
Yes, I believe that Gary's suggestion will work. Where what I was trying to do does not seem to work in TDP. Cross-Connect Query is very unstable for me and very sensitive to ANY change I do to it. Constantly causes red x error bars in TDP that a newer version MAY fix, but hard to tell. I was still asking when we will get access to a newer version at FristEnergy.