我知道有很多话题,但是我认为这是非常具体的. 我得到了用于审核目的的当前代码:
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类型
发布评论