Custom SQL Error (Report Writer) on Account Limitation

Hi,

I have a problem about showing custom sql report created report writer. There is Nodes.Department limitation on account. That below sql query correctly runs at SSMS/Database Manager and Report Writer. But if I save this report on Report Writer and check this report via limited account, I get an error like 'The multi-part identifier "Nodes.Department" coul not be bound.'.

Can you help me about that problem?

Edit: The version is NPM 11.5.2

WITH tbl (NodeID)AS (  SELECT nodelist.NodeID FROM (  SELECT InterfaceID, NodeID FROM interfaces WITH (NOLOCK) WHERE InterfaceName LIKE '%GigabitEthernet0/1.%'   UNION   SELECT InterfaceID, NodeID   FROM interfaces WITH (NOLOCK) WHERE InterfaceName LIKE '%GigabitEthernet0/1%'  AND NodeID NOT IN (   SELECT NodeID  FROM interfaces WITH (NOLOCK)   WHERE InterfaceName LIKE '%GigabitEthernet0/1.%'  )) intf , (  SELECT NodeID FROM nodes WITH (NOLOCK) WHERE Nodes.Device_Type = 'MPLS_Router'   ) nodelist  WHERE intf.NodeID = nodelist.NodeID   )
SELECT count(*) AS [COUNTOF]FROM tblINNER JOIN nodes ON nodes.NodeID = tbl.NodeIDWHERE Nodes.Device_Type = 'MPLS_Router'

Find more posts tagged with

report_writer

account_limitations

custom_sql_report

Accepted answers

All comments

mwb

Any solution here? I know its a conflict between the account limitation and the SQL query, but I'm at a loss as to where its actually checking or how/why.

zackm

IIRC rob.hock‌ had mentioned in a thread once that running custom SQL reports required the user to have administrator rights due to a risk of allowing non-admins to run SQL against the DB. (or something like that)

EDITED TO ADD: Re: Am I missing something or does Web Reporting require admin privledges for SQL reports?

Have you tried an account with Administrator rights, that still has the account limitation?

Also, with the web report writer, I haven't tested using CTEs yet. Have you attempted this query using subqueries instead? (I know it's not as effective from the SQL perspective, but it's worth the troubleshooting step)

Just some ideas. Hope it get's you in the right direction.

-ZackM

Loop1 Systems: SolarWinds Training and Professional Services

LinkedIN: Loop1 Systems
Facebook: Loop1 Systems
Twitter: @Loop1Systems

juniordev

Hi mwb‌

There are two solutions.

If you create "Web Report" with same SQL query, you can pass over the limitation problem.
If you write query with CTE (not include "WHERE" and some pattern that I couldn't exactly work out) on Report Writer, you can pass over the limitation problem.

So I choose the number 1 and the problem is over.