Forum Discussion

shavish's avatar
shavish
Helper I
1 year ago
Solved

SQL syntax error

Hello all,   Am trying to add data from SQL server through direct query option. When i add data and i am getting following error: Microsoft SQL: Incorrect syntax near the keyword 'DECLARE'. Incorr...
Need Help
Tips and Tricks
  • hnguy71's avatar
    hnguy71
    1 year ago

    Hi shavish 

    Power BI wraps your query statement in another SELECT statement, therefore, you won't be able to use the DECLARE function.

     

    You can try this instead:

    SELECT
CONVERT(varchar(50), Table1.names) As Name,
CONVERT(varchar(50), Table1.id) As ID,

FROM
Table1

     

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi shavish ,

Thank you for using Microsoft Community Forum.

 

The issue comes from the DECLARE statements, Power BI’s Direct Query doesn’t support procedural SQL commands like DECLARE.

Here are a few ways to fix this:

If the values are fixed, you can hardcode them directly in the WHERE clause:

 

SELECT

    Table1.names AS Name,

    Table1.id AS ID

FROM

    Table1

WHERE

    Table1.names = 'Example_Name'

    AND Table1.id = 'Example_ID';

 

If you need flexibility, consider creating a SQL view on the database side with the logic you need. Then connect Power BI to that view for a cleaner setup.

 

If the T-SQL logic must stay dynamic switching to Import mode instead of Direct Query will support variables and more advanced SQL syntax, though it won’t reflect live data changes.

 

I hope my suggestions give you good idea, if you need any further assistance, feel free to reach out.

 

If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.

 

Thankyou.

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi shavish ,


I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.


Thank you.

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi shavish ,

     

    May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

     

    Thank you.