Anyone using MS SQL dataserver with OpenEdge 11.7?

Posted by slegian-bva on 24-Jun-2018 23:18

Hi guys,

We have a client that needs to use MS SQL for their database.

We started with 11.7.2 and had all sorts of client session crashes. PSC advised to upgrade to 11.7.3. This solved the initial crashes only to create different ones... I have calls logged with tech support for this.

While waiting on an official resolution I wanted to throw this out there, see if anyone else is using OE 11.7 with MS SQL and if so which service pack level of OE and what has been the experience to date.

We have clients using OE 11.5 with MS SQL without any known issues...

Cheers,
Simi

OpenEdge General - Forum

All Replies

Posted by Mukesh Singh on 25-Jun-2018 02:08

With 11.7.3 you shouldn’t see any issue except when an application re-uses statements which is dependent on the value set to PRGRS_PREPCACHE (ODIA-1732, it is fixed in 11.7.4). We have also made changes in dataserver compiler layer for single-shot query (e.g. FOR EACH customer, FIRST order OF customer) in 11.7.3. So if an application uses single-shot query and is compiled with 11.7.3, it should be run with 11.7.3 and above (both client and server).
 
Thanks,
Mukesh
 

Posted by slegian-bva on 25-Jun-2018 18:03

Hi Mukesh,

Funny enough it is exactly that FOR EACH customer, FIRST order OF customer that gives us grief in 11.7.3.

That type of query crashes the client session for us in 11.7.3... The same code compiled with 11.7.2 works fine with 11.7.3 dataserver and 11.72 or 11.7.3 client. I raised a ticked with support for it (00448655).

Was wondering what other gremlins await us there...

Cheers,

Simi

Posted by Mukesh Singh on 26-Jun-2018 01:52

Hi Simi,
 
Are you seeing crash when single-shot query is compiled with 11.7.3 and run with 11.7.3 client and 11.7.3 dataserver server? If  it is the case share the reproducible with TSE who is handling case# 00448655.
Note: “FOR EACH customer, FIRST order OF customer”, is very basic use case and we haven’t seen any issue in our environment.
 
Thanks,
Mukesh
 
 

Posted by slegian-bva on 01-Jul-2018 17:40

Hi Mukesh,

I think it's the 11.7.3 compiler that has some issues. We get the crash when we compile the code with 11.7.3 and run with 11.7.3 client & dataserver. The same code compiled with 11.7.2 runs fine with 11.7.3 client/dataserver or 11.7.2 client/dataserver. So the 11.7.3 compiler is the difference... I've updated tech support.

Cheers,

Simi

Posted by Mukesh Singh on 02-Jul-2018 01:58

Hi Simi,
 
We will look into the specific use case that has issue in 11.7.3(hope you have shared the reproducible use case & environment with TSE). As a workaround, you can try appending QUERY-TUNING(NO-JOIN-BY-SQLDB) to the affected query and compile the code  that will disable 11.7.3 compiler feature and evaluate the join query at client side(same behavior like pre 11.7.3).
 
Thanks,
Mukesh
 
 

Posted by slegian-bva on 02-Jul-2018 17:14

Thanks Mukesh,

Changing the code is not really a workaround. It's not one query that has the problem, it's the basic FOR EACH, FIRST join construct that has an issue. We can't change *all* such queries... We would need a client startup parameter to turn that tuning off everywhere.

Cheers,

Simi

Posted by slegian-bva on 02-Jul-2018 17:49

Hi Mukesh,

Found the -nojoinbysqldb startup parameter, but I suppose that will also apply to FOR EACH, EACH queries which actually work fine.

So we eliminate the crash, but take on a performance hit...

Cheers,

Simi

Posted by Mukesh Singh on 03-Jul-2018 02:52

Hi Simi,
 
Yes, using -nojoinbysqldb  will disable  server-side join evaluation for all kind of join queries, and it may result in performance issue for other join queries.  You can share client log and dataserver log file to TSE,  using below parameters that may help us in analyzing this issue:
       Client log parameter : -clientlog client.log -logginglevel 4 -logentrytypes 4GLMessages,4GLTrace,4GLTrans,UBNET,QryInfo
        Dataserver log paramter : -Dsrv logentrytypes,SQL:4,Cursor:4,Trans.
