Forum Discussion

Impactful Individual

5 years ago

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), ...

Super User

5 years ago

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
Impactful Individual
5 years ago
It works perfectly in SSMS.
- ToddChitt
  Super User
  5 years ago
  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?
Markzolotoy
Impactful Individual
5 years ago
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
Impactful Individual
5 years ago
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?