February 4, 2021 at 2:10 pm
Hello,
I have a python script (version 3.8) which then calls the Stored procedure in a database (with all the required params: databasename, user,pwd)
The SP has an update within Begin/End trans.
If the Update fails then I have an EXECUTE [msdb].[dbo].[sp_send_dbmail] to send me an alert email.
That doesn't work from the Python script. It never sends the email. On its own the SP works fine and does send an alert mail.
Just before the send mail, there is a Rollback trans.
Any thoughts on why there is a conflict with a call from Python?
thank you,
Vin
February 4, 2021 at 2:33 pm
Does the update work OK when executed from Python? That is, is the only problem with the mail send part?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 4, 2021 at 2:44 pm
Hi Phil,
yes the Update works fine from Python.
When there are no errors, update completes with a commit.
When an error happens, even Rollback works but fails to send the mail.
Vin
February 4, 2021 at 2:50 pm
It sounds like a permissions problem. When the Python script executes, what is its user context and does this user have the permissions required on MSDB?
Can you try executing the proc as the user which the script uses and see whether the problem remains?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 4, 2021 at 4:41 pm
Phil,
The python itself doesn't need any permission to run. It runs any script on my Windows authentication. (on terminal)
The python script acts like an envelope over the SQL, so that any output of SQL is captured in a log file.
It's part of so many other SQL scripts, hence I can't call that bit in a stand-alone SQL SP exec.
There are scripts which needs before and after this update.
Vin
February 4, 2021 at 6:11 pm
When you execute a proc in SQL Server, there must be a SQL Server login associated with that execution.
There should also be database-level users associated with the login and those users will have been granted various permissions within that database (GRANT EXECUTE etc).
My suggestion was to verify that the MSDB user associated with the login being used by the Python script when it executes has sufficient privileges.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply