1. Kees van Heerden
  2. PowerBuilder
  3. Tuesday, 3 December 2019 13:06 PM UTC

We always used the SQL Server ODBC driver for connection.

However, SQL Server data are not accepted on servers that doesn't allow SSL 1.0.

Therefore we want to use other drivers ({SQL Server Native Client 11.0} or {ODBC Driver 13 for SQL Server} or {ODBC Driver 17 for SQL Server}).

Using these other drivers we get an error updating a datastore with a nvarchar(max) (nvarchar(max) in the SQL table): The data types nvarchar(max) and ntext are incompatible in the equal to operator.

I found a difference in the created syntax (createsyntaxfromsql) between the SQL Server and the other servers.

In SQL Server the nvarchar(max) column is defined with updatewhereclause=no, while in the other servers it is updatewhereclause=yes. This explains the error.

I can solve the problem with a modify updatewhereclause=no, but I think it is better that it works fine without a hack.

I wonder if there is a bug in the ODBC driver or in Powerbuilder (we use PB 2017 R3).

Has somebody the same problem and what to do about it?

 

Thank you for reading,

Kees van Heerden

Aron Cox Accepted Answer Pending Moderation
  1. Wednesday, 4 December 2019 12:56 PM UTC
  2. PowerBuilder
  3. # 1
0
Votes
Undo

Ntext is marked as deprecated so I recommend you replace any ntext columns with nvarchar(max), and that ought to make this issue go away.

We did that a couple of years ago, and recently started to use the ODBC driver 17 for SQL Server with no real issues.

We did have to fiddle with some code that was doing an updateblob, and we had to tick "Use FMTONLY metadata discovery" in the DSN configuration settings.

Comment
As I stated, we only use nvarchar(max) and don't use ntext columns in our sql databases. Therefore I don't know why we get the error 'The data types nvarchar(max) and ntext are incompatible in the equal to operator.'.

So where does the ntext in the error come from. The driver or powerbuilder?

  1. Kees van Heerden
  2. Wednesday, 4 December 2019 15:22 PM UTC
Sorry, missed that you said you don't use ntext in the database. In fact totally missed the whole question!!!!



  1. Aron Cox
  2. Thursday, 5 December 2019 01:35 AM UTC
:-)
  1. Kees van Heerden
  2. Thursday, 5 December 2019 13:28 PM UTC
There are no comments made yet.
Kees van Heerden Accepted Answer Pending Moderation
  1. Tuesday, 3 December 2019 14:22 PM UTC
  2. PowerBuilder
  3. # 2
0
Votes
Undo

Thank you for your fast response.

The settings we are using:

[Microsoft SQL Server]
PBCatalogOwner='dbo'
PBSystemOwner='sys'
PBSyntax='MS_SQLSERVER_SYNTAX'
PBDateTime='MSSQLSERVER_DATETIME'
PBFunctions='MSSQL_FUNCTIONS'
PBSpecialDataTypes='SYBASE_SPECIALDATATYPES'
PBObjectIDs='YES'
PBMaxBlobSize='2147483647'
PBMaxTextSize='2621440'
PBDWDynamic='NO'
PBPrimaryKeyName='YES'
Block=1
SQLSrvrTSPrefix='0x'
PBUseProcOwner='YES'
PBDefaultValues='USER,SYSTEM_USER,USER_ID(),HOST_ID(),HOST_NAME(),DB_ID(),DB_NAME(),CURRENT_TIMESTAMP,NULL'
PBDefaultCreate='YES'
PBDefaultAlter='NO'
PBDefaultExpressions='YES'

Which parameter you think must be changed?

Regards, Kees

 

Comment
I hope someone from the North America side checks in with this issue later today and has tried this beforehand so they know exactly what to tweak. There are sometimes undocumented options that come to rescue in circumstances like this.

I checked also with "Connection Reference" - alas no luck.
  1. Michael Kramer
  2. Tuesday, 3 December 2019 15:00 PM UTC
There are no comments made yet.
Michael Kramer Accepted Answer Pending Moderation
  1. Tuesday, 3 December 2019 13:58 PM UTC
  2. PowerBuilder
  3. # 3
0
Votes
Undo

Hi Kees,

You may be able to impact the syntax via PBODBxxx.INI. It is part of the configuration that governs the DataWindow engine. I haven't tried this particular combo so it may be outside its scope.

HTH /Michael

Comment
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.