Error "ODBC data type -150 is not supported" when connecting sqlalchemy to mssql
<p>I keep running into an odd error when attempting to connect python sqlalchemy to a msssql server/database. I need to use sqlalchemy as it is (from what I've been told) the only way to connect pandas dataframes to mssql. </p>

<p>I have tried connecting sqlalchemy two different ways:</p>

<ol>
<li><p>using full connection string:</p>

<pre><code>import sqlalchemy as sa
import urllib.parse as ulp

usrCnnStr = r'DRIVER={SQL Server};SERVER=myVoid\MYINSTANCE;Trusted_Connection=yes;'
usrCnnStr = ulp.quote_plus(usrCnnStr)
usrCnnStr = "mssql+pyodbc:///?odbc_connect=%s" % usrCnnStr

engine = sa.create_engine(usrCnnStr)
connection = engine.connect()
connection.execute("select getdate() as dt from mydb.dbo.dk_rcdtag")
connection.close()
</code></pre></li>
<li><p>using DSN:</p>

<pre><code>import sqlalchemy as sa
import urllib.parse as ulp

usrDsn = 'myDb'
params = ulp.quote_plus(usrDsn)
engine = sa.create_engine("mssql+pyodbc://cryo:pass@myDb")
conn = engine.connect()
conn.execute('select getdate() as dt')
conn.close()
</code></pre></li>
</ol>

<p>Both methods return the same error: </p>

<pre><code>sqlalchemy.exc.DBAPIError: (pyodbc.Error) ('ODBC data type -150 is not supported.  Cannot read column .', 'HY000') [SQL: "SELECT  SERVERPROPERTY('ProductVersion')"]
</code></pre>

<p>I am not sure how to get around this error; when I execute the "SELECT  SERVERPROPERTY('ProductVersion')" in mssql, it works fine but comes back with a data type of "sql_variant". </p>

<p>Is there any way to get around this? </p>

<p>IIRC, this is because you can't select non-cast functions directory, since they don't return a datatype <em>pyodbc</em> recognizes.</p>

<p>Try this:</p>

<pre><code>SELECT CAST(GETDATE() AS DATETIME) AS dt
</code></pre>

<p>Also, your may want to use <code>CURRENT_TIMESTAMP</code>, which is ANSI standard SQL, instead of <code>GETDATE()</code>: <a href="http://stackoverflow.com/questions/186572/retriving-date-in-sql-server-current-timestamp-vs-getdate">Retriving date in sql server, CURRENT_TIMESTAMP vs GetDate()</a></p>

<p>I'm not sure where your product version select is coming from, but hopefully this gets you on the right path. I'll amend the answer if we figure out more.</p>

python sql-server sqlalchemy pyodbc