Problem creating table for alarm logging with SQL Expess 2005

[antonisasim]

I won't to log alarm to a MS SQL Expess 2005 with DAQ and I get the following error.Could you help me? I have seen some other posts but they refair to logging not alarm logging.

I have created a DNS System Data Sourse with windows authentication.

C1021 Could not create table for alarm logging. Msg: Column, parameter, or variable #1: Cannot find data type DATE.

Thank you in advance!

[AzeoTech]

That is because MSSQL doesn't support the DATE field type supported by other databases (and surprisingly, DATE is supported by Microsoft Access, go figure!). For logging sets, you can change this, but not for alarming. To avoid this you'll need to create the alarmlog table yourself before starting DAQFactory to keep DAQFactory from having to create the table. The create SQL statement looks like this:

CREATE TABLE ALARMLOG (TheTime DATE, Name TEXT(100), Description TEXT(255), Priority SHORT, Status TEXT(1));

I'm not sure what you should use instead of DATE, you'll have to look in the MSSQL manual. The value that DAQFactory will insert will be a floating point number that is decimal days since 1900 (excel time format)

[antonisasim]

Thank you for your quick answer. But were should I put the code you wrote? In a sequence? Only this whould do the job (if I put the correct data types) ?

[antonisasim]

Do you mean to add the table with external code so that DAQF finds it created? I assume that DAQF won't try to create the table if it find it from the begining.

Thank you.

[AzeoTech]

Yes, I mean create the table externally so that DAQFactory doesn't have to create it. You just need to make sure and create it with the field names that I showed. The SQL statement I provided will work, you just need to change DATE to whatever MSSQL wants (REAL?) If the table already exists, DF won't try and create it, it will just try adding data to it.

Technically you could do it from DAQFactory using db.execute(), but I'd do it in the MSSQL environment.

[antonisasim]

Thank you very much for the help. After two days I managed to log the alarms in the SQL Express Database through ODBC. I created as you said the ALARMLOG table through SQL and I put into the table the columns you told me. Everythink works fine. If anyone want's to tell him the exact procedure for connecting DAQF with SQL Express 2005 (through ODBC) can ask for my help at my mail : antonis_asimakopoulos@yahoo.gr

Thank again for the help!

[antonisasim]

I have a new problem now. I read all the data I need from ALARMLOG but the "TheTime" column returns day-time with 2 days difference from the real.

e.g if my system(clock) time is

2010/05/20 12:23:34

I get from "TheTime" column the following

2010/05/22 12:23:34, two days difference.

So can you tell me the "TheTime" data are in Unix Format Time with offset 1970-01-01 ?

If so using the following command to the SQL Express Server do you think will help me ?

DATEADD(s, TheTime, '19700102') . (In order to change the offset)

[AzeoTech]

Yup. There is a bug in MSSQL that causes it to miscalculate dates and be off by 2 days. I have no idea why. You can send the exact same date value to Access (another MS product) and it works fine. Anyhow, please see section 9.8 in the help and the .TimeOffset variable of the logging set. With it you can apply an offset to all time values before logging to deal with the MSSQL bug. Note we've only heard of this problem in MSSQL. MySQL is fine, Access is fine, Excel is fine, etc.

[antonisasim]

Thank you for the help. I removed two days from the date and everything is OK. For anyone who is interested the MSSQL command is :

DATEADD(day, - 2, TheTime) , where "TheTime" is DAQFActory date-time. "Day" is MSSQL for days to add,"Month" for months etc.

[AzeoTech]

Great. As I said, you can correct for it a head of time from within DAQFactory, but thanks for posting the command to fix data already logged!

[antonisasim]

Somethink very strange now occurs. When I fire and reset an alarm DAQF writes it in the SQL database and everything is OK,but when I try to acknowledge it I get the following erron from DAQF :

06/10/10 14:39:39.280

C1066 Invalid operator - Error compiling sequence , Line Number 1

06/10/10 14:39:39.315

C1021 Could not create table for alarm logging. Msg: Column, parameter, or variable #1: Cannot find data type DATE.

Also strange is that some aknowledgements "go" to the SQL but some other don't (but I always get the previous error)

I have attached a print screen image for help. In this test I fire 4 alarms,then I ack them and then I reset them. You will see that although i ack all of them, in SQL i see only two of them aknowlenged.

Can you please help?

[AzeoTech]

First, you should disable the built in alarm logging so it stops trying to create the table it can't create.

Second, I'd need to see the script you put in the ack event. There is a typo somewhere.

[antonisasim]

I don't have any code written in "ack event" of the alarms. Should I write code there? In each alarm I have only the "Name", "Description" , "Alarm condition", "Reset condition".

[antonisasim]

Attached I have my test file.

Scada.ctl

[AzeoTech]

Sorry. I should have read the whole thread before responding. Ignore my last post about events and logging. The problem is that you do have code in ack event of all your alarms. The code is:

CH5==1

This is not a valid statement. If you want to assign 1 to CH5 when acked, you want:

CH5=1

[antonisasim]

Sorry I was stupid! I have forgotten code on ack event. I delete it but I still have the following

06/10/10 14:39:39.315

C1021 Could not create table for alarm logging. Msg: Column, parameter, or variable #1: Cannot find data type DATE.

Scada.ctl

[AzeoTech]

We're back to the original problem then. Do you have a table called ALARMLOG in your database? Presumably so. The issue then must be that the insert is failing. At first, if the insert fails, it assumes its because the table doesn't exist, and so creates the table. But this is failing, thus the error message. What is the format of your table? Is the log entry getting into the table?

[antonisasim]

From my previous post you can see the attached image. There you can see that the alarms( fired and reseted state) are stored without problem in the SQL table. It is very strange that the ack cannot be stored.

The SQL table is OK I think.

[AzeoTech]

That is strange since the only difference is that it writes an "A" instead of "F" or "R". So, do you see an "A" record in the table?

[antonisasim]

Yes I can see "A" in the table (see image). The strange think is that some times appear from the four alarms two aknowledgements (as in image) some other three aknowledgements etc. It doesn't have a pattern on how it reacts I think. Can you please try to emulate in your PC with MSSQL ?( it would be very helpfull for me, I am trying to get it work for days now!)

Thank you very much in advance. You have helped me a lot untill now!

[AzeoTech]

Its only going to log a line for alarms that have actually fired.