CR12 - connect to multi-DB in the same SQL instance

[Vamps]

Hello:

My first post.

I'm new to CR, and marginal at best with DB's. I'm attempting to pull data from multiple (~20 or so) different DB's within the same instance of SQL. We have a second instance with historical data, but for now, I don't need to pull anything from the second instance.

One issue is the databases are identical (except for name ofcourse). They have the same table structure. So I could be looking to pull data from a table with the same name, but in a different DB.

I'm using Crystal Reports 12, pulling from a SQL 2000 DB. I'm using an OLE DB connection, I've tried an ODBC connection, but it doesn't seem to be returning a complete list of my tables, they stop at tables starting with "P", and most of the tables I need are further along alphbetically. ODBC seems to be the way to go other than that. I can create MULTIPLE ODBC's on my workstation, and then use one for each DB (probably only need to do about 3 or 4 connections this time).

again, I'm not super fimilar with CR, so go easy on me.

Thanks in adv.,

Vamps

 

[Vamps]

One more piece of info.

My ODBC lists the partial list of tables under "dbo". They also ALL have a prefix added to them 'zDP_'. There is not another level under dbo called "tables" like when I create an OLE DB connection, where it lists the three of five options that I have it set to show (which I believe is the default setting).
It shows: tables, views, and stored procedures.
It doesn't show: System Tables or Synonyms.

My settings are the same for the ODBC as they are for the OLE DB.

 

[Vamps]

Well, I see nobody’s going to put up any thoughts here. NICE

I finally resolved this last week, and thought I'd stop by and put up what I found out.

Seems CR can pull data from multiple locations, just not in my case. Because I have multiple DB's within the same instance that ALL have an identical setup (table names).

The SOLUTION:

To create a "view" in SQL that includes all the DB's and Tables I need, then to use the view as a data source. Also, I never dealt with the ODBC issue, I just used an OLE connection, worked fine.

hopefully this will help someone in a similar situation.