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

ODBC/OLEDB via SSRS

EcoStruxure Geo SCADA Expert Forum

Schneider Electric support forum about installation, configuration, integration and troubleshooting of EcoStruxure Geo SCADA Expert (ClearSCADA, ViewX, WebX).

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
  • Remote Operations
  • EcoStruxure Geo SCADA Expert Forum
  • ODBC/OLEDB via SSRS
Options
  • Subscribe to RSS Feed
  • Mark Topic as New
  • Mark Topic as Read
  • Float this Topic for Current User
  • Bookmark
  • Subscribe
  • Mute
  • Printer Friendly Page
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 Experts
User Count
sbeadle
Kirk sbeadle Kirk
307
AndrewScott
Admiral AndrewScott
96
BevanWeiss
Spock BevanWeiss
90
AdamWoodlandToo
Lt. Commander AdamWoodlandToo
36
View All
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
Solved Go to Solution
Back to EcoStruxure Geo SCADA Expert Forum
Solved
tfranklin
Commander tfranklin
Commander

Posted: ‎2020-02-25 06:45 AM . Last Modified: ‎2023-05-03 12:16 AM

0 Likes
6
5288
  • Mark as New
  • Bookmark
  • Subscribe
  • Mute
  • Subscribe to RSS Feed
  • Permalink
  • Print
  • Email to a Friend
  • Report Inappropriate Content

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

Posted: ‎2020-02-25 06:45 AM . Last Modified: ‎2023-05-03 12:16 AM

ODBC/OLEDB via SSRS

Has anybody successfully implemented a query or report using SSRS against a Geo SCADA system over ODBC/OLE DB?

 

I was able to get SQL/SSRS to query data out of the system with no issues, but the moment I add in a parameter to constrain the data it no longer works.  I've successfully implemented an identical report while querying a SQL table and an access database with the same process.

 

Sample query can be something very basic, such as: 

SELECT ID,FULLNAME,NAME FROM CDBPOINT WHERE NAME = @Name

 

@Name would be a parameter derived in the report where the user enters in the parameter via text box or drop down.

 

When this hits Geo SCADA, the DB logs show the query exactly as written above, with the @ in the where clause.  I have a table in SQL with the same data called SQL_POINT_TEST and swapping out the table name yields the expected results.  This tells me it's likely something to do with how the driver for Geo SCADA is interpreting the request, but I'm not sure where to begin troubleshooting it.  One side of my brain says it should work since Crystal can take in parameters, the other side of my brain is saying maybe Crystal only works because there's some native integration with it. 

 

 

What I'll tack onto this is that I was able to run the following in SQL and it worked.  It looks like it might just be a SSRS issue... continuing to investigate...

 

declare @ptname varchar(25)
set @ptname = 'Flow Rate'
SELECT ID,FULLNAME,[NAME] FROM CSODBC..Kernel.CDBObject where NAME = @ptname
Labels
  • Labels:
  • Drivers
  • Tags:
  • odbc
  • oledb
  • parameters
  • Queries
  • ssrs
Reply

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

  • All forum topics
  • Previous Topic
  • Next Topic

Accepted Solutions
tfranklin
Commander tfranklin
Commander

Posted: ‎2020-06-12 09:58 AM . Last Modified: ‎2020-06-12 10:00 AM

0 Likes
0
4769
  • Mark as New
  • Bookmark
  • Subscribe
  • Mute
  • Subscribe to RSS Feed
  • Permalink
  • Print
  • Email to a Friend
  • Report Inappropriate Content

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

Posted: ‎2020-06-12 09:58 AM . Last Modified: ‎2020-06-12 10:00 AM

Just thought I'd post an update as we look to have found a solution.  Hopefully this helps somebody if they ever get stuck in a pickle like we were.

 

A colleague of mine was able to get this working by configuring the SSRS report to run a function that returned the query string syntax.  Functions are defined in the code section of a report in SSRS and you can configure the functions to accept parameters (as driven by the report).  When you define the query, configure it as an expression that just returns the results of the function -- which returns the string for the query syntax.  The code in SSRS is VB.NET so it should be pretty familiar and allows for easy formatting of dates/times for passing into reports and such. 

 

I was able to successfully test and implement a few reports that accept parameters for strings, int, and datetime.  Looks like this is the route we'll be taking in the meantime. 

See Answer In Context

Reply

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

Replies 6
BevanWeiss
Spock BevanWeiss
Spock

Posted: ‎2020-02-25 11:12 AM

1 Like
4
5281
  • Mark as New
  • Bookmark
  • Subscribe
  • Mute
  • Subscribe to RSS Feed
  • Permalink
  • Print
  • Email to a Friend
  • Report Inappropriate Content

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

Posted: ‎2020-02-25 11:12 AM

I believe this is an issue with SSRS (MS SQL) doing some advanced functions to ClearSCADA that it just can't handle.

i.e. I don't believe ClearSCADA handles named parameters.  Which sounds like the issue here.

 

I think there are a few ways to handle it, but from memory the 'easiest' was to essentially unroll the query at the MS SQL end, and wrap it up in an OPEN_QUERY 'thing'.  I forget the full details now, but that should give you 'a' direction.

 

I think there even used to be information on this on the old Resource Centre.

A google shows a link that goes against my recommendation above... but it might help still

https://www.clipsal.com/faq/detail?ID=FA280096

 

@sbeadle might have a bit more precise info


Lead Control Systems Engineer for Alliance Automation (VIC).
All opinions are my own and do not represent the opinions or policies of my employer, or of my cat..
Reply

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

