• You are here:  
  • Home
  • Q&A
  • Q&A
  • PowerBuilder
  • Problems with using other ODBC drivers then SQL Server

Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (3)

Resolved Problems with using other ODBC drivers then SQL Server

  1. Issue
0
Votes
Undo
  1. Kees van Heerden
    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

Capacity problems with base64encode and jsonpackag...
Cannot open the Data Source
Responses (3)
Michael Kramer
Michael Kramer Accepted Answer Pending Moderation
  1. Tuesday, 3 December 2019 13:58 PM UTC
  2. PowerBuilder
  3. # 1

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.
Kees van Heerden
Kees van Heerden Accepted Answer Pending Moderation
  1. Tuesday, 3 December 2019 14:22 PM UTC
  2. PowerBuilder
  3. # 2

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
Michael Kramer
  1. Michael Kramer
  2. Tuesday, 3 December 2019 15:00 PM UTC
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. Helpful
There are no comments made yet.
Aron Cox
Aron Cox Accepted Answer Pending Moderation
  1. Wednesday, 4 December 2019 12:56 PM UTC
  2. PowerBuilder
  3. # 3

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
Kees van Heerden
  1. Kees van Heerden
  2. Wednesday, 4 December 2019 15:22 PM UTC
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. Helpful
Aron Cox
  1. Aron Cox
  2. Thursday, 5 December 2019 01:35 AM UTC
Sorry, missed that you said you don't use ntext in the database. In fact totally missed the whole question!!!!



  1. Helpful
Kees van Heerden
  1. Kees van Heerden
  2. Thursday, 5 December 2019 13:28 PM UTC
:-)
  1. Helpful
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.