Free-hand SQL Webi Promotion to other server, Connection not working

[DerekJ]

I am using the Free-hand SQL connector and have configred the exact same connection in my Test and Prod BOBJ Servers. However when I promote the Webi report via CMC from Test to Prod, the connection errors saying "You do not have the right to access data returned by this universe connection.".

If i create the connection manually in Prod system, it works OK, but not from promotion, as if some detail is different so it cannot pass the connection from one server to the other.

The problem with free-hand SQL is there is no option to 'Change Source' so you have to manually change it then remap all report fields one by one. This adds risk report may not be accurately replicated in Production and is time intensive.

So, my questions are:

1) Has anyone managed to promote Free-hand SQL with no issue, if so it must be some config incorrect on the connection or security in Prod.

2) If this is a problem, is there any way around this to avoid having to rebuild the report?

My thanks in advance for any advice.

[nachtaktiv · Accepted solution]

add a second/other query within your WebI-report with the correct SQL-source/syntax,
then you can replace/change the source without rebuilding the report.

[DerekJ · Accepted solution]

Hi, thanks, yes I tried that, but thought it had not worked as the query was the same query number so I thought it had not changed. But on closer inspection, the source has changed, so both queries are now same source. I was assuming it would have swapped queries in from end. Thanks