I have a legacy database that is running SQL Server 2008 R2 Express Edition.

Today, I was trying to use sqlacodegen to generate SQLAlchemy classes via the pyodbc package. I was able to connect to the database without a problem, but I ran into an immediate error with the following query generated by SQLAlchemy:

SELECT cast(com.value as nvarchar(max))
  FROM fn_listextendedproperty('MS_Description',
    'schema', CAST(? AS NVARCHAR(max)), 'table', CAST(? AS NVARCHAR(max)), NULL, NULL
  ) as com;

with the following parameters:

[('dbo', 'Accounting')]

The error was as follows:

sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42000', "[42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Incorrect syntax near 'CAST'. (102) (SQLExecDirectW); [42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL 
Server]Statement(s) could not be prepared. (8180)")

However, when I substituted in 'dbo' and 'Accounting' in for the prepared statement wildcards, then ran it in SQL Server Management Studio, the query ran fine.

I then tried using sqlcmd and got the exact same error as when I had run the code through Python, demonstrating this is not a SQLAlchemy or pyodbc error:

1> SELECT
2> cast ( com.value as nvarchar(max) )
3> FROM
4> fn_listextendedproperty
5> (
6>   'MS_Description'
7>   , 'schema'
8>   , cast ( 'dbo' as nvarchar(max) )
9>   , 'table'
10>   , cast ( 'Accounting' as nvarchar(max) )
11>   , NULL
12>   , NULL
13> ) as com
14> go
Msg 156, Level 15, State 1, Server SERVER, Line 8
Incorrect syntax near the 'CAST'.

I even went and substituted { fn convert ('dbo', SQL_LONGVARCHAR) } per some documentation, but this did not help.

I then ran a similar query against a different Azure SQL Server database, both using the online query editor in the portal and SQLAlchemy ODBC connection, and both succeeded.

I also tried all of the different available drivers I had ('SQL Server', 'SQL Server Native Client 10.0', 'ODBC Driver 18 for SQL Server', 'ODBC Driver 17 for SQL Server', 'ODBC Driver 13 for SQL Server', 'ODBC Driver 11 for SQL Server') as well as various permutations of connection string options (only TrustServerCertificate=Yes; was helpful for 'ODBC Driver 18'), and nothing worked. Yet all of the queries I tried executed successfully in SSMS.

The only way I could get the query to run at all over ODBC was to declare and set variables for the two problematic arguments to fn_listextendedproperties:

DECLARE @schemaname NVARCHAR(max)
SET @schemaname = { fn convert ( 'dbo', SQL_LONGVARCHAR ) }

DECLARE @tablename NVARCHAR(max)
SET @tablename = { fn convert ( 'Accounting', SQL_LONGVARCHAR ) }

SELECT
convert ( nvarchar( max ) , com.value)
FROM
fn_listextendedproperty
(
  'MS_Description'
  , 'schema'
  , @schemaname
  , 'table'
  , @tablename
  , NULL
  , NULL
) as com

What is causing these errors? Does SQL Server 2008 have some sort of bug, or am I not configuring my ODBC connection properly?