Also share the generated protrace files by placing the symbol files for 11.7.3.  I will discuss  this issue with TSE.
 
Thanks,
Mukesh
 

Posted by Lars Neumeier on 04-Jul-2018 08:14

Simi,

Thank you for posting this problem, we are affected too. Every Single-Shot Query (FOR EACH, FIRST) is generating an error.

---------------------------

Fehler (Press HELP to view stack trace)

---------------------------

37000: [Microsoft][ODBC SQL Server Driver][SQL Server]Wrong syntax near '-'.

---------------------------

OK   Hilfe  

---------------------------

Posted by slegian-bva on 04-Jul-2018 18:22

Hi Lars,

You are getting an error message? You are lucky, we are getting a session crash :)

Try the log options suggested by Mukesh above and see what the logs say, maybe that will provide some hints.

Out of curiosity what OS are you using? At the moment we get the crash on win10 and win7, no crash on win server 2012. Also would you be able to check the version of vcruntime140.dll used by your prowin client?

Happy to report that is the only issue we currently have, everything else seems to work fine. The -nojoinbysqldb startup parameter got rid of the crash...

Cheers,

Simi

Posted by Mukesh Singh on 05-Jul-2018 03:00

Hi Lars,
 
We did a bit analysis on this, I think your owner name contains hyphen “-“ (e.g. test-user)  that could result in this issue. If it the case open a bug for this issue, otherwise you can share the reproducible so that we can look into this issue.
Thanks,
Mukesh
 

Posted by Lars Neumeier on 05-Jul-2018 04:06

Hi Mukesh,

You are right, the user has an hypen, but I don't think, that this is the problem, because it is working as soon as

-nojoinbysqldb is used. I opened a support ticket with Technical Support.

Kind regards,

Lars Neumeier

Posted by Lars Neumeier on 05-Jul-2018 04:17

Hi Simi,

System32 vcruntime140.dll - Version: 14.0.24210.0 (9June 2016)

SysWOW64 vcruntime140.dll - Version: 14.0.24212.0 (13July 2016)

Client System is Windows 10 x64.

Unfortunately, -nojoinbysqldb is only working at compile time and it have a huge performance hit.

Kind regards,

Lars Neumeier

Posted by Mukesh Singh on 05-Jul-2018 05:50

Hi Lars,
 
We have made performance improvement changes in 11.7.3 to evaluate single-shot query on server that may result in significant performance benefit for single shot query. As part of this feature changes is made in dataserver compiler layer and run-time code generation layer, and for that we generate WHERE clause predicates at compiler layer with owner name. It will works fine with -nojoinbyqldb or using QUERY-TUNING(NO-JOIN-BY-SQLDB) as it will disable this feature and will make join evaluation at client side, so this issue won’t be observed as owner name generation will be handled at run-time layer and owner name will be enclosed within quotes so it works fine.
 
You can ask TSE to log a bug for this issue.
 
 
Thanks,
Mukesh

Posted by Lars Neumeier on 05-Jul-2018 13:45

Hi Mukesh,

Thank you for your help. The problem is related to a database name with a hyphen. After changing the database name to a name without a hyphen, the problem went away. A test with some single-shot queries worked without any problems.

Kind regards,

Lars Neumeier

Posted by Mukesh Singh on 06-Jul-2018 04:26

Hi Lars,
 
Thanks for feedback. We have plan to fix this issue in 11.7.4, if you want this fix in 11.7.3 open a hotfix request.
 
Thanks,
Mukesh
 

Posted by slegian-bva on 08-Jul-2018 20:52

Thanks Lars, we seem to get the crash only on machines with vcruntime 14.11.xxx. Machines that have vcruntime 14.0.xxx seem to work fine. Something to keep an eye for...

Cheers,

Simi

This thread is closed