SQLServer Incorrect syntax near the keyword 'WITH'

[Jreco]

Hi Mates!

I'm trying to get some data from an SQLServer using a query; the query has a WITH statement but I'm getting the following error:
raise convert_exception(
pyspark.errors.exceptions.connect.SparkConnectGrpcException: (com.microsoft.sqlserver.jdbc.SQLServerException) Incorrect syntax near the keyword 'WITH'.
This is my code for the example:

query = """with cte as ( SELECT TOP 10 * FROM dbo.TestTb wt) select * from cte"""

df = spark.read \
    .format("jdbc") \
    .option("url", sqlserver_option["url"]) \
    .option("dbtable", query) \
    .option("user", sqlserver_option["user"]) \
    .option("password", sqlserver_option["password"]) \
    .option("driver", "com.microsoft.sqlserver.jdbc.SQLServerDriver") \
    .load()


I also tried to use: .option("query", query)

Both options return the same error.


Looking for similar cases, I found that in 2022 this issue was solved:

https://github.com/apache/spark/pull/28953

https://github.com/microsoft/sql-spark-connector/issues/147

https://github.com/apache/spark/pull/36440

Is it possible I'm doing something wrong?

Can someone give me a guide in this matter?

Thanks!!

[szymon_dybczak · Accepted solution]

Hi @Jreco ,

You need to use prepare query option and then query like below:

url = "jdbc:sqlserver://server_name:1433;database=db_name"
df = spark.read \
    .format("jdbc") \
    .option("url", url) \
    .option("prepareQuery", "with cte as ( SELECT TOP 10 * FROM dbo.TestTb wt)") \
    .option("query", "SELECT * FROM cte") \
    .option("user", "user_name") \
    .option("password", "password")
    .option("driver", "com.microsoft.sqlserver.jdbc.SQLServerDriver").load()   
    

I've tested it and it works 🙂

[Jreco]

Thanks for your response @szymon_dybczak was helpful!

Do you know where I can get some additional information about those options?

Thank you so much!