尚不支持pandas + pyodbc ODBC SQL类型-150 [英] pandas + pyodbc ODBC SQL type -150 is not yet supported

问题描述

我知道有很多话题，但是我认为这是非常具体的. 我得到了用于审核目的的当前代码:

I know there is many topics on this but i think this is much specific. I get the current code for audit purpose:

import pandas as pd
import pyodbc

query = """
--Top 50 high total CPU Queries
SELECT TOP 50
'High CPU Queries' as Type,
serverproperty('machinename') as 'Server Name',
isnull(serverproperty('instancename'),serverproperty('machinename')) as 'Instance Name',
        COALESCE(DB_NAME(qt.dbid),
        DB_NAME(CAST(pa.value as int)), 
        'Resource') AS DBNAME,
    qs.execution_count as [Execution Count],
    qs.total_worker_time/1000 as [Total CPU Time],
    (qs.total_worker_time/1000)/qs.execution_count as [Avg CPU Time],
    qs.total_elapsed_time/1000 as [Total Duration],
    (qs.total_elapsed_time/1000)/qs.execution_count as [Avg Duration],
    qs.total_physical_reads as [Total Physical Reads],
    qs.total_physical_reads/qs.execution_count as [Avg Physical Reads],
    qs.total_logical_reads as [Total Logical Reads],
    qs.total_logical_reads/qs.execution_count as [Avg Logical Reads],
SUBSTRING(qt.text,qs.statement_start_offset/2, 
        (case when qs.statement_end_offset = -1 
        then len(convert(nvarchar(max), qt.text)) * 2 
        else qs.statement_end_offset end -qs.statement_start_offset)/2)
        as query_text     
    FROM sys.dm_exec_query_stats qs
    cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt
    outer apply sys.dm_exec_query_plan (qs.plan_handle) qp
    outer APPLY sys.dm_exec_plan_attributes(qs.plan_handle) pa
    where attribute = 'dbid'   
    ORDER BY 
        [Total CPU Time] DESC
"""
cnxn = pyodbc.connect('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx')
cnxn.execute(query).fetchall()
cnxn.close()

我收到以下错误:

cnxn.execute(sql_status_20).fetchall() 追溯(最近一次通话): 文件"，第1行，在 pyodbc.ProgrammingError :('尚不支持ODBC SQL类型-150.column-index = 1 type = -150'，'HY106')

cnxn.execute(sql_status_20).fetchall() Traceback (most recent call last): File "", line 1, in pyodbc.ProgrammingError: ('ODBC SQL type -150 is not yet supported. column-index=1 type=-150', 'HY106')

任何人都可以帮助我解决这个问题吗?对于使用日期的许多SQL Server审核脚本，我也有同样的问题，因为我的生产环境中有各种SQL版本，所以我无法更改驱动程序.

Anyone can help me to handle this ? I have the same problem with many SQL Server audit scripts using dates and i can't change the driver as i have all kind of SQL versions in my production env.

推荐答案

如果无法更改驱动程序，则需要更改查询以返回其支持的数据类型.

If you can't change the driver, you'll need to change the query to return data types it supports.

SQL类型-150是SQL_VARIANT，由SERVERPROPERTY返回.解决方法是将列明确地CAST设置为受支持的类型，例如nvarchar:

SQL type -150 is SQL_VARIANT, which is returned by SERVERPROPERTY. The workaround is to explicitly CAST the column to a supported type like nvarchar:

CAST(SERVERPROPERTY('machinename') AS nvarchar(100)) AS 'Server Name',
CAST(ISNULL(SERVERPROPERTY('instancename'),SERVERPROPERTY('machinename')) AS nvarchar(100)) AS 'Instance Name',

这篇关于尚不支持pandas + pyodbc ODBC SQL类型-150的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！