Receiving an error when doing a DirectQuery

[EZimmet]

Receiving an error when doing a DirectQuery 

The code below works fine on the RDC / SQL Server Management Studio

I'm inserting the same code into Power BI and receiving an error

DECLARE @SiteIDs bigintcollection

insert into @SiteIDs

values (21),(5)

select (select siteID from [NZ.Node.Nodes] where ID=p.NodeID) AS SiteID,P.NodeID,P.[Date],MAX_PWR,MIN_PWR,AVG_PWR

from [NZ.DW.Node.CapIntel] P

where NodeID in (Select ID from [NZ.Node.Nodes]

WHERE SiteID in (select ID from @SiteIDs))

order by P.[Date], P.NodeID

After connecting to the sql server in Power BI,  I see a preview of the data, all looks good

When doing the apply I’m receiving the following error - If someone can assist with the code correction - TY 

[v-saisrao-msft · Accepted solution]

Hi @EZimmet  
Thanks for Posting your query with Microsoft forum Community. 
 

As per your query Receiving an error when doing a Direct Query. In Power BI's Direct Query mode, SQL commands such as DECLARE and INSERT INTO are not supported because Direct Query is designed to pass queries directly to the underlying database. It retrieves data dynamically, and its focus is on executing read-only SELECT statements to pull data into Power BI for visualization. 
 
I've taken a sample dataset and performed the code you have given in direct Query and got the same syntax error. 
 

 
 Based on the code you provided, it functions as expected when executed in IMPORT MODE. The query successfully inserts values into the @SiteIDs collection and retrieves the desired data from the [NZ.DW.Node.CapIntel] and [NZ.Node.Nodes] tables, returning the results ordered by Date and NodeID.
 
DECLARE @SiteIDs bigintcollection 

insert into @SiteIDs 

values (21),(5) 

select (select siteID from [NZ.Node.Nodes] where ID=p.NodeID) AS SiteID,P.NodeID,P.[Date],MAX_PWR,MIN_PWR,AVG_PWR 

from [NZ.DW.Node.CapIntel] P 

where NodeID in (Select ID from [NZ.Node.Nodes] 

WHERE SiteID in (select ID from @SiteIDs)) 

order by P.[Date], P.NodeID 

Please refer to the screenshot below as it worked in IMPORT MODE as expected by using the sample data. 

If this post has helped you, accept it as the correct solution so other members can find it quickly. 

Hope this helps! 

Thanks. 

[EZimmet]

Thank you for the research will keep this in mind but for this import need to do a Query.

The table I'm reading is loading 7,359 records per 3 min looking at about 70,646,400 records per day  

[Ritaf1983]

Hi @EZimmet 

The issue here is that Power BI does not allow the use of DECLARE, INSERT INTO, or temporary table-like constructs in DirectQuery mode. Power BI expects a single query to return the dataset without any intermediate steps or variables.

Here’s how you can adjust the SQL query to work in Power BI:

Solution:
You need to rewrite the query to avoid the use of DECLARE and INSERT INTO. Instead, use a Common Table Expression (CTE) or a subquery to achieve the same result.

Here’s a rewritten query using a CTE:

sql
Copy code
WITH SiteIDs AS (
SELECT 21 AS SiteID
UNION ALL
SELECT 5 AS SiteID
)
SELECT
(SELECT SiteID FROM [NZ.Node.Nodes] WHERE ID = P.NodeID) AS SiteID,
P.NodeID,
P.[Date],
P.MAX_PWR,
P.MIN_PWR,
P.AVG_PWR
FROM [NZ.DW.Node.CapIntel] P
WHERE P.NodeID IN (SELECT ID FROM [NZ.Node.Nodes])
AND SiteID IN (SELECT SiteID FROM SiteIDs)
ORDER BY P.[Date], P.NodeID;
Key Adjustments:
Replaced DECLARE and INSERT INTO:

Used a CTE (WITH SiteIDs AS (...)) to define the list of SiteID values.
Kept the main query intact:

Referenced the SiteIDs CTE instead of using a variable or temporary table.
Removed unsupported syntax:

No DECLARE, INSERT INTO, or other procedural statements, which are unsupported in Power BI DirectQuery.
Steps to Implement:
Copy and paste the rewritten query into the Power BI query editor.
Test the connection and validate the data preview.
Apply the changes and the error should be resolved.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

[EZimmet]

TY for the reply - I tried the new code and received the following error