tfranklin
Commander tfranklin
Commander

Posted: ‎2020-02-25 04:31 PM

In response to BevanWeiss
0 Likes
3
5273
  • Mark as New
  • Bookmark
  • Subscribe
  • Mute
  • Subscribe to RSS Feed
  • Permalink
  • Print
  • Email to a Friend
  • Report Inappropriate Content

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

Posted: ‎2020-02-25 04:31 PM

Definitely looks to be an issue with the named parameters. I tried the open query syntax and had no luck.  Open query works but still not with named parameters in the report.

 

SELECT * FROM OPENQUERY(CSODBC,"SELECT ID,FULLNAME,NAME FROM CDBPOINT WHERE NAME = @ptname")

 

I'm still struggling to understand why I can create a stored procedure in SQL with the parameter and it works, but when used in SSRS it barks.  Likely as you suggested -- it's potentially an issues with SSRS/MSSQL doing some fancy stuff. 

 

What I put in place just to force it to work for now is an unconstrained query then used the parameters in the result filter.  This works but obviously isn't ideal, especially if you need a report based on historical/record time. 

 

@sbeadlehelp me Obi Wan.  You're my only hope!

Reply

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

sbeadle
Kirk sbeadle Kirk
Kirk

Posted: ‎2020-02-26 04:09 AM

In response to tfranklin
0 Likes
0
5262
  • Mark as New
  • Bookmark
  • Subscribe
  • Mute
  • Subscribe to RSS Feed
  • Permalink
  • Print
  • Email to a Friend
  • Report Inappropriate Content

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

Posted: ‎2020-02-26 04:09 AM

OK, please can you raise this through the support channel and it will go into the system and be investigated up the chain. I'll keep an eye on it as it gets through.

Thanks

Steve

 

Reply

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

tfranklin
Commander tfranklin
Commander

Posted: ‎2020-02-26 07:57 AM

In response to tfranklin
0 Likes
1
5250
  • Mark as New
  • Bookmark
  • Subscribe
  • Mute
  • Subscribe to RSS Feed
  • Permalink
  • Print
  • Email to a Friend
  • Report Inappropriate Content

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

Posted: ‎2020-02-26 07:57 AM

Turns out open query failed because open query doesn't natively support parameters in the query.  You can declare variables ahead of time and use them in the syntax but things get pretty hairy quickly. 

 

I'll do some additional testing and capture some logs then send to support if I can't figure it out.

Reply

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

BevanWeiss
Spock BevanWeiss
Spock

Posted: ‎2020-02-27 05:37 AM

In response to tfranklin
0 Likes
0
5233
  • Mark as New
  • Bookmark
  • Subscribe
  • Mute
  • Subscribe to RSS Feed
  • Permalink
  • Print
  • Email to a Friend
  • Report Inappropriate Content

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

Posted: ‎2020-02-27 05:37 AM

Have you tried dropping the variable out 'in' the OPENQUERY call?

so instead of:

SELECT * FROM OPENQUERY(CSODBC,"SELECT ID,FULLNAME,NAME FROM CDBPOINT WHERE NAME = @ptname")

do

SELECT * FROM OPENQUERY(CSODBC,"SELECT ID,FULLNAME,NAME FROM CDBPOINT WHERE NAME = '" + @ptname + "'")

 

Not 'ideal' since you're losing what could be nice type safety of parameterised queries.  But you're at least able to get your full query text into the hands of ClearSCADA.

I'd also be a lot less worried about ClearSCADA and non-parameterised queries.  Obviously the Query Processor of ClearSCADA has some real limitations which improve the cyber security resilience of it.

Really limited opportunity to do SQL injection, since it only supports single action queries.

 

 

If the original query did work all fine in a stored procedure (with the parameter passing etc) then you could try to trick it by using something like a Table Valued Function (or whatever it's called... where you can return a table... pretty much like a View, but as a Function.. I wouldn't bother trying a View, with SQL it does a similar thing were it gets tricky on the filter clause passthrough).  There must be some ways to turn off some of the optimisation stuff in SQL Server, but I haven't found a way..


Lead Control Systems Engineer for Alliance Automation (VIC).
All opinions are my own and do not represent the opinions or policies of my employer, or of my cat..
Reply

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

tfranklin
Commander tfranklin
Commander

Posted: ‎2020-06-12 09:58 AM . Last Modified: ‎2020-06-12 10:00 AM

0 Likes
0
4770
  • Mark as New
  • Bookmark
  • Subscribe
  • Mute
  • Subscribe to RSS Feed
  • Permalink
  • Print
  • Email to a Friend
  • Report Inappropriate Content

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

Posted: ‎2020-06-12 09:58 AM . Last Modified: ‎2020-06-12 10:00 AM

Just thought I'd post an update as we look to have found a solution.  Hopefully this helps somebody if they ever get stuck in a pickle like we were.

 

A colleague of mine was able to get this working by configuring the SSRS report to run a function that returned the query string syntax.  Functions are defined in the code section of a report in SSRS and you can configure the functions to accept parameters (as driven by the report).  When you define the query, configure it as an expression that just returns the results of the function -- which returns the string for the query syntax.  The code in SSRS is VB.NET so it should be pretty familiar and allows for easy formatting of dates/times for passing into reports and such. 

 

I was able to successfully test and implement a few reports that accept parameters for strings, int, and datetime.  Looks like this is the route we'll be taking in the meantime. 

Reply

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

Preview Exit Preview

never-displayed

You must be signed in to add attachments

never-displayed

 
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