[Imported] ClearSCADA / SCADAPack 50 Gateway - ODBC Connection

[sbeadle]

>>Message imported from previous forum - Category:ClearSCADA Software<<
User: florian, originally posted: 2018-10-25 16:47:23 Id:244
This is a re-posting from the obsoleted (October 2018) "Schneider Electric Telemetry & SCADA" forum.

-------------------------------

**_HenkSmit:_**
**_Good day, I hope someone can help._**

**_I have ClearSCADA as a SCADAPack GatePack gateway with a dongle license. I have "SCADA Expert ClearSCADA 2015 R2" with SQL Server 2008 Express._**

**_I need to get the data into a SQL Database on the same machine._**

**_First Question: Can ODBC Connections work on the Dongle license or on the trial (2 hour) license that I am still using for development?_**

**_When I try to connect I do see the ClearSCADA ODBC object and from MS Access I can connect to it. When I try to query the CDBHistoric table I get an error syaing "Historic query not constrained by Object, or could not be optimised efficiently."_**

**_When I try to connect a linker server in MS Sql, I an error saying that "OLE DB Provider MSDASQL for Linker server reported an error. Cannot initialise the data source"_**

-----------------

sbeadle:
Regarding "Historic query not constrained by Object, or could not be optimised efficiently."

The database will not allow queries of historic data to be unconstrained (i.e. without a suitable WHERE clause). Otherwise it could be a query for billions of records. You always need to add a condition to look for specific ID numbers in the table. (You don't have to search for specific IDs, just join the table to CDBPoint and search that by point name).

Also you should always add a condition to constrain by date/time.

To see an example, just right-click a point with history, choose 'Display Historic List' and then view the SQL for that query on the context menu.

As for OLE DB - probably a connection string issue.

------------------------

sbeadle:
Here's how to JOIN:

_SELECT TOP( 1000000 )_
_"RecordId", "RecordTime"_ _AS "~Time", "FormattedValue", "StateDesc", "QualityDesc", "ReasonDesc", "StatusDesc", "SuppressionTypeDesc",_ _H."Foreground", H."Background", H."Blink", "FileOffset" AS "_FileOffset"_

_FROM_
_CDBPOINT AS P JOIN CDBHISTORIC AS H USING ( ID )_
_WHERE_
_"FullName" = 'Demo Items.Historic Demo.AI1' AND "~Time" BETWEEN { OPC 'H - 23H' } AND { OPC 'H - 23H+1D' }_
_ORDER BY_
_"~Time" DESC, "_FileOffset" DESC_

----------------

**_HenkSmit:_**
**_When I link MS Access to the ODBC ClearSCADA data source and query the CDBObject table for instance, there is no data in the table. I do not get queries, the table just seems to be empty. When I query the table from within ViewX, there is data, so the problem seems to be with the ODBC connection._**

**_To try and resolve it I have uninstalled the ClearSCADA data components en re-installed it. I have then re-created the ODBC data source according to a Schneider Instructional video._**

**_I do have one question on this, under the security options in the data source the video suggests you can leave the username and passsword as blank or enter values if the security has been setup like that in ClearSCADA. Where do you configure this username and password? How do I know if I can leave it as blank?_**

**_I am hoping that by resolving this I would also resolve my SQL Server Linked Server problem._**

---------------------

**_HenkSmit:_**
**_Hi All_**

**_Just a quick follow-up. I have played around with the security settings in ViewX. I have created a new user with ALL rights and I have specified that user with his password in the System DSN. I know that is not the recommended way._**

**_Well, I can now run queries in Access and get the data back._**