Help
  • Explore Community
  • Get Started
  • Ask the Community
  • How-To & Best Practices
  • Contact Support
Notifications
Login / Register
Community
Community
Notifications
close
  • Forums
  • Knowledge Center
  • Events & Webinars
  • Ideas
  • Blogs
Help
Help
  • Explore Community
  • Get Started
  • Ask the Community
  • How-To & Best Practices
  • Contact Support
Login / Register
Sustainability
Sustainability

Join our "Ask Me About" community webinar on May 20th at 9 AM CET and 5 PM CET to explore cybersecurity and monitoring for Data Center and edge IT. Learn about market trends, cutting-edge technologies, and best practices from industry experts.
Register and secure your Critical IT infrastructure

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.

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Show  only  | Search instead for 
Did you mean: 
  • Home
  • Schneider Electric Community
  • Knowledge Center
  • Geo SCADA Knowledge Base
  • Connect from SQLServer 2005 using Linked Servers
Invite a Co-worker
Send a co-worker an invite to the portal.Just enter their email address and we'll connect them to register. After joining, they will belong to the same company.
You have entered an invalid email address. Please re-enter the email address.
This co-worker has already been invited to the Exchange portal. Please invite another co-worker.
Please enter email address
Send Invite Cancel
Invitation Sent
Your invitation was sent.Thanks for sharing Exchange with your co-worker.
Send New Invite Close
Top Labels
Top Labels
  • Alphabetical
  • database 32
  • Web Server and Client 31
  • WebX 19
  • Request Form 18
  • Lists, Events & Alarms 16
  • ViewX 15
  • Application Programming 12
  • Setup 12
  • Telemetry 8
  • Events & Alarms 7
  • Lists 7
  • Mimic Graphics 7
  • Downloads 6
  • Support 5
  • IoT 5
  • SCADA 5
  • Geo SCADA Expert 5
  • Drivers and Communications 4
  • Security 4
  • DNP 3 3
  • IEC 61131-3 Logic 3
  • Trends and Historian 2
  • Virtual ViewX 2
  • Geo Scada 1
  • ClearSCADA 1
  • Templates and Instances 1
  • Releases 1
  • Maps and GIS 1
  • Mobile 1
  • Architectures 1
  • Tools & Resources 1
  • Privacy Policy 1
  • OPC-UA 1
  • Previous
  • 1 of 4
  • Next
Latest Blog Posts
  • OPC UA - Driver and Server
  • Requirements for Generating a Valid OPC UA Server Certificate
  • Load Events Using LoadRecord and LoadRecords
  • Geo SCADA Embedded Component Licenses
  • Geo SCADA 2023 Known Issues
Related Products
product field
Schneider Electric
EcoStruxure™ Geo SCADA Expert

Invite a Colleague

Found this content useful? Share it with a Colleague!

Invite a Colleague Invite
Anonymous user
Not applicable
‎2021-06-09 09:43 AM
1 Like
0
1488
  • Bookmark
  • Subscribe
  • Email to a Friend
  • Printer Friendly Page
  • Report Inappropriate Content

Link copied. Please paste this link to share this article on your social media post.

‎2021-06-09 09:43 AM

Connect from SQLServer 2005 using Linked Servers

Originally published on Geo SCADA Knowledge Base by Anonymous user | June 09, 2021 06:43 PM

📖 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.JPGSQLServer New Linked Server.JPGSQLServer 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.JPGProvider Options.JPGProvider 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

Biography

Anonymous user

Link copied. Please paste this link to share this article on your social media post.

  • Back to Blog
  • Newer Article
  • Older Article
To The Top!

Forums

  • APC UPS Data Center Backup Solutions
  • EcoStruxure IT
  • EcoStruxure Geo SCADA Expert
  • Metering & Power Quality
  • Schneider Electric Wiser

Knowledge Center

Events & webinars

Ideas

Blogs

Get Started

  • Ask the Community
  • Community Guidelines
  • Community User Guide
  • How-To & Best Practice
  • Experts Leaderboard
  • Contact Support
Brand-Logo
Subscribing is a smart move!
You can subscribe to this board after you log in or create your free account.
Forum-Icon

Create your free account or log in to subscribe to the board - and gain access to more than 10,000+ support articles along with insights from experts and peers.

Register today for FREE

Register Now

Already have an account? Login

Terms & Conditions Privacy Notice Change your Cookie Settings © 2025 Schneider Electric

This is a heading

With achievable small steps, users progress and continually feel satisfaction in task accomplishment.

Usetiful Onboarding Checklist remembers the progress of every user, allowing them to take bite-sized journeys and continue where they left.

of