Changing universe to access Production VS Test SQL Server

building semantic layer
1

Greetings ‘listers’…

I am in the final stages of developing a system which will go into production use at the turn of the year. My universes are in their final versions and are fully tested. I have had users developing sales reports using a test database for several months now. My question is… how can I change my universe to make it access the Production SQL Server Tables? I have tried changing the database connection defined in the universe parameters… but that doesn’t seem to effect any change. My queries are still pulling data from the tables in the test database. I have been through several rounds with the BusObj support technicians. Their focus has remained on getting my ‘database connection’ updated everwhere that it needs to be updated (in the repository… and subsequently in the users local universe directory). However… my SQL Server database administrator says that it doesn’t matter what the connection name is… that this is just a ‘default’ database name that is used if the database name is not already specified in the SQL… and in the beginning, when I first generated my universe, I let Business Objects do all the automatic stuff for me that it would… and what I ended up with is… for every object, the select statement (definition tab) includes the database name as well as the table name and field name. For example, I have an object named Item Number and it’s select statement is:

Test_Access.dbo.DWD00ITEM.Item_Number

Test_Access.dbo is the database name
DWD00ITEM is the table name
Item_Number is the field name

So now… no matter what I do with the ‘database connection’ in my universe
parameters… my queries are generating SQL that reference tables in the Test_Access.dbo database. How do I make it point at the tables in the Prod_Access.dbo database? I’m hoping that someone knows of a better way to do this than MANUALLY editing the select statements for ALL my objects and ALL my join expressions… and what about the table names? Is it even possible to edit the table names in the structure window? Will it work if I leave them alone? When I ‘refresh structure’ is it going to refresh based on the test database, or the production database? So many questions !!! Please let
me know if you can help!

I am working with BusObj version 4.1.3, SQL Server version 6.5, Windows NT4.0 workstation, on a Compact Proliant server running Windows NT4.0.

THANKS !
Ione Myers
Brunswick Indoor Recreation Group
business@brunbowl.com or Ione_Myers@brunbowl.com

Listserv Archives (BOB member since 2002-06-25)

2

Database

Ione,

I don’t know if this will help or not [I’m not familiar with SQL Server], but try the Rename Table option in the universe, and see if you can strip off the database name.

Steven Jones
Consultant to BT Office Products International

Listserv Archives (BOB member since 2002-06-25)

3

Database

Ione,

Changing the connection string in your universe won’t fix your problem (and your DBA’s right). When your DBA sets up a login with MS SQLSERVER, he can specify either the default database to log in to (in your case, Test_Access) or specify nothing which may log you to something like the Pubs database by default.

Assuming that the tables you built your universe upon (in Test_Access) are also in Prod_Access, just ask your DBA to change the setting of the login that you use for the default database to Prod_Access, log back to Designer, refresh your universe and that should do it.

So now… no matter what I do with the ‘database connection’ in my universe
parameters… my queries are generating SQL that reference tables in the Test_Access.dbo database. How do I make it point at the tables in the Prod_Access.dbo database?

Listserv Archives (BOB member since 2002-06-25)