Re: perplexing problem....

"Glen A Stromquist" <gstromquist_at_hotmail.com> wrote in message
news:TYLp7.2034$9j.607724_at_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

First of all: please don't post in HTML. Many newsreaders don't know how to deal with it.
Secondly: your *real* problem is you are running 7.3.3 and 7.2.3 on platforms (win2k) for which it was *never* certified Thirdly : evidently the problem is the setting of NLS_DATE_FORMAT which is by default DD-MON-YY, and as you are not using the correct format mask in your procedure (ie you are relying on client defaults which you should *never ever* do), you get this problem.

IMO, your problem is not perplexing at all.

Hth,

Sybrand Bakker, Senior Oracle DBA Received on Tue Sep 18 2001 - 13:15:44 CDT