0

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.

1 Answer 1

1

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.

1
  • OMG, i don't believe it, it worked... I would kiss you right now. This is new info for me that a report location in a maint plan is obligatory for MSA. Thank you very much!
    – JohnnyP
    Feb 9, 2021 at 21:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.