Problem with report using DBCON multiconnect to MSSQL Server.

77 Views

Hi,

I have created this report following OSS note 738371 and it works fine:

REPORT z99bc_dbcon_test.

DATA: dbn(128).

PARAMETERS: pconn TYPE dbcon_name DEFAULT 'TEST-CONN'.

ptab TYPE string DEFAULT 'MYSCHEMA.MYTABLE'.

EXEC SQL.

CONNECT TO :pconn

ENDEXEC.

EXEC SQL.

SET CONNECTION :pconn

ENDEXEC.

EXEC SQL.

SELECT db_name() INTO :dbn FROM MYSCHEMA.MYTABLE

ENDEXEC.

EXEC SQL.

SET CONNECTION DEFAULT

ENDEXEC.

WRITE: / 'current database name', dbn.

DBCON entry has conn info very simple: 'MSSQL_SERVER=<IP Address> MSSQL_DBNAME=TEST'

This program connects to database TEST, and reads table MYTABLE in schema MYSCHEMA.

I would know if it is possible to select the table name in a dynamic way:

SELECT db_name() INTO :dbn FROM :ptab

But this line fails with meesages:

dbdsmss: DBSL99 SQL1087

Must declare the table variable "@P1".

***LOG BY2=> sql error 1087 performing OPC [dbds#2 @ 486] [dbds 0486 ]

***LOG BY0=> Must declare the table variable "@P1". [dbds#2 @ 486] [dbds 0486 ]

Any idea ? It is possible at all ?

Regards,

Joan B. Altadill