How to pass a dynamic query to source server from databricks

[Rajani]

I have this usecase wherein i am supposed to pass a dynamic query to get data from source I have tried the query option but its giving error 
SparkConnectGrpcException: (com.microsoft.sqlserver.jdbc.SQLServerException) Incorrect syntax near the keyword 'exec'.
I have tried executing same query in sql server and it works fine.Does anyone know how can i acheive it?

i have also tried the prepare_query option and tried to pass two queries it was helpful when i was passing cte but not when a dynamic query is created.

can anyone help?

#jdbc#databricks#dynamicQuery

[Kaniz_Fatma · Accepted solution]

Hi @Rajani, 

1. Make sure the table name is correctly specified within parentheses as part of your query definition.
2. If your query needs to be dynamically generated based on runtime conditions, consider using placeholders (? or named parameters) in your SQL query. This allows you to bind parameters dynamically at runtime, ensuring flexibility and correctness.
3. While prepare_query is handy for static queries with placeholders, it might not suit dynamic queries as effectively. Ensure your approach aligns with the dynamic nature of your SQL generation.

4. If issues persist, verify that your dynamically generated query is valid SQL and that placeholders are appropriately substituted with actual values. Also, check for any special characters or reserved keywords that could be causing syntax errors.

[Rajani · Accepted solution]

Hi  
thanks for your reply,
I have used foreign catalouge to fetch required data from information schema  then i am creating the dynamic query in databricks and then passing in query this is working for me! 

@Kaniz_Fatma