SQL Server data source with multiple connections keeps using same database

[Invisi]

My client runs Cognos 10 with SQL Server 2008. Since we are migrating to another database for the data warehouse query layer, I make an extra connection in the test environment to the new database. When I create a test report, run it and choose the newly made connection, the (simple) report is slow, so I check the SQL. It turns out it generates a query that still explicitely refers to the database of the other connection. What am I doing wrong?

[MFGF]

My client runs Cognos 10 with SQL Server 2008. Since we are migrating to another database for the data warehouse query layer, I make an extra connection in the test environment to the new database. When I create a test report, run it and choose the newly made connection, the (simple) report is slow, so I check the SQL. It turns out it generates a query that still explicitely refers to the database of the other connection. What am I doing wrong?

If you look at the data source definition in the underlying Framework Manager model, do you have the original database defined in either the Catalog or Schema properties?

MF.

[Invisi]

In the framework, I go to the Data Source and I see the database name in the attribute 'Catalog'... Is this the source of all evil?  :'(

[Invisi]

Oh, and that wretched 'dbo' in Schema...  :-\

[MFGF]

In the framework, I go to the Data Source and I see the database name in the attribute 'Catalog'... Is this the source of all evil?  :'(

Actually I think the root of all evil is the love of money, but I digress :)

Try removing the Catalog entry and re-publishing your package (or a new package to test). Does that fix things?

MF.

[Invisi]

Are you mocking my new love?  >:(

I will try and see what happens. Also if it doesn't mess up the reports... Probably not.

[Invisi]

It works beautifully! Now the test report rushes over my gorgeously designed fresh version of the datawarehouse with ease. Thanks!

[MFGF]

Are you mocking my new love?  >:(

Would I do such a thing? ;)

I will try and see what happens. Also if it doesn't mess up the reports... Probably not.

Be brave! Be confident! Cross your fingers in secret and go for it! :)

It works beautifully! Now the test report rushes over my gorgeously designed fresh version of the datawarehouse with ease. Thanks!

Yay! A satisfying end to the week, I hope!

MF.

[Invisi]

Very satisfying... Until I come to work this morning to see that our weekly schedule on this environment failed... Reason:  The connection is ambiguous.

How do I tell a schedule which connection to use? I fail to see it in the settings so far. Is this something to set in the settings of the schedule, or of the report itself?

[Invisi]

Anybody about my schedule issue?

[Invisi]

Never mind, I find it. It's a prompt value...  :-\ >:(