FME collation setting

Hi @jaywen,

I’m assuming the database is set to Latin1_General_CI_AI?

I am wondering about this too… perhaps the client os system collation is being followed by default by FME… Maybe the SQLExecuter and using the COLLATE key word?

I was perusing this article: 
https://stackoverflow.com/questions/7210189/how-to-set-collation-for-a-connection-in-sql-server

There might be an easier route with SQL Server JDBC and setting a connection parameter but I’m not sure. 

With the MSSQL_ADO format there won’t be a way to do this with the Writer. 
Depending on your needs you may need to do this with custom SQL in the SQLExecuter and not the Writer.  

Hopefully someone else in the community has gone through this.  I will check with our Dev Team for any other insight.

If you create a table without specifying collation, it will use the database default, and this will be what FME is doing. Generally it will cause headaches if you mix collations. You could create the table on a database which uses the Latin1_General_CI_AI collation, or you can put in a SQLExecutor to alter the collation of the text columns in your newly created table (which may or may not affect the data, need to test)

ALTER TABLE YourTable
ALTER COLUMN YourColumn varchar(50) COLLATE Latin1_General_CI_AI NULL

Hi @jaywen 

Sharing some more notes on this.

From our Developers: This is one of a few things you might want to set immediately after you connect to a database and before you run other queries. You might be able to get some traction with "SQL To Run Before Read|Write", but there is a problem with that – depending on the readers, writers, and/or transformers in play, we don't promise that these nodes will share (or not share) the same connection to the database. That means we might use the collation in some cases but not others.
 

So I would tread carefully with the above… and ensure you test if you must go that route.

But as I shared earlier… you might have luck with the SQLExecuter… and setting this there when running the commands… not as slick or code free of course.  

Lastly there is a good chance that FME, using SQL Server driver would honour the collation of the local OS System… this may be impractical if you are expecting to write to many different collations.

I will file an issue to get this reconsidered again… long ago this was looked at but there was not enough interest and there was a lot of work to properly implement it.

Hi @steveatsafe @ctredinnick , 

Thanks for all your help. We just use work around solutions :)