Error loading data

[Markzolotoy]

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:

Query2

Microsoft SQL: Incorrect syntax near the keyword 'declare'. Incorrect syntax near ')'.

What is the problem?

Thanks

[Anonymous]

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

[Markzolotoy]

I used DirectQuery.

[Anonymous]

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. 

[Markzolotoy]

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

[Anonymous]

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. 

[Markzolotoy]

@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 <> ''

[Markzolotoy]

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.

[Markzolotoy]

I will definitely optimize it later, it's just existing  code now.

[ToddChitt]

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.

[Markzolotoy]

@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?

[Markzolotoy]

@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 ')'.

[Markzolotoy]

It works perfectly in SSMS.

[ToddChitt]

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
Source

Is that what you are doing here?