Do not see the synonyms tables for SQL SERVER DB

User472204-OC

Hi Experts,

After I create the synonyms for different data source(DB Name) in SQL SERVER, and I can see the related synonyms tables in SQL SERVER, but do not see them when I import data by RPD for using the same user. Is it ok for Oracle, not SQL SERVER DB, What happen? The synonyms is not for SQL SERVER DB? Or what I missing some steps?

– Generates Synonyms:

use [ecp_ods]
select 'create synonym ' + name + ' for [ecp_ods].[dbo].[' + name + '];'
from sys.objects where type_desc='user_table' order by name;

– Then create synonyms:
use [ecp_lcl];
create synonym acpt_typ for [ecp_ods].[dbo].[acpt_typ];

[Deleted User]

Have you tried with differed ODBC drivers? Like the native SQL Server ones? In your screenshot you're visibly using different drivers for different connections...

User472204-OC

Hi Christian,

The name "SQL Server driver" is the old, and I download and install the latest SQL SERVER drivers named "ODBC Driver 11 for SQL Server" from Microsoft Web.Some tables are created in ECP_LCL DB and some tables are created in ECP_ODS DB. In order to improve the performance for joining different tables in different DB , so I create the synonyms tables with ECP_ODS, but when I import data from RPD, and I can not see the synonyms tables with ECP_ODS, only see the tables with ECP_LCL.

By the way, I can use the same user for see the synonyms tables with ECP_ODS in SQL SERVER Management Tools.

I create the different connection pool for testing.

3410125

While importing metadata in the OBIEE RPD, you might seen an option where of metadata type import selection is there....you need to check the 'synonym' so that synonym will be appearing the rpd as well.

User472204-OC

Yeah, I have checked this option for  'synonym' in RPD, but it does not be OK. You can click on mouse in my screenshot to view the detail steps.

[Deleted User]

User472204-OC wrote:Hi Christian,The name "SQL Server driver" is the old, and I download and install the latest SQL SERVER drivers named "ODBC Driver 11 for SQL Server" from Microsoft Web.Some tables are created in ECP_LCL DB and some tables are created in ECP_ODS DB. In order to improve the performance for joining different tables in different DB , so I create the synonyms tables with ECP_ODS, but when I import data from RPD, and I can not see the synonyms tables with ECP_ODS, only see the tables with ECP_LCL.By the way, I can use the same user for see the synonyms tables with ECP_ODS in SQL SERVER Management Tools.I create the different connection pool for testing.

That doesn't answer my question of "have you actually TRIED using different drivers and do the results vary depending on the drivers you use?"

User472204-OC

I only use the windows ODBC drivers, not native SQL Server drivers,Because I do not know how to use the native SQL Server drivers.

[Deleted User]

User472204-OC wrote:Because I do not know how to use the native SQL Server drivers.

Same as all other ODBC drivers: Download, install, go to your ODBC sources of your machine, create a new system dsn using the drivers you just installed (or were already installed). and point them to your target.

LMGTFY

User472204-OC

Yeah， the issue is also same, can you test it in this scenario for importing data from SQL Server?

3410125

You might have done it when you make excel as the data source in OBIEE. You need to go the Control panel ->Administrative tools->ODBC -> User DSN and Add the new DSN source.