9.0.1 ODBC and MS access 2003 error

[MNJoe]

I am trying to write a new access report and connecting 2 tables in my ERP system (Visual manufacturing from Infor software). The Work_Order table and the Part table. The Work_Order table has a column for a part number but not every work order has a part number. I am trying to do an outer join using the ODBC drivers for SQLBase 9.0.1 driver pack 3 but, I get a run time error 3146 ODBC call failed. If I use the old 3.6 drivers it works just fine. With out having to a lot of back door programming which I probably could do but, does anyone know if there is a fix for this?

Thanks
Joel

[MNJoe]

I have searched for several hours on the internet looking for error 3146 and so far have found something I hope helps. I turned on the odbc logging and I have copied below the last several lines of the log. Hope this helps someone.

Closed_Work_Ord 13c4-13cc ENTER SQLExecDirectW
HSTMT 0B481CE8
WCHAR * 0x0BB027A8 [ -3] "SELECT "SYSADM"."WORK_ORDER"."BASE_ID" ,"SYSADM"."WORK_ORDER"."PART_ID" ,"SYSADM"."WORK_ORDER"."DESIRED_QTY" ,"SYSADM"."WORK_ORDER"."DESIRED_WANT_DATE" ,"SYSADM"."WORK_ORDER"."CLOSE_DATE" ,"SYSADM"."PART"."DESCRIPTION" FROM {oj "SYSADM"."WORK_ORDER" LEFT OUTER JOIN "SYSADM"."PART" ON ("SYSADM"."WORK_ORDER"."PART_ID" = "SYSADM"."PART"."ID" ) } WHERE ((("SYSADM"."WORK_ORDER"."SUB_ID" = '0' ) AND ("SYSADM"."WORK_ORDER"."CLOSE_DATE" BETWEEN ? AND ? ) ) AND ("SYSADM"."WORK_ORDER"."STATUS" = 'C' ) ) \ 0"
SDWORD -3

Closed_Work_Ord 13c4-13cc EXIT SQLExecDirectW with return code -1 (SQL_ERROR)
HSTMT 0B481CE8
WCHAR * 0x0BB027A8 [ -3] "SELECT "SYSADM"."WORK_ORDER"."BASE_ID" ,"SYSADM"."WORK_ORDER"."PART_ID" ,"SYSADM"."WORK_ORDER"."DESIRED_QTY" ,"SYSADM"."WORK_ORDER"."DESIRED_WANT_DATE" ,"SYSADM"."WORK_ORDER"."CLOSE_DATE" ,"SYSADM"."PART"."DESCRIPTION" FROM {oj "SYSADM"."WORK_ORDER" LEFT OUTER JOIN "SYSADM"."PART" ON ("SYSADM"."WORK_ORDER"."PART_ID" = "SYSADM"."PART"."ID" ) } WHERE ((("SYSADM"."WORK_ORDER"."SUB_ID" = '0' ) AND ("SYSADM"."WORK_ORDER"."CLOSE_DATE" BETWEEN ? AND ? ) ) AND ("SYSADM"."WORK_ORDER"."STATUS" = 'C' ) ) \ 0"
SDWORD -3

DIAG [HYC00] [Gupta][ODBC Driver][Parser] [Position (80, 133)] 'OUTER JOIN' keyword is missing (-19)

Closed_Work_Ord 13c4-13cc ENTER SQLErrorW
HENV 0B481788
HDBC 0B481830
HSTMT 0B481CE8
WCHAR * 0x0013D298 (NYI)
SDWORD * 0x0013D2E4
WCHAR * 0x025E2E30
SWORD 4095
SWORD * 0x0013D2D0

Closed_Work_Ord 13c4-13cc EXIT SQLErrorW with return code 0 (SQL_SUCCESS)
HENV 0B481788
HDBC 0B481830
HSTMT 0B481CE8
WCHAR * 0x0013D298 (NYI)
SDWORD * 0x0013D2E4 (-19)
WCHAR * 0x025E2E30 [ 82] "[Gupta][ODBC Driver][Parser] [Position (80, 133)] 'OUTER JOIN' keyword is missing"
SWORD 4095
SWORD * 0x0013D2D0 (82)

