Forum Discussion
Error loading data
I have the following SQL in my query:
declare @AlarmSummaryAlramCount table (summary_description nvarchar(20), summary_cnt int);
DECLARE @reportData TABLE(
reportID int, userSID varchar(max), xmlData xml
);
insert @reportData
select * from [PRISM2019R32DB]..udf_getSSRSReportRawData(3, 'S-1-5-21-1275210071-1532298954-725345543-9037')
-- Bar chart - Monthly distribution
DECLARE xml XML = (select xmlData from @reportData)
;WITH rs AS
(
SELECT col.value('(Summary_Description)[1]','varchar(20)') AS [summary_description]
, col.value('(Summary_Cnt)[1]','int') AS [summary_cnt]
, col.value('(ColorAlarmTotals)[1]','varchar(10)') AS [alarm_color]
FROM xml.nodes('AlarmSummaryReport/AlarmSummary/AlarmSummaryAlarmsSubReport') AS tab(col)
)
insert @AlarmSummaryAlramCount
SELECT case summary_description when 'Not In Alarm' then 'Not In Alarm' else 'In Alarm' end as summary_description, summary_cnt FROM rs;
select * from @AlarmSummaryAlramCount
where summary_cnt <> ''
Getting this:
13 Replies
Does this EXACT text work in SSMS?
Not sure you can do complex statements, stuff with declared variables, etc. Maybe you should wrap this into a stored procedure in the database then run that proc from Power BI.
It works perfectly in SSMS.
Is the entire statement wrapped in double quotes? Is it its own Query?
I do this sometimes, create a query with something like:
" SELECT ... FROM...WHERE... "
Then have a second query with M code like this:
let
Source = Sql.Database(ServerName, DatabaseName, [Query=#"sql_text"])
in
SourceIs that what you are doing here?
ToddChitt I have converted my code into SP and it's exactly same error. Smells to me like a PBI bug.
I am calling the store proc like this:
exec usp_GetAlerts_Summary_Counts 3, '.................'
Get this:
Microsoft SQL: Incorrect syntax near the keyword 'exec'. Incorrect syntax near ')'.
ToddChitt I have copied code from the first query inot second and I get the same error. Looks like the bug is:
declare works only for one query.
Is there a PBI version without this bug?
I will definitely optimize it later, it's just existing code now.
That's not what I am doing. Not that advanced yet 😉. I am getting data from SQL server database and in advanced section entering the above code. This is a second query. First one forked fine and I believe it's almost the same code. But I can't verify that because I can't see the code of the first query.
ToddChitt Here is the same query that works fine:
declare @AlarmSummaryWeeklyDistribution table (bucket nvarchar(20), distribution_cnt int) DECLARE @reportData TABLE( reportID int, userSID varchar(max), xmlData xml ); insert @reportData select * from [PRISM2019R32DB]..udf_getSSRSReportRawData(3, 'S-1-5-21-1275210071-1532298954-725345543-9037') -- Bar chart - Monthly distribution DECLARE @xml XML = (select xmlData from @reportData) ;WITH rs AS ( SELECT col.value('(Bucket)[1]','varchar(20)') AS [bucket] , col.value('(Distribution_Cnt)[1]','int') AS [distribution_Cnt] , col.value('(ColorBarChart)[1]','varchar(10)') AS [distribution_color] FROM @xml.nodes('AlarmSummaryReport/AlarmSummary/AlarmSummaryAlarmsSubReport') AS tab(col) ) insert @AlarmSummaryWeeklyDistribution SELECT bucket, distribution_Cnt FROM rs; select * from @AlarmSummaryWeeklyDistribution where bucket <> ''Is that the answer?
https://community.powerbi.com/t5/Desktop/Why-is-DECLARE-not-supported-Error/td-p/40983
So, still a bug even after converting into a SP???
Hi Markzolotoy
What kind of connection mode did you try to connect to SQL, import or Direct Query?
I found a post with the smiliar problem like yours, he solved his problem by change connection mode from Direct Query to import.
Here is the post: Microsoft SQL: Incorrect syntax near the keyword 'exec'. Incorrect syntax near ')'.
From this blog:Incorrect syntax in Power BI - but query works fine in SSMS? I found that Power BI Desktop has issues on parsing Queries in Direct Query mode. It always resolves the front end input SQL statement as a derived table so that statements like DECLARE, CTE will cause syntax error.
Please try to connect to SQL by import with this code.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I used DirectQuery.
Hi Markzolotoy
I found an issue like yours and it show that:
The error occurs because the query used by the customer contains comments (--) in the query. If the query were to run as-is then the query would return results successfully. While using visuals additional query is added around the original query. The '--' comments would extend to the end of line thus breaking the query generated by the visual.
Please try to remove the '--' comments from the query. If that is not possible, they should use Block Comment instead (/* */).
Or you can try import connection as well.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Markzolotoy
Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your problem.
Best Regards,
Rico Zhou
