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!
SQL Agent fails when running a Job
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
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
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)
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
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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?
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?
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
or if it must be a local SSAS with Sql server (the one that you use sql agent),
check if SSAS service is up
Make sure the account running your SQL Agent service has the correct permissions on the SSAS instance.
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
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
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
===================================
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)
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
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
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
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...
ssas is logged on and running..
SQL agent Job step is to run an SP..
i dont have any old servers...
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..
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..
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
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
yeah i thought of that changed everything to run under local system account, everything is running except SQL Agent
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
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
Tony - can you screenshot your configuration manager screen showing the services, states and accounts?
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)
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 )
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 )
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.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial