CODEWITHSUNDEEP
azureazure-synapse
Bhargav Jangam
Bhargav Jangam

Reputation: 1

External table 'dbo.alltype' is not accessible because location does not exist or it is used by another process.DB-Lib error

I am trying to access data from Azure Synapse,getting the below error ""External table 'dbo.alltype' is not accessible because location does not exist or it is used by another process.DB-Lib error". My part is retrieving data so I am not worrying about how the table has been created and with what parameters.

Below is the code

def read_query_data(self, query, db=""):
        result = {"STATUS": "FAIL", "MESSAGE": "Error in {} read_query_data".format(self.ds_type),
                  "DATA": pd.DataFrame()}
        try:
            df = pd.read_sql(query, self.connection)
            df.columns = [col.upper() for col in df.columns]
            result["DATA"] = df.fillna("")
            result["STATUS"] = "SUCCESS"
            result["MESSAGE"] = ""
        except Exception as e:
            result["MESSAGE"] += "\nError(s): {}".format(str(e.args))
            log.error(result["MESSAGE"])
            traceback.print_exc()
        return result

where query is SELECT * FROM dbo.alltype

Upvotes: 0

Views: 277

Answers (1)

Bhavani
Bhavani

Reputation: 5317

The external table 'dbo.alltype' is not accessible because the location does not exist or it is being used by another process.

The user to whom you are trying to connect to the SQL database does not have access to read data from the external table location attached to the External table. This may be the reason for receiving the error while reading the table. If the table is from the serverless SQL pool/dedicated SQL pool and the external location is blob storage or ADLS attached to Synapse, you can use Active Directory MFA authentication. You can use the code below:

import pyodbc
conn = pyodbc.connect(driver='{ODBC Driver 18 for SQL Server}', host='<hostName>', database='<databaseName>',
                      user='<ADUserName>', Authentication='ActiveDirectoryInteractive')
cursor = conn.cursor()
cursor.execute("SELECT * FROM <tableName>")
rows = cursor.fetchall()
for row in rows:
    print(row)

The data table will be read successfully as shown below:

Enter image description here

Upvotes: 0

Related Questions