COGONS - sqlPrepareWithOptions' status='-56

Question

I have the following code:

;WITH CTE1 AS
(
    SELECT
        ROW_NUMBER() OVER (ORDER BY EVT_CODE) AS rn,
        E.EVT_ORG AS Org,
        E.EVT_CODE AS OT, 
        E.EVT_OBJECT AS Equipo, O.OBJ_POSITION AS Posicion, 
        E.EVT_JOBTYPE AS Tipo, 
        E.EVT_DESC AS Descripcion,
        E.EVT_WORKADDRESS AS Comentarios, 
        E.EVT_REQM AS Error, B.BOO_PERSON AS Trabajador, 
        B.BOO_ENTERED AS Fecha, B.BOO_HOURS AS Horass
    FROM
        dbo.r5events AS E 
    INNER JOIN
        dbo.r5bookedhours AS B ON E.EVT_CODE = B.BOO_EVENT 
    INNER JOIN
        dbo.r5objects AS O ON O.OBJ_CODE = E.EVT_OBJECT
    WHERE   
        E.EVT_JOBTYPE IN ('PM', 'CM', 'PMM') and
        E.EVT_ORG = #PROMPT('Organización')# and
        B.BOO_ENTERED between #PROMPT('Fecha_Inicio')# and #PROMPT('Fecha_Final')# and 
        (E.EVT_REQM = #PROMPT('Error')# OR #PROMPT('Error')# = '%') and
        (E.EVT_OBJECT = #PROMPT('Equipo')# OR #PROMPT('Equipo')# = '%') and
        (O.OBJ_POSITION = #PROMPT('Posicion')# OR #PROMPT('Posicion')# = '%')
)
,CTE2 AS
(
    SELECT
        ROW_NUMBER() OVER (ORDER BY BOO_event) AS rn,
        BOO_event 'Evento de Trabajo',
        STUFF((SELECT ', ' + CAST(BOO_person AS VARCHAR(100)) [text()]    
               FROM r5bookedhours 
               WHERE BOO_event = t.BOO_event
               FOR XML PATH(''), TYPE).value('.','NVARCHAR(4000)'),1,1,' ') Empleados, 
        STUFF((SELECT ', ' + CAST(BOO_hours AS VARCHAR(100)) [text()]       
               FROM r5bookedhours 
               WHERE BOO_event = t.BOO_event
               FOR XML PATH(''), TYPE).value('.','NVARCHAR(4000)'), 1, 1, ' ') Horas
    FROM r5bookedhours t
    GROUP BY BOO_event
)
SELECT Org, Empleados, Horas from CTE1 FULL JOIN CTE2 ON CTE1.rn = CTE2.rn

And it's working fine on SQL Server 2008, but when I try to execute it on COGNOS Report Studio, I'm getting this error:

'sqlPrepareWithOptions' status='-56

UDA-SQL-0115 Solicitud SQL no válida.UDA-SQL-0564 [Microsoft OLE DB Provider for SQL Server]Deferred prepare could not be completed.UDA-SQL-0564 [Microsoft OLE DB Provider for SQL Server]Statement(s) could not be prepared. (SQLSTATE=42000, SQLERRORCODE=8180)UDA-SQL-0564 [Microsoft OLE DB Provider for SQL Server]Incorrect syntax near ')'. (SQLSTATE=42000, SQLERRORCODE=102)UDA-SQL-0564 [Microsoft OLE DB Provider for SQL Server]Incorrect syntax near ','. (SQLSTATE=42000, SQLERRORCODE=102)UDA-SQL-0564 [Microsoft OLE DB Provider for SQL Server]Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon. (SQLSTATE=42000, SQLERRORCODE=319)UDA-SQL-0564 [Microsoft OLE DB Provider for SQL Server]Incorrect syntax near the keyword 'WITH'. (SQLSTATE=42000, SQLERRORCODE=156)RSV-SRV-0042 Rastreo:RSReportService.cpp(722): QFException: CCL_CAUGHT: RSReportService::process()RSReportServiceMethod.cpp(263): QFException: CCL_RETHROW: RSReportServiceMethod::process(): promptPagingForward_RequestRSASyncExecutionThread.cpp(808): QFException: RSASyncExecutionThread::checkExceptionRSASyncExecutionThread.cpp(260): QFException: CCL_CAUGHT: RSASyncExecutionThread::runImpl(): promptPagingForward_RequestRSASyncExecutionThread.cpp(864): QFException: CCL_RETHROW: RSASyncExecutionThread::processCommand(): promptPagingForward_RequestExecution/RSRenderExecution.cpp(670): QFException: CCL_RETHROW: RSRenderExecution::executeAssembly/RSDocAssemblyDispatch.cpp(291): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSLayoutAssembly.cpp(79): QFException: CCL_RETHROW: RSLayoutAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(358): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchChildrenAssemblyForwardAssembly/RSReportPagesAssembly.cpp(179): QFException: CCL_RETHROW: RSReportPagesAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(308): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSPageAssembly.cpp(303): QFException: CCL_RETHROW: RSPageAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(308): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSTableRowAssembly.cpp(177): QFException: CCL_RETHROW: RSTableRowAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(308): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSTableCellAssembly.cpp(137): QFException: CCL_RETHROW: RSTableCellAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(358): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchChildrenAssemblyForwardAssembly/RSDocAssemblyDispatch.cpp(308): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSAssembly.cpp(662): QFException: CCL_RETHROW: RSAssembly::createListIteratorAssembly/RSAssembly.cpp(717): QFException: CCL_RETHROW: RSAssembly::createListIteratorRSQueryMgr.cpp(1055): QFException: CCL_RETHROW: RSQueryMgr::getListIteratorRSQueryMgr.cpp(1131): QFException: CCL_RETHROW: RSQueryMgr::getResultSetIteratorRSQueryMgr.cpp(1295): QFException: CCL_RETHROW: RSQueryMgr::createIteratorRSQueryMgr.cpp(1569): QFException: CCL_RETHROW: RSQueryMgr::executeRsapiCommandRSQueryMgr.cpp(1559): QFException: CCL_RETHROW: RSQueryMgr::executeRsapiCommandRSQueryMgrExecutionHandlerImpl.cpp(168): QFException: CCL_RETHROW: RSQueryMgrExecutionHandlerImpl::execute()RSQueryMgrExecutionHandlerImpl.cpp(160): QFException: CCL_RETHROW: RSQueryMgrExecutionHandlerImpl::execute()QFSSession.cpp(1147): QFException: CCL_RETHROW: QFSSession::ProcessDoRequest()QFSSession.cpp(1145): QFException: CCL_CAUGHT: QFSSession::ProcessDoRequest()QFSSession.cpp(1102): QFException: CCL_RETHROW: QFSSession::ProcessDoRequest()QFSSession.cpp(1078): QFException: CCL_RETHROW: QFSSession::ProcessDoRequest()QFSConnection.cpp(788): QFException: CCL_RETHROW: QFSConnection::ExecuteQFSQuery.cpp(213): QFException: CCL_RETHROW: QFSQuery::Execute v2CoordinationQFSQuery.cpp(4456): QFException: CCL_THROW: CoordinationPlanner

Already tried to simplify the select with only 3 columns, and to simplify the querys with less camps to be selected, but the error keeps showing.

Second Error:

UDA-SQL-0115 Solicitud SQL no válida.UDA-SQL-0564 [Microsoft OLE DB Provider for SQL Server]Deferred prepare could not be completed.UDA-SQL-0564 [Microsoft OLE DB Provider for SQL Server]Statement(s) could not be prepared. (SQLSTATE=42000, SQLERRORCODE=8180)UDA-SQL-0564 [Microsoft OLE DB Provider for SQL Server]Incorrect syntax near ')'. (SQLSTATE=42000, SQLERRORCODE=102)UDA-SQL-0564 [Microsoft OLE DB Provider for SQL Server]Incorrect syntax near ';'. (SQLSTATE=42000, SQLERRORCODE=102)RSV-SRV-0042 Rastreo:RSReportService.cpp(722): QFException: CCL_CAUGHT: RSReportService::process()RSReportServiceMethod.cpp(263): QFException: CCL_RETHROW: RSReportServiceMethod::process(): promptPagingForward_RequestRSASyncExecutionThread.cpp(808): QFException: RSASyncExecutionThread::checkExceptionRSASyncExecutionThread.cpp(260): QFException: CCL_CAUGHT: RSASyncExecutionThread::runImpl(): promptPagingForward_RequestRSASyncExecutionThread.cpp(864): QFException: CCL_RETHROW: RSASyncExecutionThread::processCommand(): promptPagingForward_RequestExecution/RSRenderExecution.cpp(670): QFException: CCL_RETHROW: RSRenderExecution::executeAssembly/RSDocAssemblyDispatch.cpp(291): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSLayoutAssembly.cpp(79): QFException: CCL_RETHROW: RSLayoutAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(358): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchChildrenAssemblyForwardAssembly/RSReportPagesAssembly.cpp(179): QFException: CCL_RETHROW: RSReportPagesAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(308): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSPageAssembly.cpp(303): QFException: CCL_RETHROW: RSPageAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(308): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSTableRowAssembly.cpp(177): QFException: CCL_RETHROW: RSTableRowAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(308): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSTableCellAssembly.cpp(137): QFException: CCL_RETHROW: RSTableCellAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(358): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchChildrenAssemblyForwardAssembly/RSDocAssemblyDispatch.cpp(308): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSAssembly.cpp(662): QFException: CCL_RETHROW: RSAssembly::createListIteratorAssembly/RSAssembly.cpp(717): QFException: CCL_RETHROW: RSAssembly::createListIteratorRSQueryMgr.cpp(1055): QFException: CCL_RETHROW: RSQueryMgr::getListIteratorRSQueryMgr.cpp(1131): QFException: CCL_RETHROW: RSQueryMgr::getResultSetIteratorRSQueryMgr.cpp(1295): QFException: CCL_RETHROW: RSQueryMgr::createIteratorRSQueryMgr.cpp(1569): QFException: CCL_RETHROW: RSQueryMgr::executeRsapiCommandRSQueryMgr.cpp(1559): QFException: CCL_RETHROW: RSQueryMgr::executeRsapiCommandRSQueryMgrExecutionHandlerImpl.cpp(168): QFException: CCL_RETHROW: RSQueryMgrExecutionHandlerImpl::execute()RSQueryMgrExecutionHandlerImpl.cpp(160): QFException: CCL_RETHROW: RSQueryMgrExecutionHandlerImpl::execute()QFSSession.cpp(1147): QFException: CCL_RETHROW: QFSSession::ProcessDoRequest()QFSSession.cpp(1145): QFException: CCL_CAUGHT: QFSSession::ProcessDoRequest()QFSSession.cpp(1102): QFException: CCL_RETHROW: QFSSession::ProcessDoRequest()QFSSession.cpp(1078): QFException: CCL_RETHROW: QFSSession::ProcessDoRequest()QFSConnection.cpp(788): QFException: CCL_RETHROW: QFSConnection::ExecuteQFSQuery.cpp(213): QFException: CCL_RETHROW: QFSQuery::Execute v2CoordinationQFSQuery.cpp(4456): QFException: CCL_THROW: CoordinationPlanner

Answer 1

Tired of everything, created 2 views and was the end of my problems... way easier. Thanks all anyway.