SQL Agent fails when running a Job

Tony Owers
Tony Owers used Ask the Experts™
on
SQL Agent is failing when it attempts to execute a stored procedure. (A connection cannot be made.)

however using the copy database wizard, this utilizes SQL agent to complete the task and this is able to make the connection without a problem, but running a task direct from SQL Agent always fails.

Agent is up and running , so are all the other components of SQL server ie SQL Browser, Reporting, etc


the error message i am getting is

Date		23/11/2019 10:31:59
Log		SQL Server Agent (Current - 13/11/2019 08:57:00)

Message
[136] Job daily_PPM_Update reported: Microsoft.AnalysisServices.Xmla.ConnectionException: A connection cannot be made. Ensure that the server is running. ---> System.Net.Sockets.SocketException: No connection could be made because the target machine actively refused it 127.0.0.1:2383
   at System.Net.Sockets.TcpClient..ctor(String hostname, Int32 port)
   at Microsoft.AnalysisServices.Xmla.XmlaClient.GetTcpClient(ConnectionInfo connectionInfo)
   --- End of inner exception stack trace ---
   at Microsoft.AnalysisServices.Xmla.XmlaClient.OpenConnection(ConnectionInfo connectionInfo, Boolean& isSessionTokenNeeded)
   at Microsoft.AnalysisServices.Xmla.XmlaClient.Connect(ConnectionInfo connectionInfo, Boolean beginSession)
   at Microsoft.SqlServer.Management.Smo.Olap.SoapClient.Connect()
   at OlapEvent(SCH_STEP* pStep, SUBSYSTEM* pSubSystem, SUBSYSTEMPARAMS* pSubSystemParams, Boolean fQueryFlag)

Open in new window



i have tried everything i can find on line to sort this but no luck

I am using MS SQL Server 2017 on a Windows 2019 Datacenter server
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Distinguished Expert 2017

Commented:
How was the job created?
Is it a local job or is being triggered to run in another SQL server (connection error)

The task is supposed to update an OLAP instance?

Commented:
as per your error, you try to connect to SSAS (SQL Server Analysis Services )   -=> check if SSAS  is up and running
or if it must be a local SSAS with Sql server (the one that you use sql agent),
check if SSAS service is up

Commented:
Make sure the account running your SQL Agent service has the correct permissions on the SSAS instance.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
you may have hit the nail on the head.. looking to see if that was running and it doesnt exist in my services list, trying to make sure its installed now.. will post back when i have SSAS up and running

Author

Commented:
OK now i have another problem, i installed SSAS and that is now up and running but since then SQL Agent wont start, i get this error

===================================

Start failed for Job 'daily_PPM_Update'.  (Microsoft.SqlServer.Smo)

------------------------------
For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=15.0.18142.0+((SSMS_Rel).190722-0816)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Start+Job&LinkId=20476

------------------------------
Program Location:

   at Microsoft.SqlServer.Management.Smo.Agent.Job.Start()
   at Microsoft.SqlServer.Management.SqlManagerUI.StartAgentJobs.StartJobAction.DoAction(ProgressItemCollection actions, Int32 index)
   at Microsoft.SqlServer.Management.SqlStudio.Controls.ProgressItemCollection.DoWorkOnThread()

===================================

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------
Program Location:

   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType, Boolean retry)
   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(StringCollection sqlCommands, ExecutionTypes executionType, Boolean retry)
   at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(StringCollection queries, Boolean retry)
   at Microsoft.SqlServer.Management.Smo.Agent.Job.StartImpl(String jobStepName)
   at Microsoft.SqlServer.Management.Smo.Agent.Job.Start()

===================================

SQLServerAgent is not currently running so it cannot be notified of this action. (.Net SqlClient Data Provider)

------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=14.00.2027&EvtSrc=MSSQLServer&EvtID=22022&LinkId=20476

------------------------------
Server Name: MYCMMS
Error Number: 22022
Severity: 16
State: 1


------------------------------
Program Location:

   at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException)
   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType, Boolean retry)

Open in new window


When i attempt to start it, it goes through its start up, starts and is marked as running, then stops within about 30 seconds, no error reported

Commented:
can you check and start the SQLServerAgent  service and start it (you'd need to set it in "auto start' mode)?


you'd need to set SSAS DB/Cube/ login/ etc.

also- open the sql agent job step to see more details
or
check the "old" servers (sql server and SSAS) to see what is missed

