CODEWITHSUNDEEP
pythont-sqlpyodbc
v_0ver
v_0ver

Reputation: 169

Does not detach database by sp_detach_db in pyodbc

I am trying to detach the database, but for some reason it does not detach with no error, am I missing something?

import pyodbc
params = 'DRIVER={SQL Server};SERVER=.\RISKSPEC_PSA2012;DATABASE=master;UID=sa;Pwd=sa_pasw;Trusted_Connection=yes;'
try:
    with pyodbc.connect(params) as cnxn:
        with cnxn.cursor() as cursor:
            cnxn.autocommit = True
            cursor.execute(
                '''
                USE [master];
                ALTER DATABASE [sss] SET SINGLE_USER WITH NO_WAIT;
                EXEC sp_detach_db @dbname=sss;
                ''')
except pyodbc.Error as ex:
    QMessageBox.warning(self, 'pyodbc', ex.args[0])

SQLServer 2012 version: 11.0.2100

pyodbc version: 4.0.31

Upvotes: 0

Views: 251

Answers (1)

v_0ver
v_0ver

Reputation: 169

Thx for Gord Thompson for the tip.

Fixes:

  • SET SINGLE_USER WITH NO_WAIT -> SET TRUSTWORTHY ON
  • Remove from connect params "Trusted_Connection=yes;", It associates the system user instead of "sa" user.
import pyodbc
params = 'DRIVER={SQL Server};SERVER=.\RISKSPEC_PSA2012;DATABASE=master;UID=sa;Pwd=sa_pasw;'
    try:
        with pyodbc.connect(params) as cnxn:
            cnxn.autocommit = True
            with cnxn.cursor() as cursor:
                cursor.execute(
                    '''
                    USE [master];
                    ALTER DATABASE [sss] SET TRUSTWORTHY ON;
                    EXEC sp_detach_db @dbname=sss;
                    ''')
    except pyodbc.Error as ex:
        QMessageBox.warning(self, 'pyodbc', ex.args[0])

Upvotes: 1

Related Questions