>I have a stored procedure with a union in it that is returning an error:
>Microsoft OLE DB Provider for ODBC Drivers error '80004005'
>[SAP AG][SQLOD32 DLL][SAP DB]General error;-4024.
>However, SQL runs fine in SQL Studio. I'm running 7.3.0.29 and the 7.3 ODBC
>driver on Windows 2000. Any suggestions? The dbproc (stripped down to
>minimum fields; parameters aren't used in this section of the dbproc) is
>below:
>CREATE DBPROC spCalendarGetList (
> IN hint int,
> IN selDate date,
> IN fkGeneral int
>) RETURNS CURSOR AS
>
> DECLARE :$CURSOR CURSOR FOR
> SELECT
> tblCalendar.pkCalendar,
> tblCalendar.Headline
> FROM
> Brian.tblCalendarDtl
> INNER JOIN
> Brian.tblCalendar on tblCalendarDtl.fkCalendar = tblCalendar.pkCalendar
> INNER JOIN
> Brian.tblUser ON tblCalendar.fkUser = tblUser.pkUser
> INNER JOIN
> Brian.tblEventType ON tblCalendar.fkEventType = tblEventType.pkEventType
> WHERE
> tblCalendar.isApproved = true AND
> DATE <= tblCalendarDtl.EventDate AND
> DATEDIFF( DATE, tblCalendarDtl.EventDate ) < 7
>
> UNION
>
> SELECT
> tblCalendar.pkCalendar,
> tblCalendar.Headline
> FROM
> Brian.tblCalendarDtl
> INNER JOIN
> Brian.tblCalendar on tblCalendarDtl.fkCalendar = tblCalendar.pkCalendar
> INNER JOIN
> Brian.tblUser ON tblCalendar.fkUser = tblUser.pkUser
> INNER JOIN
> Brian.tblEventType ON tblCalendar.fkEventType = tblEventType.pkEventType
> WHERE
> tblCalendar.isApproved = true AND
> UPPER( tblEventType.EventType ) = 'DEADLINE' AND
> DATE <= tblCalendarDtl.EventDate AND
> DATEDIFF( DATE, tblCalendarDtl.EventDate ) < 30;
>
This is a known bug which occurs when using the $cursor variable in combination with a
union select.
The fix will be available with 7.3.0.35 / 7.4.3.19.
As far as I remember as a workaround you could try to substitute
the variable by a constant, for example :
$CURSOR = 'MYCURSOR';
DECLARE MYCURSOR CURSOR FOR SELECT ...
Regards,
Thomas
--
Thomas Anhaus
SAP DB, SAP Labs Berlin
thomas.anhaus@stripped
http://www.sapdb.org/
_______________________________________________
sapdb.general mailing list
sapdb.general@stripped
http://listserv.sap.com/mailman/listinfo/sapdb.general
