PDA

View Full Version : Data Conversion problems with MS-SQL CK 4.0



Data_Access_Worldwide
19-Dec-2005, 11:44 AM
I have been asked to do a comparison evaluation of DAW's and Mertech's
MS-SQL drivers for Dataflex by a company currently running with a very large
native Dataflex database, and who also uses MS-SQL for other critical
applications. I have lots of experience with Mertech's driver. It just
works. However, I haven't worked with DAW's driver since version 1 and am
having a couple of issues converting Dataflex tables using VDF 10.1 DB
Builder with DAW's driver, version 4.0.0.35.

1) I cannot convert Dataflex system tables unless I add an index to the
table prior to converting. Obviously this isn't a show-stopper, but it seems
like an obvious omission. The driver ought to understand that a Dataflex
table with a max. records setting of 1 and no defined indices is a system
table, and if SQL Server needs an index, then by all means just create one
using the Record Identity column.

2) Any Dataflex table that uses Recnum as the last segment of an index, that
has data in that index that relies on the recnum value to make it unique is
resulting in duplicate record errors and that data fails to get loaded into
the SQL table.

I am testing under XP-Pro and MS-SQL Server version 8. I am using these
settings in the driver's configuration file:
DEFAULT_NULLABLE_ASCII 0
DEFAULT_NULLABLE_NUMERIC 0
DEFAULT_NULLABLE_DATE 0
DEFAULT_NULLABLE_TEXT 0
DEFAULT_NULLABLE_BINARY 0
DEFAULT_DEFAULT_ASCII ''
DEFAULT_DEFAULT_NUMERIC 0
DEFAULT_DEFAULT_DATE '1753-01-01'
DEFAULT_DEFAULT_TEXT ''
DEFAULT_DEFAULT_BINARY 0
DEFAULT_RECORD_IDENTITY_HIDING 0


I can't even validate that DAW's driver will work for us at this point, let
alone doing a meaningful comparison of these two drivers until I can
successfully convert all of the actual data in the database.

Am I doing something wrong, or missing something? (I did look in the user's
guide, in DAW's Knowledgebase and on WASP for answers but couldn't find any
references to these issues.)

Also, the evaluation license I have installed expires in 9 days. How do I
get this extended?

Finally, I saw a 12/15 post from Steve Meeley regarding a 4.1 driver dll but
could not find it on DAW's FTP site. Perhaps it would help. How is it
available?

Thanks,
Bob

P.S. Why must the conversion process regenerate all of the .FD files when no
structure changes are being made. Altering date stamps on such files
interferes with their administration procedures of their development
environment.

Bob Cergol
19-Dec-2005, 12:38 PM
OOPS! Sorry for the misleading "from" name. I don't know how it happened,
but I've fixed it so my name shows up properly.

"Data_Access_Worldwide" <rcergol@compuserve.com> wrote in message
news:MZaw6tLBGHA.5164@dacmail.dataaccess.com...
>I have been asked to do a comparison evaluation of DAW's and Mertech's
>MS-SQL drivers for Dataflex by a company currently running with a very
>large native Dataflex database, and who also uses MS-SQL for other critical
>applications. I have lots of experience with Mertech's driver. It just
>works. However, I haven't worked with DAW's driver since version 1 and am
>having a couple of issues converting Dataflex tables using VDF 10.1 DB
>Builder with DAW's driver, version 4.0.0.35.
>
> 1) I cannot convert Dataflex system tables unless I add an index to the
> table prior to converting. Obviously this isn't a show-stopper, but it
> seems like an obvious omission. The driver ought to understand that a
> Dataflex table with a max. records setting of 1 and no defined indices is
> a system table, and if SQL Server needs an index, then by all means just
> create one using the Record Identity column.
>
> 2) Any Dataflex table that uses Recnum as the last segment of an index,
> that has data in that index that relies on the recnum value to make it
> unique is resulting in duplicate record errors and that data fails to get
> loaded into the SQL table.
>
> I am testing under XP-Pro and MS-SQL Server version 8. I am using these
> settings in the driver's configuration file:
> DEFAULT_NULLABLE_ASCII 0
> DEFAULT_NULLABLE_NUMERIC 0
> DEFAULT_NULLABLE_DATE 0
> DEFAULT_NULLABLE_TEXT 0
> DEFAULT_NULLABLE_BINARY 0
> DEFAULT_DEFAULT_ASCII ''
> DEFAULT_DEFAULT_NUMERIC 0
> DEFAULT_DEFAULT_DATE '1753-01-01'
> DEFAULT_DEFAULT_TEXT ''
> DEFAULT_DEFAULT_BINARY 0
> DEFAULT_RECORD_IDENTITY_HIDING 0
>
>
> I can't even validate that DAW's driver will work for us at this point,
> let alone doing a meaningful comparison of these two drivers until I can
> successfully convert all of the actual data in the database.
>
> Am I doing something wrong, or missing something? (I did look in the
> user's guide, in DAW's Knowledgebase and on WASP for answers but couldn't
> find any references to these issues.)
>
> Also, the evaluation license I have installed expires in 9 days. How do I
> get this extended?
>
> Finally, I saw a 12/15 post from Steve Meeley regarding a 4.1 driver dll
> but could not find it on DAW's FTP site. Perhaps it would help. How is it
> available?
>
> Thanks,
> Bob
>
> P.S. Why must the conversion process regenerate all of the .FD files when
> no structure changes are being made. Altering date stamps on such files
> interferes with their administration procedures of their development
> environment.
>

