Subscribing is a smart move!
You can subscribe to this forum after you log in or create your free account.
Connect from SQLServer 2005 using Linked Servers
Geo SCADA Knowledge Base
Access vast amounts of technical know-how and pro tips from our community of Geo SCADA experts.
Invite a Co-worker
Send a co-worker an invite to the Exchange portal.Just enter their email address and we?ll connect them to register. After joining, they will belong to the same company.
Send Invite Cancel
📖 Home Back
It is possible to make a direct connection to ClearSCADA from SQLServer 2005 using Linked Servers. The linked server functionality in SQLServer will connect using the OLEDB Provider for ODBC.
The effect of a linked server is that the ClearSCADA database can essentially be directly queried through SQLServer with only a minor change to the structure of the query.
SQLServer New Linked Server.JPG
* The Provider should be "Microsoft OLE DB Provider for ODBC Drivers" so that SQLServer can connect using the ClearSCADA ODBC interface.
If you intend to perform updates on ClearSCADA tables, enable "Non Transact Updates" on the Linked Server Provider:
Provider Options.JPG
Should you fail to enable "Non transacted updates" and attempt to perform an UPDATE on a table, the following error message appears:"Msg 7390, Level 16, State 1, Line 1 The requested operation could not be performed because the OLE DB provider 'MSDASQL' does not support the required transaction interface."
Note that the use of "CLEARSCADA" tells SQLServer to use the linked server connection we created. The rest of the table reference defines the location of the table within the ClearSCADA database structure. In the above example, the CDBPoint table is within the "Core" group and so the reference must include both Core and CDBPoint.
ODBC Table Owners is a new feature in ClearSCADA 2009 R1. Prior to this version, the query table reference would be CLEARSCADA...CDBPoint i.e. the table owner is left out of the table reference in the query.
Due to an issue with the SQL Server query optimizer, certain types of queries run in SQLServer against a linked server are consider non-remoteable. This means that SQLServer will ignore parts of the WHERE clause of the query and process the result set locally. One such case of this occuring is with the use of datetime fields in ClearSCADA.
The queries below highlight this problem.
is actually passed through to ClearSCADA as:
Note that the RecordTime part of the WHERE clause has been removed. In this case SQLServer has decided that the RecordTime part of this query is "non-remoteable" and so it issues a query which expects to get ALL records for point ID 124 from CDBHistoric.
Since CDBHistoric could contain billions of records, this type of query will either be rejected as unconstrained, or take a very long time to run as all data would need to be passed to SQLServer for processing one record at a time. At best it would be grossly inefficient, at worst it would not work at all
Microsoft describe this as a "quirk" of the query processor!
There is however a couple of solutions to this problem.
OPENQUERY provides a way where an entire query can be sent through to a remote linked server. The query executed in SQLServer would look something like:
Note the added complexity of the query and the two single quotes around the time. Some customers do not like this additional complexity added by the "quirk" in SQLServer.
Another approach is to use a variable to hold the datetime value and pass the variable in the WHERE clause rather than the actual datetime value. See the example below:
Go: Home Back
It is possible to make a direct connection to ClearSCADA from SQLServer 2005 using Linked Servers. The linked server functionality in SQLServer will connect using the OLEDB Provider for ODBC.
The effect of a linked server is that the ClearSCADA database can essentially be directly queried through SQLServer with only a minor change to the structure of the query.
Making the Connection
- From within SQLServer Management Studio, open the Linked Servers section in the Object Explorer.
- Right-click on the Linked Servers line, and select "New Linked Server..."
SQLServer New Linked Server.JPG* The Provider should be "Microsoft OLE DB Provider for ODBC Drivers" so that SQLServer can connect using the ClearSCADA ODBC interface.
- The product name should match the name of the ODBC driver - in this case "Control Microsystems ClearSCADA Driver".
- The Data source is the name of the System DSN configured to connect to ClearSCADA.
Allowing SQLServer to Write to ClearSCADA Tables
If you intend to perform updates on ClearSCADA tables, enable "Non Transact Updates" on the Linked Server Provider:
- From within SQLServer Management Studio, open the Linked Servers/Providers section in the Object Explorer.
- Double click on "MSDASQL"
Provider Options.JPG
- Enable "Non transacted updates" by checking its check box.
Should you fail to enable "Non transacted updates" and attempt to perform an UPDATE on a table, the following error message appears:"Msg 7390, Level 16, State 1, Line 1 The requested operation could not be performed because the OLE DB provider 'MSDASQL' does not support the required transaction interface."
Running Queries on ClearSCADA
- Open a query window within SQLServer Management Studio - look for the "New Query" option in the toolbars.
SELECT FullName FROM CLEARSCADA..Core.CDBPoint
Note that the use of "CLEARSCADA" tells SQLServer to use the linked server connection we created. The rest of the table reference defines the location of the table within the ClearSCADA database structure. In the above example, the CDBPoint table is within the "Core" group and so the reference must include both Core and CDBPoint.
- The Level zero only option available in the properties of the MSDASQL provider turns on and off the SQLServer support for using ClearSCADA database schemas. So to reference CDBHistoric in the form of CS...CDBHistoric rather than using CS..Historic.CDBHistoric (or other schema name), enable the Level zero only option.
ODBC Table Owners is a new feature in ClearSCADA 2009 R1. Prior to this version, the query table reference would be CLEARSCADA...CDBPoint i.e. the table owner is left out of the table reference in the query.
- Click on the Execute button on the toolbar and the data set should be retrieved from ClearSCADA.
Writing Queries Filtering on a DateTime Field
Due to an issue with the SQL Server query optimizer, certain types of queries run in SQLServer against a linked server are consider non-remoteable. This means that SQLServer will ignore parts of the WHERE clause of the query and process the result set locally. One such case of this occuring is with the use of datetime fields in ClearSCADA.
The queries below highlight this problem.
SELECT TOP( 100000 ) "RecordTime", "FormattedValue", "StateDesc"FROM CS1..Historic.CDBHistoricWHERE "Id" = 124 AND "RecordTime" > { ts '2008-06-02 00:00:00' }ORDER BY "RecordTime" DESC
is actually passed through to ClearSCADA as:
SELECT TOP( 100000 ) "RecordTime", "FormattedValue", "StateDesc"FROM CS..Historic.CDBHistoricWHERE "Id" = 124ORDER BY "RecordTime" DESC
Note that the RecordTime part of the WHERE clause has been removed. In this case SQLServer has decided that the RecordTime part of this query is "non-remoteable" and so it issues a query which expects to get ALL records for point ID 124 from CDBHistoric.
Since CDBHistoric could contain billions of records, this type of query will either be rejected as unconstrained, or take a very long time to run as all data would need to be passed to SQLServer for processing one record at a time. At best it would be grossly inefficient, at worst it would not work at all
Microsoft describe this as a "quirk" of the query processor!
There is however a couple of solutions to this problem.
Use OPENQUERY
OPENQUERY provides a way where an entire query can be sent through to a remote linked server. The query executed in SQLServer would look something like:
SELECT * FROM OPENQUERY(CS, 'SELECT TOP( 100000 ) RecordTime, FormattedValue, StateDescFROM CDBHistoricWHERE Id = 2133 AND RecordTime > { ts ''2008-06-02 00:00:00'' }ORDER BY RecordTime DESC')
Note the added complexity of the query and the two single quotes around the time. Some customers do not like this additional complexity added by the "quirk" in SQLServer.
Use a Variable to Hold the DateTime Value
Another approach is to use a variable to hold the datetime value and pass the variable in the WHERE clause rather than the actual datetime value. See the example below:
declare @x as datetimeset @x = convert(datetime , '2008-06-02 00:00:00')select Id, RecordTime, StateDesc from alpha..Historic.CDBHistoric where RecordTime > @x
Go: Home Back
Author
Create your free account or log in to subscribe to the forum - and gain access to more than 10,000+ support articles along with insights from experts and peers.