XData and Azure SQL

[cluff_david (David Cluff)]

My project is using XData, Aurelius and Azure VM with access to Azure SQL server via a Private Link. When I run my XData server on my local computer, everything works fine (it is accessing the Azure SQL server). When I run my XData server in an Azure VM, everything works (creates the data table, can list records from the data table) except when inserting a record I receive an error:

raised exception class EXDataGetRequestException with message 'XData server request error.
Uri: http://x.x.x.x/sam/Store(xxxx)
Status Code: 500
Error Code: AureliusOdbcException
Error -1: [Microsoft][ODBC SQL Server Driver]Invalid precision value'.

The AureliusConnection is using the native driver for MSSQL. Do I need to install something, like a driver, in the Azure VM?

At first I thought this had something to do with some of the fields in my table, like nvarchar(max). I changed all of those to nvarchar(100) and I still receive the same error.

[Farias_Anderson (Anderson Farias)]

Hi,

This seems to be an ODBC issue.

1. Check ODBC versions installed between your local computer and the Azure VM
2. Try setting the correct size of your varchar fields in your Aurelius mappings

HTH,

[cluff_david (David Cluff)]

I have not installed (and am not aware of) ODBC anything on my local computer or in the Azure VM. I was surprised to the "ODBC" in the error message.

In the connection setup, should the parameters called MARS, OdbcAdvanced need to have values?

I have several memo fields in my table. For test purposes I have reduced the size down to 100 characters (Unicode). With the same issues. I'm sure the answer is a simple one.

I think I'll try switching the AureliusConnection to use an Adapter to see if that works. Or maybe switch DBs to ElevateDB (I already know that works). I was just hoping to use Azure SQL for this project.

[Farias_Anderson (Anderson Farias)]

Hi David,

Well, you stated it works when running from your Dev machine so, not a problem using Aurelius with Azure SQL.

This error is usually reported for one of 2 reasons: trying to insert on varchar(max) fields with older ODBC driver versions that do not support it or while using newer ODBC versions, do not correcly set the size property (length) of varchar fields.

I guess the Aurelius MS Sql Native Driver is tested against some ODBC versions. You may find usefull to check this other thread with a similar problem (issues with ODBC):

MSSQL Invalid precision value

Hi, I face an EAureliusOdbcException with message 'Error -1: [Microsoft][ODBC Driver 11 for SQL Server]Invalid precision value' on a field that is declared as Nullable<Double> on the model and as float at SQL Server (version 2014). I also tried as Decimal(5,2) but the same happens. I face the error on every number I insert, even 0. What I miss here?

Sorry I don't have a more straightforward solution. TMS support you certainly point you in the right direction.

Regards,

[cluff_david (David Cluff)]

I switched it to use an "adapter" rather than "native" for the Azure SQL. That appears to work perfectly. So for now I will continue with that.

I'm not sure how to play with the ODBC driver versions when doing the native thing. That will need to be a future discovery.

Thank you for help with this.

[wlandgraf (Wagner Landgraf)]

Even though "ODBC" is mentioned, it's the SQL Server native client that is being used. Which happens to be the ODBC one. It's just a confusing Microsoft nomenclature.

My suggestion is that you simply install the latest SQL Server native client libraries in your server. That should solve the issue.