SQL 2005 “LEFT OUTER JOIN” ISSUE


In our C++ application, we call ODBC apis to execute following SQL
statement. It did work for SQL Server 7.0 and SQL server 2000. When we
upgrade to SQL server 2005( and SQLNCLI.dll), it does not work any more
without any error report, it just returns empty result.

Note, not error report. The return code is success. Every thing is looked
perfect, except no content. It should return one row of the result. It works
perfect, if we just select same record from the first table without using
"left outer join" and the correct row was returned.

The sql statement is:

SELECT FM_WEB_DETAIL.TASK_GUID,
FM_WEB_DETAIL.SEQ_NO,
FM_WEB_DETAIL.DATA_DEF_ITEM_GUID,
FM_WEB_DETAIL.LABEL,
FM_WEB_DETAIL.WEB_PART,
FM_DATA_DEF_DETAIL.WEB_PART
FROM { OJ FM_WEB_DETAIL LEFT OUTER JOIN FM_DATA_DEF_DETAIL
ON FM_WEB_DETAIL.DATA_DEF_ITEM_GUID = FM_DATA_DEF_DETAIL.DATA_DEF_ITEM_GUID }
WHERE FM_WEB_DETAIL.PROCESS_GUID = ?
AND FM_WEB_DETAIL.TASK_GUID = ?
ORDER BY FM_WEB_DETAIL.SEQ_NO

We guess that the curly brick may not ansi standard, we changed the
statement to:

SELECT FM_WEB_DETAIL.TASK_GUID,
FM_WEB_DETAIL.SEQ_NO,
FM_WEB_DETAIL.DATA_DEF_ITEM_GUID,
FM_WEB_DETAIL.LABEL,
FM_WEB_DETAIL.WEB_PART,
FM_DATA_DEF_DETAIL.WEB_PART
FROM FM_WEB_DETAIL LEFT OUTER JOIN FM_DATA_DEF_DETAIL
ON FM_WEB_DETAIL.DATA_DEF_ITEM_GUID = FM_DATA_DEF_DETAIL.DATA_DEF_ITEM_GUID
WHERE FM_WEB_DETAIL.PROCESS_GUID = ?
AND FM_WEB_DETAIL.TASK_GUID = ?
ORDER BY FM_WEB_DETAIL.SEQ_NO

But, it still does not work. Later, we try to use “*= “ for “Left outer
join”, but it does not work either.

SELECT FM_WEB_DETAIL.TASK_GUID,
FM_WEB_DETAIL.SEQ_NO,
FM_WEB_DETAIL.DATA_DEF_ITEM_GUID,
FM_WEB_DETAIL.LABEL,
FM_WEB_DETAIL.WEB_PART,
FM_DATA_DEF_DETAIL.WEB_PART
FROM FM_WEB_DETAIL, FM_DATA_DEF_DETAIL
Where FM_WEB_DETAIL.DATA_DEF_ITEM_GUID *=
FM_DATA_DEF_DETAIL.DATA_DEF_ITEM_GUID
AND FM_WEB_DETAIL.PROCESS_GUID = ?
AND FM_WEB_DETAIL.TASK_GUID = ?
ORDER BY FM_WEB_DETAIL.SEQ_NO

However, all three style statements are working find on “SQL server 2005
query analyzer”.


Does any body know what the problem is? IT may be the ODBC driver is not
working perfect with SQL server 2005. We checked that our ODBC connection
indeed using the new driver "sqlncli.dll". Btw, it is not working with old
driver either.

Any feedback is appreciated. Thanks for your time.