odbc cursor type - db system settings

[lovitz]

Hi everyone.
Last week we changed database from SQL 2000 SP4 to SQL 2005 SP2 update 6 and now we have notice that we are having an issue with JDE batch processing on workstations (local processing). Sometimes happens that we get empty reports. But when we pass this report via batch server everything is fine. We found out that there is difference in database setting in jde.ini file.
On batch server we have:
[DB SYSTEM SETTINGS]
Version=43
ODBCCursors=1
CursorType=3
on workstations there is noting about ODBC Cursor parameters.
So when we paste this section to workstation jde.ini file, report works fine. After that in jdedebug file we have this message: Data selection processed with high concurrency option.
So when ODBC cursor type is set to 3 (static) JDE first query database using standard select statement and after 15sec query again using optimizer hint NOLOCK. When we don't set up cursor type in db settings in jde.ini then JDE is querying db only ones with standard statement (we check this using db profiler) using fast forward cursor and after 15sec JDE continues processing further returning empty data.
My question is: Do u have experience whit this issue and what settings do u have in JDE.INI file on yours batch servers and workstations.

Thanks.

 

[davepave]

Hi!

I am struggling with the same issue. Reports get empty data sets. I think it has something to do with autofetch option of the dynamic cursor, but this is only a guess based on some logging and sniffing performed.

Indeed, switching to the static cursor type seem to solve the problem but I am wondering about any drawbacks of such change before applying it into production environment. I suspect at least a slight performance drop.

Can anybody help? What cursor types do you use with JDE and MS SQL, could you please take a look into your JDE.INI files?

Best Regards
David
--------------------
JDEdwards Oneworld XE / MS SQL 2005
SP23_G1