O7: ODBC missing data from SQLServer - SOLVED

Gavin Foster omnislist at dataweaver.com
Tue Jun 21 09:19:30 EDT 2016

That’s the heart of it, Phil.
In summary:


- nvarchar(x) columns in Unicode database being accessed by Unicode Omnis 7 using 32 bit SQL Server ODBC driver.

- if any column is filled to the maximum of ‘x’ characters and a select is performed against that table with greater than approx 60 columns, the last character in that field is lost in the retrieval.


- Either extend the length of all nvarchar(x) columns to nvarchar(x+1) but don’t bother filling the last character with data.

- Or use varchar(x) instead. This is not recommended by many database specialists as you are storing your data under the old single-byte, ascii, non-unicode format and you could have problems with national characters and interfacing with other systems. However, it may be a temporary solution for migrating Omnis 7 data into SQL.

Thanks for all help and input, Phil, Alain and Mark.
Rgds, Gav

> On 21 Jun 2016, at 13:25, Phil (OmnisList) <phil at pgpotter.co.uk> wrote:
> Hi Gav,
> if you are not using unicode in Omnis 7, why are you using nvarchar over varchar?
> regards
> Phil Potter
> Based in Chester in the UK.
> On 21/06/2016 12:27, Gavin Foster wrote:
>> Hi Phil,
>> Date modified: 09/02/2001 13:39
>> Size: 84kB
>> Apparently there are many reports of issues around ODBC to unicode databases from non-unicode applications.
>> It seems to affect Oracle as well as SQLServer.
>> Just Google 'odbc non unicode reading nvarchar’.
>> [Thanks to Mark Wood for that one.]
>> So the issue seems to be the nvarchar fields.
>> I’m losing one character from a nvarchar(1) column and the 8th character from an nvarchar(8) column.
>> We just discovered that extending the nvarchar(1) to nvarchar(2) fixes the loss.
>> I’m now going to try maxing out all nvarchar columns (where there its max is 90 chars, I’ll put in 90)…and then I’ll see if we are consistently losing the final character.
>> Rgds, Gav
>>> On 21 Jun 2016, at 12:17, Phil (OmnisList) <phil at pgpotter.co.uk> wrote:
>>> Hi Gav,
>>> An interestingly old SQL server driver?
>>> On my win 10 machine its version 10.00.10586.00
>>> Dated 30/10/2015
>>> what date is your o7odbc.dll ?
>>> maybe relevant?
>>> regards
>>> Phil Potter
>>> Based in Chester in the UK.
>>> On 21/06/2016 11:13, Gavin Foster wrote:
>>>> Hi Phil,
>>>> Omnis 7: v3,8,1
>>>> ODBC:
>>>> 	'SQL Server’
>>>> 	filename: SQLSRV32.DLL
>>>> 	version 6.01.7601.17514
>>>> 	dated 21/11/2010
>>>> I tried other more up to date ODBC drivers but they seem to have problems like simply returning nondescript errors from O7 when you execute a select or a stored proc.
>>>> Rgds, Gav
>>>>> On 21 Jun 2016, at 10:50, Phil (OmnisList) <phil at pgpotter.co.uk> wrote:
>>>>> Hi Gav,
>>>>> What version of Omnis 7, or the o7odbc.dll are you using?
>>>>> half recall someone having this issue, but don't recall what the answer was....
>>>>> I latterly used O7v3832 and used a dll dated 9/2/2001 84kb.
>>>>> regards
>>>>> Phil Potter
>>>>> Based in Chester in the UK.
>>>>> On 20/06/2016 23:44, Gavin Foster wrote:
>>>>>> Hi all,
>>>>>> I’m migrating an Omnis 7 application to SQLServer, using ODBC to connect.
>>>>>> This is Windows 7, Omnis 7 (32 bit) with ODBC connection created using C:/Windows/syswow64/odbcad32.exe
>>>>>> All the data goes into the database, using $insertnames / $updatenames and seems fine.
>>>>>> However if we do a “select * from <table>” and fetch a row / build a list based on a large file format (like 200 fields), then characters get lost. This only happens with particularly large (wide, multi-column) results sets.
>>>>>> i.e.
>>>>>> At various points along the row(s) of data, a character in the results simply doesn’t end up in the CRB.
>>>>>> It might be a char(1) field which ends up blank.
>>>>>> It might be a char(9) field which only has 8 characters in it, and the last one has gone awol.
>>>>>> Can anyone remember this issue from the time they used O7?
>>>>>> - is it the ODBC driver we’re using (SQL Server)?
>>>>>> - is it a fault in O7 that was fixed in some patch release or not?
>>>>>> Note that all the data is returned in Studio 8 v1.2.1
>>>>>> However, that is 64 bit Studio on Windows 7 with a 64 bit ODBC connection.
>>>>>> Thanks and regards,
>>>>>> Gav
>>>>>> _____________________________________________________________
> _____________________________________________________________
> Manage your list subscriptions at http://lists.omnis-dev.com

More information about the omnisdev-en mailing list