Automation Account error - when Try to call stored procedure from Automation account getting error:

Question

Automation Account error - when Try to call stored procedure from Automation account getting error:

Prajna Jena (ADM) 0

Traceback (most recent call last): File "/usr/src/tmp/8643d647-6858-4b74-88c5-96b2e28a66f4/runbooks/Dravosa.py", line 30, in <module> cursor = source_engine.raw_connection().cursor() File "C:\userenv\lib\site-packages\sqlalchemy\engine\base.py", line 3300, in raw_connection return self.pool.connect() File "C:\userenv\lib\site-packages\sqlalchemy\pool\base.py", line 449, in connect return _ConnectionFairy._checkout(self) File "C:\userenv\lib\site-packages\sqlalchemy\pool\base.py", line 1263, in _checkout fairy = _ConnectionRecord.checkout(pool) File "C:\userenv\lib\site-packages\sqlalchemy\pool\base.py", line 712, in checkout rec = pool._do_get() File "C:\userenv\lib\site-packages\sqlalchemy\pool\impl.py", line 180, in _do_get self._dec_overflow() File "C:\userenv\lib\site-packages\sqlalchemy\util\langhelpers.py", line 146, in exit raise exc_value.with_traceback(exc_tb) File "C:\userenv\lib\site-packages\sqlalchemy\pool\impl.py", line 177, in _do_get return self._create_connection() File "C:\userenv\lib\site-packages\sqlalchemy\pool\base.py", line 390, in _create_connection return _ConnectionRecord(self) File "C:\userenv\lib\site-packages\sqlalchemy\pool\base.py", line 674, in init self.__connect() File "C:\userenv\lib\site-packages\sqlalchemy\pool\base.py", line 901, in __connect pool.logger.debug("Error on connect(): %s", e) File "C:\userenv\lib\site-packages\sqlalchemy\util\langhelpers.py", line 146, in exit raise exc_value.with_traceback(exc_tb) File "C:\userenv\lib\site-packages\sqlalchemy\pool\base.py", line 896, in __connect self.dbapi_connection = connection = pool._invoke_creator(self) File "C:\userenv\lib\site-packages\sqlalchemy\engine\create.py", line 643, in connect return dialect.connect(*cargs, **cparams) File "C:\userenv\lib\site-packages\sqlalchemy\engine\default.py", line 620, in connect return self.loaded_dbapi.connect(*cargs, **cparams) pyodbc.InterfaceError: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')

0 comments

1 answer

Your answer

Answer 1

Hello Prajna !

Thank you for posting on MS Learn Q&A.

I think your issue is not from the SP because it fails before connecting to SQL Server and the Automation runbook cannot find the required ODBC Driver for SQL Server or the driver name in the connection string is wrong.

In the cloud sandbox uploading pyodbc/sqlalchemy is not enough because pyodbc also needs a native ODBC SQL Server driver installed on the machine. Azure Automation supports importing Python packages but native dependencies can be a limitation in the sandbox environment. Try to use a hybrid RW when the runbook needs local machine dependencies or specific network access. https://learn.microsoft.com/en-us/azure/automation/python-3-packages

check in your runbook

import pyodbc
print(pyodbc.drivers())

if you do not see something like:

ODBC Driver 17 for SQL Server
ODBC Driver 18 for SQL Server

then your connection will fail.

so run the python runbook on a HW and install:

Python
pyodbc
sqlalchemy
Microsoft ODBC Driver 17 or 18 for SQL Server

and use a DSN less connection string :

import pyodbc
conn = pyodbc.connect(
    "DRIVER={ODBC Driver 18 for SQL Server};"
    "SERVER=tcp:<server>.database.windows.net,1433;"
    "DATABASE=<database>;"
    "UID=<user>;"
    "PWD=<password>;"
    "Encrypt=yes;"
    "TrustServerCertificate=no;"
)
cursor = conn.cursor()
cursor.execute("EXEC dbo.YourStoredProcedure")
conn.commit()

Share via

Automation Account error - when Try to call stored procedure from Automation account getting error:

1 answer

Your answer