Closed_Work_Ord 13c4-13cc ENTER SQLErrorW
HENV 0B481788
HDBC 0B481830
HSTMT 0B481CE8
WCHAR * 0x0013D298 (NYI)
SDWORD * 0x0013D2E4
WCHAR * 0x025E2EEA
SWORD 4002
SWORD * 0x0013D2D0

Closed_Work_Ord 13c4-13cc EXIT SQLErrorW with return code 100 (SQL_NO_DATA_FOUND)
HENV 0B481788
HDBC 0B481830
HSTMT 0B481CE8
WCHAR * 0x0013D298 (NYI)
SDWORD * 0x0013D2E4
WCHAR * 0x025E2EEA
SWORD 4002
SWORD * 0x0013D2D0

Closed_Work_Ord 13c4-13cc ENTER SQLFreeStmt
HSTMT 0B481CE8
UWORD 1 <SQL_DROP>

Closed_Work_Ord 13c4-13cc EXIT SQLFreeStmt with return code 0 (SQL_SUCCESS)
HSTMT 0B481CE8
UWORD 1 <SQL_DROP>

Closed_Work_Ord 13c4-13cc ENTER SQLDisconnect
HDBC 0B481830

Closed_Work_Ord 13c4-13cc EXIT SQLDisconnect with return code 0 (SQL_SUCCESS)
HDBC 0B481830

Closed_Work_Ord 13c4-13cc ENTER SQLFreeConnect
HDBC 0B481830

Closed_Work_Ord 13c4-13cc EXIT SQLFreeConnect with return code 0 (SQL_SUCCESS)
HDBC 0B481830

Closed_Work_Ord 13c4-13cc ENTER SQLFreeEnv
HENV 0B481788

Closed_Work_Ord 13c4-13cc EXIT SQLFreeEnv with return code 0 (SQL_SUCCESS)
HENV 0B481788

[denke]

I am having the same problem. My research shows that the problem stems from an incorrect registry entry.

We are using Crystal Reports 8.5 and SqlBase 9.0.1. Here's a link to an article about join errors http://www.sdn.sap.com/irj/boc/go/porta ... ayout=true

I know SQLBase 6.x and 7.x used the PlusEqual join syntax. I'm trying to find out which join syntax SqlBase 9.0.1 uses and make the appropriate registry entry.

When I get this resolved, I'll post the solution to help others out.

[denke]

Fixed it.

Step 1 - Check the ODBC settings for the driver:

Note that Gupta uses SQLBASEODBC.DLL

Step 2 - Check your registry. You need to ensure your HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI file matches your ODBC settings. In my case it needed to be:


Step 3 - SQLBase uses the PlusEqual join syntax. Make sure you have the Seagate Software registry entries. Put sqlbaseodbc as the first entry in the PlusEqual key. It should look like this:


And that is how you get DataLink Viewer 60002_85 to connect to Visual Mfg via the Gupta 9.0.1 drivers using XP mode on a 64 bit Windows 7 operating system.

Works like a charm.

[Mike Vandine]

Hi Donna,

You beauty!!!

Thanks so much for this solution! This will help many other users as well!!

Best regards,

Mike

[MNJoe]

Thats Ok for Crystal reports but, What about Microsoft access. This does not work for it. There are no such registry settings for MS access. PLUS I have also tested the old ODBC connection using the old Merant SQLBase drivers and this works and also when I use unify's own report writer ReportBuilder 2005 this works also. This tells me that there is something wrong with the SQLBaseoledb.dll and not the registry. Instead of using an outer join on the tables I tried to do 2 SQL queries in access. When I click on each query individually they work. When I try to union them together I end up with the same error. 3146. Now I would use the old Merant drivers except they will not install on a new windows 7 system.

Thanks for your help anyway.
Joel