Hello~
I have a scheduled SSMS Job that had been working without issue, but started failing recently with the following error:
Message
Executed as user: NT AUTHORITY\SYSTEM. Failed to initialize sqlcmd library with error number -2147467259. [SQLSTATE 42000] (Error 22050). The step failed.
The job is simply running a T-SQL script that counts the number of records in a view, and if there are records in that view, sends an email to specific users. The script works without issue when I execute it directly.
It runs successfully as a job only when there are no records in my view and there is no email to send. Otherwise, I get the above error. In the job step properties, the type is 'Transact SQL (T-SQL) Script' and the Database is the database which contains the view in question.
On searching this error, I found an article suggesting that I define all my tables and views using the entire string starting with the db name ([DBNAME].dbo.table1), which I have done, but that hasn't made any difference.
Perhaps something else I'm missing? Thanks in advance.
January 5, 2021 at 8:32 pm
Does that view access a table across a linked server? You say the script works fine if you execute it manually - but does that include the send mail portion? If not, check and validate that you can send mail using sp_send_dbmail.
Last thought - if the agent job is not owned by 'sa', then verify the owner of the agent job's permissions/access hasn't been changed. You can also try executing the code as that user.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 5, 2021 at 8:35 pm
removed - duplicate from above
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 5, 2021 at 8:52 pm
Hi guys ~
Thanks for your responses. I am not querying a linked server. The code does work in its entirety, including sending an email, when I run it manually.
I changed job ownership to 'sa', and then logged back into SSMS using the 'sa' account and tried to run the job. Unfortunately it failed again with the same error.
I'll keep searching for a solution, but of course any other ideas or suggestions are much appreciated. Thanks!
January 5, 2021 at 9:23 pm
Was the original owner of the agent job also a sysadmin in SQL Server?
I am thinking something has changed for the service account running SQL Server and/or SQL Server Agent. If the agent job is owned by a sysadmin - it is executed in the context of the account running the service.
When you run it manually - it is executing in the context of your account.
You could try setting up a proxy account with your windows credentials and set the agent job step to run using the proxy account.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 5, 2021 at 11:29 pm
Hi~
I do have a proxy account set up to run some of my VBS scripts. If I'm understanding you correctly, I would need to change the Type to 'Operating System (cmdexec)' and then select the proxy under 'Run As', and then in the command section enter a sqlcmd line to run my script?
The sqlcmd utility does not seem to be installed on my system, but I'll look into getting that installed.
Hi Jeff,
Thanks again for your help -- I think I figured it out -- along the lines of what you were getting at -- I added the following line to the t-sql script and its now working.
execute as login = 'sa'
EXEC msdb.dbo.sp_send_dbmail
This seems to solve whatever permission issues it was facing. Thanks again for your help -- its so very useful to hash these things out with someone.
January 11, 2021 at 2:45 pm
I think what Jeffery was getting at was "who is the SQL Server Agent Service set to run as?". I have a feeling it is NT Authority/System which doesn't have permissions on the database.
Your 'fix" is essentially telling the Agent to run it as the SQL Server Service account which does appear to have permissions.
I would look at changing the SQL Server Agent Service account to an AD account so you have some control over it and then remove the "execute as login = 'sa'" line as that should not be needed.
But that is just me. Since it is working now, if it is a critical process, I wouldn't want to change things while it is working and break it unless I had a good downtime window.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
January 11, 2021 at 2:49 pm
Brian~
Thanks for that feedback -- yes, I will find a weekend to get to the bottom of this.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply