Unable to get MS SQL Server paths using python

Question

I am trying to get sql server data and log path using SERVERPROPERTY.

When I run below stmt in SSMS, I get paths.

SELECT  SERVERPROPERTY('InstanceDefaultLogPath') ,SERVERPROPERTY('InstanceDefaultDataPath')

But when I try to run the same query from python using pyodbc. it gives me:

result = connsql.cursor().execute(query).fetchone()
pyodbc.ProgrammingError: ('ODBC SQL type -150 is not yet supported.  column-index=0  type=-150', 'HY106')

Any idea how to get the paths in python?

Code:

 def getSQLServerPath(self):
        try:
            print("Into function..")
            connsql = self.sql_connection()
            query = "SELECT SERVERPROPERTY('InstanceDefaultLogPath') ,SERVERPROPERTY('InstanceDefaultDataPath') "
            result = connsql.cursor().execute(query).fetchone()
            print(result)
            connsql.cursor().commit()
            connsql.close()
            # return path
        except Exception:
            logging.exception("getSQLServerPath function: Something went wrong.")

Thom A · Accepted Answer · 2019-12-12 18:16:53Z

2

The error is actually telling you the problem here, it's the data type being returned that's the problem. The expression SERVERPROPERTY('InstanceDefaultLogPath') returns the data type sql_variant, which almost nothing apart from SQL Server supports. You can check this with the below SQL:

SELECT system_type_name
FROM sys.dm_exec_describe_first_result_set('SELECT SERVERPROPERTY(''InstanceDefaultLogPath'');',NULL, NULL);

As a result, you need to explicitly CONVERT the values to datatypes that ODBC does support. As these are both file paths, an nvarchar would seem the correct choice:

SELECT CONVERT(nvarchar(260),SERVERPROPERTY('InstanceDefaultLogPath')) AS InstanceDefaultLogPath,
       CONVERT(nvarchar(260),SERVERPROPERTY('InstanceDefaultDataPath')) AS InstanceDefaultDataPath;

answered Dec 12, 2019 at 18:16

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Unable to get MS SQL Server paths using python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related