I have a complex query that uses a reference to a linked server and database. I successfully use the query in standard table object on a display.
I’m trying now to make a report using the same query and am having problems.
The query is in the form:
EXEC(’ use Database; long/complex query code . . . ’) at [LinkedServer]
If I hard-code real names for Database and LinkedServer, the report works fine. But that’s not an option when deploying the report.
In the original query, I use client tags to substitute Database and LinkedServer.
My 1st try in Reports was to use a SQL Query for the data and use parameters for Database and LinkedServer. The system would recognized it and would create a parameter input when I replaced Database with a question mark. It would never recognize and create a parameter input when I replaced LinkedServer with a question mark.
So, next I tried using a Basic SQL Query for the data, because it supports references directly in the query using the brace characters { }.
Using the Basic SQL Query, I copied in the exact query (with the curly-braced tag references) into the query definition. I don’t get any data in the preview and get the following error: WARN: Simple Query could not be completed. Could not find server ‘null’ in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.
Is there a particular method I need to use to be able to reference a linked server and database in the Report module?
That's the root of the problem. Reports execute on the gateway - so while using a Basic SQL Query is the correct way to specify your dynamic query, you cannot use client tags as the source of the values. Use parameters, and wherever you're calling the query from, either manually specify the values, or use "real" tags to store persistent parameter values.
I can’t use the 2nd method I tried, which leaves me with my original problem of how do I get it to recognize the 2nd question mark when I create the query so that this:
EXEC(’ use Database;
long/complex query code . .
’) at [LinkedServer]
becomes this:
EXEC(’ use ?;
long/complex query code . . .
’) at [?]
You can’t. Parameterized statements are strictly defined (to prevent SQL injection, for one reason) and so you can’t replace the ‘structure’ of a query with a parameter. Using a ‘Basic SQL Query’ is the right way to do this; I suspect the warning you were getting about null being invalid is because of the client tag issue. Try the same approach with real system level tags, even with fixed values, to see if you get the same behavior.
This is a shared enterprise application, whose information is made specific to a user at a particular location with client tags. I don’t have a connection to each database directly, I have a connection to a central server that has linked servers to each location’s database. if I can’t use client tags, then multiple concurrent users would step on each other’s reports.
It sounds like to me that without making individual reports for each location, I’m not able to use reports in this application.
Where are you executing these reports from? You can pass whatever values you want to use as report parameters via scripting function https://docs.inductiveautomation.com/display/DOC80/system.report.executeAndDistribute. If you need to run a scheduled report, then you could maintain a mapping of usernames : databases and run through that list, passing them through the scripting function.