Anyone using MS SQL dataserver with OpenEdge 11.7? - Forum - OpenEdge General - Progress Community

Anyone using MS SQL dataserver with OpenEdge 11.7?

 Forum

Anyone using MS SQL dataserver with OpenEdge 11.7?

This question is not answered

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

All Replies
  • 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
     
  • 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

  • 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
     
     
  • 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

  • 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
     
     
  • 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

  • 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

  • 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
     
  • 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  

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

  • 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

  • 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
     
  • 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

  • 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

  • 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
  • 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