SQL Server 2019 MSX/TSX environment. TSX servers are clustered instances. MSX is not. There is a SQL integration service on each of the nodes in the cluster. I create a maintenance plan on the MSX and when i try to push it to the TSX i get this error:
Message
[000] Failed to import Maintenance Plan 'MaintenancePlanMSA' from MSX: Microsoft.SqlServer.Management.Sdk.Sfc.EnumeratorException: Failed to retrieve data for this request. —> Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. —> System.Data.SqlClient.SqlException: Parameter 0 is incorrect for this statement.
The statement has been terminated.
at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException)
at Microsoft.SqlServer.Management.Common.ServerConnection.GetExecuteReader(SqlCommand command)
— End of inner exception stack trace —
at Microsoft.SqlServer.Management.Common.ServerConnection.GetExecuteReader(SqlCommand command)
at Microsoft.SqlServer.Management.Smo.ExecuteSql.GetDataReader(String query, SqlCommand& command)
at Microsoft.SqlServer.Management.Smo.DataProvider.SetConnectionAndQuery(ExecuteSql execSql, String query)
at Microsoft.SqlServer.Management.Smo.ExecuteSql.GetDataProvider(StringCollection query, Object con, StatementBuilder sb, RetriveMode rm)
at Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillData(ResultType resultType, StringCollection sql, Object connectionInfo, StatementBuilder sb)
at Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillDataWithUseFailure(SqlEnumResult sqlresult, ResultType resultType)
at Microsoft.SqlServer.Management.Smo.SqlObjectBase.BuildResult(EnumResult result)
at Microsoft.SqlServer.Management.Smo.DiskFile.GetData(EnumResult erParent)
at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData()
at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData(Request req, Object ci)
at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.GetData(Object connectionInfo, Request request)
at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.Process(Object connectionInfo, Request request)
— End of inner
I tried to add all agent service users as sysadmin, didn't work.
The funny thing is that the job, under the maintenance plan, appears on the TSX.
When i create a standalone job and push it to the target, the job appears on the TSX(?!).
Hope someone can help.
Thank you in advance.
Best Answer
JohnnyP. I know that a long time has passed - but maybe someone will need it. This error is related to the sys.dm_os_enumerate_filesystem() function launched without the @Path parameter. In my case, the error appeared due to the fact that I did not specify the folder for the job execution log. If you create a maintenance plan by wizard, then you need to tick the 'write and report to a text file' and select folder on the "Select Report Options" window.