When you turn WebFOCUS SQL traces on, you can copy the generated SQL and run it in MS SQL Server Enterprise Manager, EXCEPT when Date-Time columns are used in selection criteria- the generated SQL is not compatible.

This example, copied from WebFOCUS, does not work when run in SQL Server Enterprise Manager, I get the error "Your entry cannot be converted to a valid date time value.".

SELECT T1."ActivityEnd",T1."BMOAreaName",T1."ActivityId",
MIN(T1."ActivityEnd"), MIN(T1."RegardingObjectIdName"),
MIN(T1."Subject") FROM GGOF_CRMEXT.dbo.ActivityPointerView T1
WHERE (T1."ActivityEnd" < '20080925 04:00:00.000') AND
(T1."ActivityEnd" >= '20080922 04:00:00.000') AND
(T1."IsWorkflowCreated" <> 1) AND (T1."BMORSMId" =
'{FD668BD1-BF75-DD11-BBA7-001438BFF493}') AND
(T1."ActivityTypeCode" = 4201) GROUP BY T1."ActivityEnd",
T1."BMOAreaName",T1."ActivityId" ORDER BY T1."ActivityEnd",
T1."BMOAreaName",T1."ActivityId";

I have to modify the Date-Time values for the SQL to work. This works:

SELECT T1."ActivityEnd",T1."BMOAreaName",T1."ActivityId",
MIN(T1."ActivityEnd"), MIN(T1."RegardingObjectIdName"),
MIN(T1."Subject") FROM GGOF_CRMEXT.dbo.ActivityPointerView T1
WHERE (T1."ActivityEnd" < '2008-09-25 04:00:00') AND
(T1."ActivityEnd" >= '2008-09-22 04:00:00') AND
(T1."IsWorkflowCreated" <> 1) AND (T1."BMORSMId" =
'{FD668BD1-BF75-DD11-BBA7-001438BFF493}') AND
(T1."ActivityTypeCode" = 4201) GROUP BY T1."ActivityEnd",
T1."BMOAreaName",T1."ActivityId" ORDER BY T1."ActivityEnd",
T1."BMOAreaName",T1."ActivityId";

I suppose this is a rhetorical question: why does WebFOCUS generate SQL that can't be executed outside WebFOCUS?

This message has been edited. Last edited by: Kerry,

Not my strong expertise --- but we generate SQL that is compatible with the SQL PREPARE call. Searching MSDN for the error msg, you see that certain parts of enterprise manager support different date formats. That leads me to believe that they use multiple pre-parse engines and these engines are not as robust (or as forgiving) as the underlying DBMS parser. (The GRID pane supports local-specific, the SQL pane does not...)

Thanks Brian.