Ben Weijers
19-Dec-2005, 02:36 PM
The enclosed dll is the latest 4.1 version.

Regards,

Ben Weijers
Data Access Worldwide

Chris Spencer
19-Dec-2005, 03:20 PM
Ben (and Stephen Meeley)

You are always very helpful with newer versions that fix CK problems.
However sometimes these can me missed.
Is there any plan now that the Studio has the start centre to announce newer
CKs through that option and/or to make them available from the ftp site?


Chris Spencer
TUFware Systems

"Ben Weijers" <Ben.Weijers@dataaccess.nl> wrote in message
news:ekog2NNBGHA.4288@dacmail.dataaccess.com...
> The enclosed dll is the latest 4.1 version.
>
> Regards,
>
> Ben Weijers
> Data Access Worldwide
>
>

Bob Cergol
19-Dec-2005, 04:15 PM
Thanks Ben, that fixed both issues.
Bob

"Ben Weijers" <Ben.Weijers@dataaccess.nl> wrote in message
news:ekog2NNBGHA.4288@dacmail.dataaccess.com...
> The enclosed dll is the latest 4.1 version.
>
> Regards,
>
> Ben Weijers
> Data Access Worldwide
>
>

Bob Cergol
19-Dec-2005, 04:53 PM
Ben,

As I mentioned in my other email the 4.1 version dll fixed those issues and
the entire database converted without any errors. However I am wondering if
there isn't one lingering issue.

I get the following message when beginning a conversion to a freshly created
SQL database.

"The Database option ARITHABORT is set off for database "TEST_DAW_DRV".
It must be set on in order for uppercase segments to function.
Do you want the Dataflex Connectivity Kit for SQL Server attempt to set it
now?"

I answered "yes" to this prompt since uppercase index segments do exist in
this database. The SQL table is created with add'l columns that uppercase
the ASCII columns specified as uppercased index segments in the Dataflex
table. This is unnecessary if the collation specified for the SQL database
is case-insensitive -- I'm using as the default:
SQL_Latin1_General_CP1_CI_AS -- but I can live with it.

However, the real potential problem is that it creates these columns with
the "allow nulls" attribute. I thought the rule was to never allow nulls in
columns that will be used in an index because it causes abysmal performance.
All other columns do not allow nulls, per the settings in MSSQLDRV.INT

Is this intentional? -- or an oversight in the driver? Should I change this
attribute using the "Design Table" option in Enterprise Manager?

Thanks,
Bob


"Ben Weijers" <Ben.Weijers@dataaccess.nl> wrote in message
news:ekog2NNBGHA.4288@dacmail.dataaccess.com...
> The enclosed dll is the latest 4.1 version.
>
> Regards,
>
> Ben Weijers
> Data Access Worldwide
>
>

Marco
19-Dec-2005, 10:01 PM
Bob,

I would say that the uppercase columns are automatically the uppercase
value of the initial column. If the initial column cannot be null, then
the uppercase column can also never be null.

I'm not sure if specifically setting these columns to not null, would
influence speed of indexes etc, even if there are not any nulls.

Cheers,
Marco

Bob Cergol wrote:
> Ben,
>
> As I mentioned in my other email the 4.1 version dll fixed those issues and
> the entire database converted without any errors. However I am wondering if
> there isn't one lingering issue.
>
> I get the following message when beginning a conversion to a freshly created
> SQL database.
>
> "The Database option ARITHABORT is set off for database "TEST_DAW_DRV".
> It must be set on in order for uppercase segments to function.
> Do you want the Dataflex Connectivity Kit for SQL Server attempt to set it
> now?"
>
> I answered "yes" to this prompt since uppercase index segments do exist in
> this database. The SQL table is created with add'l columns that uppercase
> the ASCII columns specified as uppercased index segments in the Dataflex
> table. This is unnecessary if the collation specified for the SQL database
> is case-insensitive -- I'm using as the default:
> SQL_Latin1_General_CP1_CI_AS -- but I can live with it.
>
> However, the real potential problem is that it creates these columns with
> the "allow nulls" attribute. I thought the rule was to never allow nulls in
> columns that will be used in an index because it causes abysmal performance.
> All other columns do not allow nulls, per the settings in MSSQLDRV.INT
>
> Is this intentional? -- or an oversight in the driver? Should I change this
> attribute using the "Design Table" option in Enterprise Manager?
>
> Thanks,
> Bob
>
>
> "Ben Weijers" <Ben.Weijers@dataaccess.nl> wrote in message
> news:ekog2NNBGHA.4288@dacmail.dataaccess.com...
>
>>The enclosed dll is the latest 4.1 version.
>>
>>Regards,
>>
>>Ben Weijers
>>Data Access Worldwide
>>
>>
>
>
>

Bob Cergol
20-Dec-2005, 10:03 AM
Of course (duh!) those fields have a formula that sets them equal to the
upper-cased value of the source data column, so it shouldn't (can't?) be a
problem. It just jumped out at me as an inconsistency when I was inspecting
the table definition. (Inconsistencies worry me...)

thanks.
Bob

"Marco Kuipers" <marco.kuipers@nci.com.au> wrote in message
news:it4kyGRBGHA.5164@dacmail.dataaccess.com...
> Bob,
>
> I would say that the uppercase columns are automatically the uppercase
> value of the initial column. If the initial column cannot be null, then
> the uppercase column can also never be null.
>
> I'm not sure if specifically setting these columns to not null, would
> influence speed of indexes etc, even if there are not any nulls.
>
> Cheers,
> Marco
>
> Bob Cergol wrote:
>> Ben,
>>
>> As I mentioned in my other email the 4.1 version dll fixed those issues
>> and the entire database converted without any errors. However I am
>> wondering if there isn't one lingering issue.
>>
>> I get the following message when beginning a conversion to a freshly
>> created SQL database.
>>
>> "The Database option ARITHABORT is set off for database "TEST_DAW_DRV".
>> It must be set on in order for uppercase segments to function.
>> Do you want the Dataflex Connectivity Kit for SQL Server attempt to set
>> it
>> now?"
>>
>> I answered "yes" to this prompt since uppercase index segments do exist
>> in this database. The SQL table is created with add'l columns that
>> uppercase the ASCII columns specified as uppercased index segments in the
>> Dataflex table. This is unnecessary if the collation specified for the
>> SQL database is case-insensitive -- I'm using as the default:
>> SQL_Latin1_General_CP1_CI_AS -- but I can live with it.
>>
>> However, the real potential problem is that it creates these columns with
>> the "allow nulls" attribute. I thought the rule was to never allow nulls
>> in columns that will be used in an index because it causes abysmal
>> performance. All other columns do not allow nulls, per the settings in
>> MSSQLDRV.INT
>>
>> Is this intentional? -- or an oversight in the driver? Should I change
>> this attribute using the "Design Table" option in Enterprise Manager?
>>
>> Thanks,
>> Bob
>>
>>
>> "Ben Weijers" <Ben.Weijers@dataaccess.nl> wrote in message
>> news:ekog2NNBGHA.4288@dacmail.dataaccess.com...
>>
>>>The enclosed dll is the latest 4.1 version.
>>>
>>>Regards,
>>>
>>>Ben Weijers
>>>Data Access Worldwide
>>>
>>>
>>
>>