Forum

Ask, reply and learn. Join the community of Akaunting.

New Discussion

SQL Server datetime conversion problem

Albert Van Der Ploeg   ( User )

Commented 3 years ago

Hi all. Hope anyone can help me with my issue.

I installed Akaunting on a Linux webserver (Ubuntu 18.04) and I want to connect Akaunting to a remote SQL Server.
I managed to install PDO_sqlsrv and got the connection working. However, when installing in step 2, it creates my database and tries to insert some default values, for the admin account.
The exception window shows a SQL Server error, which is:

SQLSTATE[22007]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. (SQL: insert into [mjf_roles] ([name], [display_name], [description], [updated_at], [created_at]) values (admin, Admin, Admin, 2021-01-23 12:35:47.099, 2021-01-23 12:35:47.099))

As far as I can see, dates are (without quotes here) presented as ISO values which should work fine. I tried to use a different language and different LCID but can't get it to work.
Why is this not working?

I could try and install it on Feb 2 for example which may work but will possibly lead to problems further down the year again.

Any solution on how to setup or configure my initial database?

Thnx Albert

Albert Van Der Ploeg   ( User )

Commented 3 years ago

Managed to fix this myself by changing the language of the database user to English.

New issue now is step 3 where default settings are inserted. This is done by an INSERT statement with multiple values. Between those values are character and numeric values.
SQL Server has the habit of trying to convert this to the type with the highest precedence before inserting, so character will be converted to int which obviously doesn't work.

SQLSTATE[22018]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Conversion failed when converting the nvarchar value 'USD' to data type int. (SQL: insert into [rtw_settings] ([company_id], [key], [value]) values (1, default.account, 1), (1, default.currency, USD), (1, default.locale, nl-NL), (1, invoice.title, Factuur), (1, wizard.completed, 0), (1, offline-payments.methods, [{"code":"offline-payments.cash.1","name":"Contant geld","customer":"0","order":"1","description":null},{"code":"offline-payments.bank_transfer.2","name":"Bankoverschrijving","customer":"0","order":"2","description":null}]), (1, company.name, MyCompanyName), (1, company.email, [email protected]), (1, company.address, ?))

So the values should be quoted, like in the above statement where wizard.completed is a numeric 0, which should be "0". In the above statement I see some values quoted, others not, this is leading to problems.

I have the feeling support for SQL Server is quite weak :(

Please login or register to leave a response.

Showing 1 to 2 of 2 discussions