bug: export to pyarrow does not works with Fabric DWH

[djouallah]

What happened?

when I run this code

con.sql(sql, dialect="mssql").to_pyarrow(),

I get this error

DMV (Dynamic Management View) 'dm_exec_describe_first_result_set' is not supported.

What version of ibis are you using?

9.5

What backend(s) are you using, if any?

mssql

Relevant log output

No response

Code of Conduct

• [X] I agree to follow this project's Code of Conduct

[cpcloud]

Thanks for the issue!

Not sure if I'd categorize this as a bug. We don't test against Fabric and the various MS SQLs seem to be wildly incompatible with each other.

The reason we're using dm_exec_describe_first_result_set is to compute the column names and types of arbitrary SELECT statements. Is there a way to do that that works in both Fabric and vanilla MS SQL? If so, then we can probably implement it without having an explicit Fabric backend (though I imagine eventually we'll want that).

[djouallah]

Thanks for the issue!

Not sure if I'd categorize this as a bug. We don't test against Fabric and the various MS SQLs seem to be wildly incompatible with each other.

The reason we're using dm_exec_describe_first_result_set is to compute the column names and types of arbitrary SELECT statement. Is there a way to do that that works in both Fabric and vanilla MS SQL? If so, then we can probably implement it without having an explicit Fabric backend (though I imagine eventually we'll want that).

to be honest, I know, it is an upstream issue, I add it just as a bookmark for the lack of a better word

[gforsyth]

The previous method that we used (https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-describe-first-result-set-transact-sql) would work on both vanilla MSSQL and Fabric, according to their docs.

Unfortunately, using sp_describe_first_result_set with a case-sensitive collation apparently causes DLL loading issues because... no one knows?

[lostmygithubaccount]

@djouallah do you know people on the Fabric (or Synapse) team(s) that we could reach out to about discussing proper Azure data backend(s) to Ibis? it would be great to at least enumerate all the work we need and have a point of contact for the key differences between what we already support (MSSQL)

[cpcloud]

This requires some support on the Fabric side. Would be great to get a conversation going. @djouallah friendly ping!