multiple dababases 2

[susheeltips]

i want to create a catalog which gets data from oracle and sqlserver. what to do ?

thanks in advance

 

[gasman009]

Sorry, your best bet is to bring in the data you want as Hotfiles (preferably from the smaller sets of data).

As you probably already know, one DB per catalog.

I don't think there is another way. I remember sometime ago, new middleware products were coming out that would connect multiple DB's through one interface, i.e. ODBC. (If I recall, datajoiner(?) was one of these products?)

Sorry this wasn't more helpful.

 

[griffindm]

Actually I've heard of SQL Server 2000 functionality that lets you create a table alias to other platforms. If so you could do it in one catalog without hotfiles. You may want to do a post at the SQL Server forum to see if anyone there has more specifics. If they say how to do it, please pass it along to everyone here <g>.

HTH,

Dave Griffin

 

[griffindm]

guxeby,

I did a little research on the SQL Server forum (#183). The process is called creating a linked server. MS docs for SQL Server should explain a little more, or you can try to search the forums, faqs, or do a posting there. Another MS alternative is to use DTS (data transformation services) to create a copy - static or scheduled - of the Oracle data in SQL Server.

Good luck.

Dave Griffin

 

[goldsmt]

You can us MS Access to create link tables to your 2 databases and then map Imptomptu over the access database.

topic covered in a recent Support link

Regards

tony goldsmith

mailto:tony.goldsmith@frontline-consultancy.co.uk

 

[griffindm]

Thanks Tony,

I thought this was possible, but I know that Access requires login passwords for foreign databases at connect time, and I was unsure if you could automate that.

Dave Griffin

 

[gasman009]

Thanks Tony and Dave.
I tried (quickly) using Access to link to SQL Server, but the only objects that showed up (I'm using Impromptu version 6.1) were just the Access tables - no linked tables. Are you guys using Impromptu also or did I do something wrong?

Gaston

 

[griffindm]

Gaston,

Here is the Cognos solution:

Problem Description

You have a main access database and links to tables in another access database. On the Catalog menu, you select Tables but the linked tables are not visible.

Solution Description

To resolve this issue, select Options on the Tools menu. On the General tab disable the check box for
'retrieve database tables when editing catalog tables.' Now on the Catalog menu, when you select Tables press the Retrieve Tables button. A prompt will appear;d make sure you retrieve views and synonyms. The tables will now be visible.

My Note: I haven't tried this yet, and the solution only refers to links to ANOTHER ACCESS database, so it may not work on links to other database types. It is worth a try though. Let me know if it works for you.

HTH,

Dave Griffin

 

[gasman009]

Good info. I just tested it out quickly and it seems like it would work. Thanks for the help!

-Gaston

 

[gizuffi]

I tried this solution: I linked an SQL database to an Access database because I needed both the Access local tables and the SQL tables. The link between Access and SQL is ODBC. Then I connected my catalog using ODBC on Access Database. It works with local Access tables but I get error with SQL tables. My opinion is that making a double ODBC is not possible: Catalog ---ODBC---- Access DB ----ODBC---- SQL DB

I'm new to Cognos so don't take this for sure

Gianluca

 

[gasman009]

What is the complete error message and number?

 

[vivica]

Why don't you try to create a data mart on either Oracle or Sql. And import the data using DTS package ( you can also schedule the DTS package to feed data on regular base) from multiple DB into one, so that you have a centrilized data source for your Cognos Catalog.