perplexing problem....

From: Glen A Stromquist <gstromquist_at_hotmail.com>
Date: Tue, 18 Sep 2001 18:00:19 GMT
Message-ID: <TYLp7.2034$9j.607724@news1.telusplanet.net>

I have an application written in MS access that has been a constant source of headaches, here is the latest...

database's are 7.3.3 on NT4, 7.2.3 on AIX dblinked together.

Access app calls a procedure on the 7.3.3 db with the following via a sql pass thru query:

begin PROCEDURE('01-Sep-2001', '15-Sep-2001', 15); end;

this is where an ODBC 3146 error is generated, after running a trace on the ODBC I found this:

"WBS             b0c-91c ENTER SQLExecDirect 
  HSTMT               0AA51930
  UCHAR *             0x0012ECE8 [      -3] "begin PROCEDURE('01-Sep-2001', '15-Sep-2001', 15); end;\ 0"
  SDWORD                    -3

WBS             b0c-91c EXIT  SQLExecDirect  with return code -1 (SQL_ERROR)
  HSTMT               0AA51930
  UCHAR *             0x0012ECE8 [      -3] "begin PROCEDURE('01-Sep-2001', '15-Sep-2001', 15); end;\ 0"
  SDWORD                    -3

DIAG [S1000] [MERANT][ODBC Oracle driver][Oracle]ORA-01830: date format picture ends before converting entire input string ORA-06512: at line 1
(1830)"

interestingly enough, running the procedure from TOAD fails as well, but from DB artisan it runs ok.

I copied & pasted the text access passes to oracle as well in to sqlplus and svrmgrl, and the ORA-01830 error was generated.

On one machine the process that triggers this call actually worked, investigation revealed that it also had oracle ODBC's set up in the user dsn as well as Merant ODBC's in the system dsn, this same setup was causing an "ODBC-3151 failed to insert on a linked table" error on a different, but identically configured machine. When the Oracle ODBC's were deleted it worked fine.

All client machines are WIN 2000

This has been an ongoing problem, a while ago after talking to Oracle I set all of the registries for NLS_LANG to n/a, which cleared up a lot of ODBC problems, but obviously not all of them.

The short and long date formats in windows regional settings are also set at what they need to be.

Ideas anyone?

thanks in advance Received on Tue Sep 18 2001 - 13:00:19 CDT