ODBC/OLEDB via SSRS

[tfranklin]

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

[BevanWeiss]

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

[tfranklin]

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!

[sbeadle]

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

[tfranklin]

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.

[BevanWeiss]

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..

[tfranklin · Accepted solution]

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.