BEGIN_SQL parameter usage from BO to MS SQL Server 2008

[Former Member]

Hi,

We are trying to use the BEGIN_SQL and END_SQL universe parameters. When a report is created in WEBI, I see that the END_SQL value is appended to the query but the BEGIN_SQL value is ignored. I tried using comments initially but removed them thinking SQL server is probably ignoring the comments before the query.

I forced the comments in a custom SQL and I can see the comments in the database. We are using SQL Server 2008 and I tried using the parameters with both ODBC and OLEDB connections. Is the BEGIN_SQL value ignored due to some server settings?

Thanks!

[former_member193452 · Accepted solution]

Sql Server does not support the BEGIN_SQL parameter.

HERES INFO From the Universe Designer Guide for XIR3.1 sp3

The SQL prefix function inserts SQL orders before a

BusinessObjects-generated SQL statement by using the universe parameter

Begin_SQL. This ensures that the orders are executed before all generated

SQL statements. This function works with any database that supports passing

parameters before the select statement. The following are some examples:

• Teradata: using ‘QUERY_BAND’ for a transaction (refer to the Teradata

documentation)

• Oracle: turn on read only transaction

• Netezza: trigger optimization options

[former_member193452]

It may be the version of the product.  BEGIN_SQL should be transparent to the user.  They should not be able to view the sql.  If it is displayed, that would be perhaps an earlier product version.

[former_member193452]

The BEGIN_SQL parameter was designed for a particular use.  It encapsulates a presql statement before a query is run. So it runs as a seperate query before any webi query is run.

The value will not be visible from the Webi SQL view.

This feature was not designed with sql server in mind.  the query runs before the actual webi query.

Here is what it is designed for:

per the universe designer guide XIR3.1 sp3 page  650

Teradata: using ‘QUERY_BAND’ for a transaction (refer to the Teradata

Oracle for Set read only

Netezza for query optimization.

I think you are best to use the End SQL parameter.

I hope this helps!

[Former Member]

Hi Jacqueline,

Do you have any source which says that BEGIN_SQL values should not be displayed in the query?

I just tested with my SP5 builds and I can see "value" for BEGIN_SQL.

Thanks,

Vivek

[Former Member]

Hi Vivek,

Was the SP5 for Business Objects or SQL Server 2008?

Thanks!

[Former Member]

Hi,

I am talking about SP5 of BOBJ.

My test was with MS-Access (Island Resort Marketing - DB) and yes I can see the "Value" assigned in the "Universe Parameter" for "BEGIN_SQL" parameter.

I am currently installing MS-SQLServer2008 and will confirm the result.

Can you please check with MS_Access DB at your end?

Thanks,

Vivek

[Former Member]

Hi Vivek,

Unfortunately, we do not have SP5.. we are still on SP3 and there are no plans for our infrastructure team to move on to SP5. Please let me know if you are successful in testing on SQL Server 2008.

Thanks!

[Former Member]

Hi,

Can you please provide below information: -

1) BO Version and Patch Level

2) If BI4.0, then is it UNV or UNX

Thanks,

Vivek

[Former Member]

Hi Vivek,

We are using BO 3.1 with SP 3/FP 3.1.

The BEGIN_SQL portion is not working either with an ODBC connection or an OLEDB connection.

Thanks!