Author

Commented:
not sure what you mean by any of those 3 actions, but will try and answer them

ssas is logged on and running..

SQL agent Job step is to run an SP..

i dont have any old servers...

Author

Commented:
the following is the SQL agent log,

sqlaErrors.jpg

Commented:
can you check if sql server service is up?

do you have SSAS database set?

Author

Commented:
the server is up, whats the ssas db?
Distinguished Expert 2017

Commented:
It is the Olap advanced analytics instance of ms SQL components.

Author

Commented:
yes that is running

Commented:
can you start sql agent service?
can you run select @@version and post the result to see if it is Sql express that does not halve sql agent ?


Can you login into SSAS  by using SSMS and check if you have databases there?
if you just installed SSAS - you may need to set DB and more..

Author

Commented:
it is evaluation edition..

SQL Agent was running untill i installed ssas
David ToddSenior Database Administrator

Commented:
Hi,

As I read the SQL Agent error log, it stops because it can not log into SQL. That is, something changed the account it uses as a service, because that account needs rights within SQL.

What I do as a step back from best practice where every service runs under their own account, is for SQL and SQL Agent (for the same instance) to run under the same service account.

HTH
  David

Author

Commented:
yeah i thought of that changed everything to run under local system account, everything is running except SQL Agent

Commented:
did you try to reboot?

try to create local windows login and add to the local admins group and sql sa role
and make it the sql agent service  as a start up login

Commented:
Tony - can you screenshot your configuration manager screen showing the services, states and accounts?

Author

Commented:
screen shot of gonfig manager

Commented:
Tony are you connected to a domain? Would it be possible to use domain accounts?

Author

Commented:
i did a repair install and it is now up and running, but have another problem it is failing when executing the sp with the error below, but the sp runs directly if i execute it so the problem isnt with the sp.

Date		25/11/2019 07:21:34
Log		SQL Server Agent (Current - 25/11/2019 06:46:00)

Message
[136] Job PPM Update reported: Microsoft.AnalysisServices.Xmla.XmlaException: The exec sp_daily_pms text node at line 7, column 33 cannot appear inside the Command element (namespace urn:schemas-microsoft-com:xml-analysis) under Envelope/Body/Execute. This element can only have text nodes containing white-space characters.

   at Microsoft.AnalysisServices.Xmla.XmlaClient.CheckForSoapFault(XmlReader reader, XmlaResult xmlaResult, Boolean throwIfError)
   at Microsoft.AnalysisServices.Xmla.XmlaClient.CheckForError(XmlReader reader, XmlaResult xmlaResult, Boolean throwIfError)
   at Microsoft.AnalysisServices.Xmla.XmlaClient.SendMessage(Boolean endReceivalIfException, Boolean readSession, Boolean readNamespaceCompatibility)
   at Microsoft.AnalysisServices.Xmla.XmlaClient.SendMessageAndReturnResult(String& result, Boolean skipResult)
   at Microsoft.AnalysisServices.Xmla.XmlaClient.Execute(String command, String properties, String& result, Boolean skipResult, Boolean propertiesXmlIsComplete)
   at Microsoft.SqlServer.Management.Smo.Olap.SoapClient.ExecuteStatement(String stmt, StatementType stmtType, Boolean withResults, String properties, String parameters, Boolean restrictionListElement, String discoverType, String catalog)
   at Microsoft.SqlServer.Management.Smo.Olap.SoapClient.SendCommand(String command, Boolean withResults, String properties)
   at OlapEvent(SCH_STEP* pStep, SUBSYSTEM* pSubSystem, SUBSYSTEMPARAMS* pSubSystemParams, Boolean fQueryFlag)

Open in new window

Commented:
it is a good news..


can you tell more about the job?

it looks like the proc is called and running  from SSIS..

Also please reply with SSAS post setup that as per your above post you just installed but it does not look like it configured ( login, permissions ,Database..)
--
if you are new to SSAS and SSIS - please open an another EE Q. to address this error

the original one is answered  (no SSAS )
Distinguished Expert 2017

Commented:
Check under whose credentials the SP is running, often it will run under the creator of the JOB using that user's credentials, rights, switching it to another account could resolve your issue.
I have got this working, in sql manager i started an instance of ssas then recreated the sp and now it runs, thank you so much for your help..

i wouldnt have done it alone

Author

Commented:
Thank you for all your help, i couldnt have done this alone.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial