Anyone noticed strange behv with C10 and NT8 latest - browse + join fields

[Stu]

Hey Folks,

Has anyone noticed this?

Clarion 10 (latest), Nettalk 8.51, MSSQL files.

1. Browse + alias file.
2. Browse shows records.
3. M:1 relationship field added to browse.
  * [EDIT] It's not adding the field to the nettalk browse that causes this, it's adding the related file to the file dialog tree .. whether the field is in the browse or not, the problem happens.
  * [EDIT] The "Inner" file dialog template option is definitely not ticked, but if ticked doesn't stop problem.
4. Browse stops showing records.

Have tried to replicate in an example, but the examples (TPS) are working fine.

Thought I'd ask if this is something people have seen / know what I'm doing wrong / etc.

Cheers

[Stu]

When "Send Filter .." and "Send stage" to debugview, get the following. Doesn't seem to reference the related file field at all, so am not sure what's happening. Will try and dig deeper.

[attachment deleted by admin]

[kevin plummer]

Hi Stu,

there is one SQL example that ships with NT you could play with and post an example.

Otherwise post some screen shots as I think what you are trying to do works fine in C9.1 and NT8.51

Cheers,

Kevin

[Stu]

Thanks Kevin, will have a look at the sql example.

[Stu]

Found something!

When there is a related file in the file dialog, and you do a next(ThisView) in the embed code (check screenshot), you get the following error:

Code: [Select]

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CS_AS" and "Latin1_General_CI_AS" in the equal to operation. Statement(s) could not be prepared.
Any ideas on this?

Stackoverflow (googling showed me a lot of stuff on this) says, http://stackoverflow.com/questions/1607560/cannot-resolve-the-collation-conflict-between-sql-latin1-general-cp1-ci-as-and.

Talks about collations, so I'm hoping/guessing it's a template thing?

[attachment deleted by admin]

[kevin plummer]

I would test on C9.1 to confirm if it is a clarion10 thing or your DB tables are in fact using different collation settings.

[Stu]

Thanks Kevin.

It seems wierd that it's a system-wide issue (whereever and whatever tables I use). And that the issue only came about recently.

So the collation on my tables is "Latin1_General_CI_AS".

Now I'm wondering .. Would FM3 have anything to do with this?

I use it to create/update my sql tables in this system.

But then again, it comes back to .. Was working. Now isn't.

[Stu]

Could the error,

Code: [Select]

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CS_AS" and "Latin1_General_CI_AS" in the equal to operation. Statement(s) could not be prepared.
Be that something (Clarion 10, NT8, FM3, ??) thinks that the collation should be the first but the sql db is the second?

The server is definitely the second, but don't know if that means the tables/fields are, used this:

Code: [Select]

SELECT CONVERT (varchar, SERVERPROPERTY('collation'));

[kevin plummer]

my money is on C10 which is why I suggested testing in C9.1

If it was NT8 or FM3 I would see the same problem in C9.1

BTW what flavour of SQL are you running?

[Stu]

Hey Kevin,

Yeah, I'll have to test it on c9 (don't have 9.1, have got the beta up to 9 i think) .. Different machine, so it's a bit of a hassle. I'll get to it.

MSSQL 2012.

[kevin plummer]

I'm on MS SQL2008 so it could be your sql version in combination with C10

[Stu]

This worries me.

So.

At the moment I'm just concerned with solving the immediate issue - getting the browse to work with related fields.

Am going to try changing the collation on my db .. But this worries me too. Makes me wonder what changed in clarion 10 latest version.

Okay, so I should post on the newsgroups.

The big thing is .. I still don't understand what the error means. What change in Clarion, or potentially what changed.

Anyway, will carry on.

Stu

[Poul]

I recall that the "SQL_" collations were used as the default around sql2005, and now mainly exist for backward compatibility
"Latin1_General_ ... " became the newer default standard and default based on Windows Regional settings.

Newer sql versions allowed collations at database and table levels etc.
Has your server been upgraded or data migrated/restored recently?
if you just accepted the defaults on a sql install it can be a different collation then the defaults from an older sql install.
(you can select the collation in setup, I used to makes sure it was the same as my older sql server, because i had an application
that explicitly complained if the systemdefault collation did not equal the collation of some views)

if you have had different collations in the past or introduced some in a new install,
you might want to watch where the systemdefault collation is being used for new objects you create
but you are comparing to older objects with different collations.

maybe this helps?
http://blog.sqlauthority.com/2007/06/11/sql-server-cannot-resolve-collation-conflict-for-equal-to-operation/

you can remove clarion from the equation if you can prove/illustrate the problem with sql profiler and query analyzer.

(it may be that C10 does something different,  but i would hope/prefer not) 

[Stu]

Thanks Poul, that's very helpful info to know.

And thanks for the link, will give that a look-see.

Cheers!

[Stu]

Okay, more to this saga.

1. This system, database A - Nettalk 8.51, C10 (latest), MSSQL 2014, FM3 (latest)
2. Another system, database B - Nettalk 8.51, C9 (last before gold), MSSQL 2012, Tables created by clarion (CREATE flag)

Both database A and B have the same collation.

This points me back to the latest C10 being the cause of the issue. Unless something else pops up.

I guess I'm going to need to enter the ticketing/fault system .. with trepidation.

Stu