REXYGEN Community Forum

hubc

Member

Registered: 2016-09-05

Posts: 60

MS SQL database - archive TRND problem

Hello,

I see this error in logs:

DBDRV: Arc:ReadXxxItem() return=-106)

and nothing is saved in db table.

I tried to setup DbDrv to archive TRND object.
I can see values from TRND in Archive block - so I guess this connection is OK.

I tried another (not Archive) write to the database (MS SQL) and it is working well, so I assume connection to the db server is established.

Maybe something in my .rio file?

	Archive {
		Mode        2
		SQL		"TrndActivePower"
		ArchiveID	0
		Items	1201,1201
	}

TrndActivePower - is my table name, table has columns: Time (datetime), GroupID, Value1, Value2, Value3, Value4  (int)

ArchiveID: 0 - this is the first Archive block from the left, right? (if I change it to 1 nothing happens - no errors in logs, but also nothing in the db table)
Items: 1201 is my TRND id.

Hubert

stetina

REX developer

From: Pilsen, Czech Republic

Registered: 2015-10-29

Posts: 9

Re: MS SQL database - archive TRND problem

Hello Hubert,
your *.rio file seems correct. Message in your log means invalid parameter while DbDrv tries to read an archive item. This is usually due to incorrect start time. Some versions have a bug that cause this error when database table was empty or last item was older than the 1st archive item, but version 2.10.7 (and later) should be fixed. Anyway you can try putting a line into database table manualy.

Same error could also happens, when your database table contains too new items (e.g. with time in future).
You should also check datetime format - DbDrv use/require YYYY-MM-DD hh:mm:ss  (e.g. for example 2016-09-30 13:01:59), UTC timezone. If you have an empty table, this is not the current issue, but will be later.

Best regards,
Milan Stetina

hubc

Member

Registered: 2016-09-05

Posts: 60

Re: MS SQL database - archive TRND problem

Hello Milan,

thank you for explanation.
I purged my table and now having different error

DBDRV: SQL request failed (idx=0, result=-1, sql='INSERT INTO TrndActivePower (Time, GroupID, Value1, Value2, Value3) VALUES("2016-10-08 19:25:42.974191", 1201, 3995.87, 4629.94, 2529.4)')
DBDRV: SQL error detail (code=241, state='22008', string='[FreeTDS][SQL Server]Conversion failed when converting date and/or time from character string.')

this is conversion error, but I don't understand why? Datetime format is correct, in my table Time column is 'datetime' type - so what's going on?

AND I found solution!

regarding to that:
https://msdn.microsoft.com/en-us/library/ms186724.aspx

datetime type accuracy may be too small, so I changed column to datetime2

I'm posting it so maybe it will be useful for someone

Regards
Hubert

jaroslav_sobota

Administrator

Registered: 2015-10-27

Posts: 535

Re: MS SQL database - archive TRND problem

Hi Hubert,
this is extremely valuable information. Thank you for sharing it with the community!

Jaroslav

---

Monarco HAT for Raspberry Pi - Lightweight I/O for monitoring, archiving and control.
Raspberry Pi in industrial automation!