ODBC Query error

[mikej28621]

I am running an ODBC query from MS Excel agains AS400.

Select B3GLAC, B2TRDT, B1VND#, B2DESC, B1VNAM, B3TITM+B3TFGT+B3TTAX-B3TDSC as Test, B1PON#, B1INV#, B3REF#, B3GLPX FROM YADPOS2.b3billl1 INNER JOIN YADPOS2.b2billl3 on B3REF# = B2REF# and B3SEQ#=B2SEQ# INNER JOIN YADPOS2.b1billl0 on B2REF# = B1REF# where B2TRDT >= '1/1/04' and b3glpx =' ' and B3GLAC = 6423

and getting error

run-time error '-2147217913(80040e07)':

[IBM][iseries access odbc driver][db2 udb]sql0180 - syntax of date, time or timestamp value not valid.

It only errors on one computer the rest run normally.

Any Thoughts

MJ

 

[ETID]

Try updating the client access driver from the setup on that machine.

It seems the driver for iseries is the same as pre iseries but the ID for it, (if it's the old driver) bombs out odbc.

 

[xlbo]

Might also look at the ODBC settings on that machine - you could have the regional settings set up differently

Rgds, Geoff

"Having been erased. the document thjat you are seeking. Must now be retyped"

Please read FAQ222-2244 before you ask a question

 

[ETID]

Another reason could be user permissions to the files / libraries used in the query.

....One other note "Selective setup" if this particular machine was used for roll out testing at any time, selective setup might be pointing to the path of the old setup files use before the iseries upgrade. Check the path carefully as they might be only subtle differences.

 

[mikej28621]

Good advice everyone and thanks again.

The ODBC driver shown above is the older version. I tried it after the newer one failed with the same error.

I am using a DNS'less connection so the regional settings should be the same and I am using the same user name and password in the connection for each computer.

Here are the 2 DNS'less connection stings I used

' cnstr = "DRIVER=iSeries Access ODBC Driver;"
' cnstr = cnstr & "CONNTYPE=2;"
' cnstr = cnstr & "DESC=iSeries Access for Windows ODBC data source;"
' cnstr = cnstr & "SQDIAGCODE=;"
' cnstr = cnstr & "DATABASE=;"
' cnstr = cnstr & "QAQQINILIB=;"
' cnstr = cnstr & "PKG=QGPL/DEFAULT(IBM),2,0,1,0,512;"
' cnstr = cnstr & "TRACEFILENAME=D:\my documents\IBM\Client Access\Service\Trace Files;"
' cnstr = cnstr & "SORTTABLE=;"
' cnstr = cnstr & "LANGUAGEID=ENU;"
' cnstr = cnstr & "XLATEDLL=;"
' cnstr = cnstr & "DFTPKGLIB=QGPL;"
' cnstr = cnstr & "DBQ=," & CurrLib & ";"
' cnstr = cnstr & "SYSTEM=" & ipadd
' cnstr = cnstr & "UID=bla;"
' cnstr = cnstr & "PWD=bla;"

cnstr = cnstr & "DRIVER=Client Access ODBC Driver (32-bit);"
cnstr = cnstr & "PKG=QGPL/DEFAULT(IBM),2,0,1,0,512;"
cnstr = cnstr & "LanguageID = ENU;"
cnstr = cnstr & "DFTPKGLIB = QGPL;"
cnstr = cnstr & "DBQ=qgpl," & CurrLib & ";"
cnstr = cnstr & "SYSTEM=" & ipadd & ";"
cnstr = cnstr & "UID=bla;"
cnstr = cnstr & "PWD=bla;"

I have tried both. The only differences that I know of are that the machine in question is Windows 2k and Office 2k and the development computer is Windows XP and Office XP. But I have other Windows 2k and Office 2k that do work.

I also took out the date portion of the query and it ran normally. I have tried other date formats (thinking it might be a date format issue) on the development computer and it runs normally.

Thanks Again

MJ

 

[ETID]

try replacing the d:\ with the actual server name.


' cnstr = cnstr & "TRACEFILENAME=D:\my documents\IBM\Client Access\Service\Trace Files;"

 

[ETID]

One other thing is this users logon resetting their default library list via a script, rendering odbc unable to find it?

 

[mikej28621]

xlbo you turned out to be correct. I had checked it but I guess I did not get it right. It turned out to be the regional settings. It was giving the date as '1/1/2004' rather then '01/01/2004'

Thanks again to all that answered.

Once again the simplest answer was the correct answer.

Mj

 

[xlbo]

LOL - I only suggested that as I'd literally just come back from someone's machine where they'd been having the same issue !

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question