SQL Sqlps Execution Ad-sqlavailabilitydatabase exception

SQL Server sqlps Execution Ad-sqlavailabilitydatabase encountered a problem

This error is a reverse of the day, check the help of the cmdlet

There is debug in the general parameters.

To perform debug, the output is as follows:

PS c:\windows\system32> add-sqlavailabilitydatabase-inputobject $ag-database $DatabaseList-debug

Tune ì?? Test o?:  (add-sqlavailabilitydatabase)  in method processrecord. resolving targets. ì ?? Trial o?:  (add-sqlavailabilitydatabase)  Resolving targets: Parameter Set =  ' Byobject ' tune ì?? Try o?:  (add-sqlavailabilitydatabase)  resolved target [app1on] tune ì?? Try o?:  (add-sqlavailabilitydatabase)  getshouldprocesstargetstring in sqlcmdlet. ì?? Try o?:  (add-sqlavailabilitydatabase)  validating target tune ì?? Trial o?:  (add-sqlavailabilitydatabase)  ValidateTarget in SqlCmdlet. Returning  True. Tune ì?? Trial o?:  (add-sqlavailabilitydatabase)  Target is valid. Calling  begintargetprocessing. Tune ì?? Try o?:  (add-sqlavailabilitydatabase)  in begintargetprocessing tune ì?? Try o?:  (add-sqlavailabilitydatabase)  subscribing to server events: infomesage,  statementexecuted tune ì?? Trial o?:  (add-sqlavailabilitydatabase)  done with beginTargetprocessing. calling processtarget. Detailed ¨o?é:select * into  #tmpag_availability_ Groups from master.sys.availability_groupsselect agstates.group_id, agstates.primary_ replica into  #tmpag_availability_group_states  from master.sys.dm_hadr_availability_group_ states as agstatesselect group_id, replica_id, replica_metadata_id into # Tmpag_availability_replicas from master.sys.availability_replicasselect replica_id, is_ local, role into  #tmpag_availability_replica_states  from master.sys.dm_hadr_ Availability_replica_statesselectag.name as [name],ag.group_id as [uniqueid],isnull ( ag.automated_backup_preference, 4)  as [automatedbackuppreference],isnull (AG.failure_condition_ level, 6)  as [failureconditionlevel],isnull (ag.health_check_timeout, -1)  AS [ Healthchecktimeout],isnull (Agstates.primary_replica,  ')  as [primaryreplicaservername],isnull (arstates2.role, 3)  AS [ Localreplicarole],ar2.replica_metadata_id as [id],isnull (ag.basic_features, 0)  AS [ Basicavailabilitygroup],isnull (ag.db_failover, 0)  as [databasehealthtrigger],isnull (AG.dtc_ support, 0)  as [dtcsupportenabled],isnull (ag.is_distributed, 1)  AS [ Isdistributedavailabilitygroup],isnull (ag.cluster_type, 0)  as [clustertype],isnull (AG.required_ copies_to_commit, 0)  AS [RequiredCopiesToCommit]FROM#tmpag_availability_groups AS  agleft outer join  #tmpag_availability_group_states  as agstates on ag.group_ id = agstates.group_idinner join  #tmpag_availability_replicas  as ar2 on  AG.group_id = AR2.group_idINNER JOIN  #tmpag_availability_replica_states  as  arstates2 on ar2.replica_id = arstates2.replica_id and arstates2.is_local = 1where ([email protected]_ MSPARAM_0) drop table  #tmpag_availability_groupsdrop  table  #tmpag_availability_group_ statesdrop table  #tmpag_availability_replicasdrop  table  #tmpag_availability_replica_ States detailed ¨o?é:drop table  #tmpag_availability_groupsdrop  table  #tmpag_availability_group _statesdrop table  #tmpag_availability_replicasdrop  table  #tmpag_availability_replica_ States tune ì?? Trial o?:  (add-sqlavailabilitydatabase)  Exception occurred  microsoft.sqlserver.management.common.executionfailureexception: ? Line d transact-sql  language?? Sentence or ¨° batch ¨ 2 place? | Daniel ¤¨a when O?à hair ¤?é |¨2 ￠? °¨ ê,?ê ---> System.Data.SqlClient.SqlException:  column ￠d name?  ' Required_ Copies_to_commit '   no T-effect ì ê in ¨2 microsoft.sqlserver.management.common.connectionmanager.executetsql ( Executetsqlaction action, object execobject, dataset filldataset, boolean catchexception) in ¨2  Microsoft.SqlServer.Management.Common.ServerConnection.GetExecuteReader (Sqlcommand command)---  internal ¨ 2 department? °¨, ê heap, ¨2 á¨′ ì, ¨￠ tail 2 ---in ¨2  Microsoft.SqlServer.Management.Common.ServerConnection.GetExecuteReader (Sqlcommand command) in ¨2  Microsoft.SqlServer.Management.Smo.ExecuteSql.GetDataReader (string query, sqlcommand&  command) at ¨2 microsoft.sqlserver.management.smo.dataprovider.setconnectionandquery (ExecuteSql execSql ,  stringquery) at ¨2 microsoft.sqlserver.management.smo.executesql.getdataprovider (StringCollection  QUERY, OBJECT CON,STATEMENTBUILDER SB, RETRIVEMODE RM) in ¨2  Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillData (Resulttype resulttype, stringcollectionsql ,  OBJECT CONNECTIONINFO, STATEMENTBUILDER SB) in ¨2  Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillDataWithUSefailure (Sqlenumresult sqlresult,resulttype resulttype) in ¨2  Microsoft.SqlServer.Management.Smo.SqlObjectBase.BuildResult (Enumresult result) in ¨2  Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData () in ¨2  Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData (REQUEST REQ, OBJECT CI) in ¨2  Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.GetData (object connectioninfo, request  Request) at ¨2 microsoft.sqlserver.management.smo.executionmanager.getenumeratordatareader (Request req) In ¨2 microsoft.sqlserver.management.smo.sqlsmoobject.getinitdatareader (string[] fields, orderby[ ] orderby) in ¨2 microsoft.sqlserver.management.smo.sqlsmoobject.implinitialize (String[] fields,  orderby[] orderby) in ¨2 microsoft.sqlserver.management.smo.sqlsmoobject.initialize (Boolean  allproperties) in ¨2 microsoft.sqlserver.management.smo.sqlsmoobject.onpropertymissing (String propname,   Boolean usedefaultvalue) in ¨2 microsoft.sqlserver.management.smo.propertycollection.retrieveproperty ( Int32 index, boolean usedefaultonmissingvalue) in ¨2  Microsoft.SqlServer.Management.Smo.PropertyCollection.GetValueWithNullReplacement (String propertyname,  boolean throwonnullvalue, boolean usedefaultonmissingvalue) in ¨2  Microsoft.SqlServer.Management.Smo.AvailabilityGroup.get_PrimaryReplicaServerName () in ¨2  Microsoft.SqlServer.Management.PowerShell.Hadr.CmdletUtilities.IsReplicaPrimary (availabilitygroupag,  Smorecordcontext context) in ¨2  Microsoft.SqlServer.Management.PowerShell.Hadr.AddSqlAvailabilityGroupDatabaseCommand.ProcessTarget ( Availabilitygroup target, smorecordcontext context) in ¨2  Microsoft.SqlServer.Management.PowerShell.SqlCmdlet ' 1.ProcessRecord () tune ì?? Try o?:  (add-sqlavailabilitydatabase)  calling endprocessing. ì?? Trial o?:  (add-sqlavailabilitydatabase)  IN ENDTARGETPRocessing tune ì?? Try o?:  (add-sqlavailabilitydatabase)  unsubscribing from server events: infomesage,  StatementExecutedAdd-SqlAvailabilityDatabase : ?? Line d transact-sql  language?? Sentence or ¨° batch ¨ 2 place? | Daniel ¤¨a when O?à hair ¤?é |¨2 ￠? °¨ ê, ê in ¨′ 2-bit? ¨?  word d:1  á inputobject  $ag  -Database  $DatabaseList  -debug+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~+ categoryinfo : notspecified:  (:)  [ add-sqlavailabilitydatabase], executionfailureexception+ fullyqualifiederrorid :  Microsoft.SqlServer.Management.Common.ExecutionFailureException, Microsoft.SqlServer.Management.PowerShell.Hadr.AddSqlAvailabilityGroupDatabaseCommand

The core error message is as follows:

Commissioning: (add-sqlavailabilitydatabase) Exception occurred Microsoft.SqlServer.Management.Common.ExecutionFa

Ilureexception: An exception occurred while executing a Transact-SQL statement or batch. ---> System.Data.SqlClient.SqlException: Column name ' Required_copies_to_commit ' is not valid.

To extract the relevant statements separately to execute:

select * into  #tmpag_availability_groupsfrom  master.sys.availability_groupsselect  agstates.group_id, agstates.primary_replica into  #tmpag_availability_group_statesfrom   master.sys.dm_hadr_availability_group_states as agstatesselect group_id, replica_id,  replica_metadata_id into  #tmpag_availability_replicasfrom  master.sys.availability_ replicasselect replica_id, is_local, role into  #tmpag_availability_replica_statesfrom  master.sys.dm_hadr_availability_replica_statesselectag.name as [name],ag.group_id as  [uniqueid],isnull (ag.automated_backup_preference, 4)  as [automatedbackuppreference], ISNULL (ag.failure_condition_level, 6)  as [failureconditionlevel],isnull (AG.health_check_timeout ,  -1)  as [healthchecktimeout],isnull (agstates.primary_replica,  ")  AS [ Primaryreplicaservername],isnull (arstates2.role, 3)  as [localreplicarole],ar2.replica_metadata_id as [id],isnull (AG.basic_features,  0)  as [basicavailabilitygroup],isnull (ag.db_failover, 0)  as [ Databasehealthtrigger],isnull (ag.dtc_support, 0)  as [dtcsupportenabled],isnull (AG.is_ distributed, 1)  as [isdistributedavailabilitygroup],isnull (ag.cluster_type, 0)  AS  [clustertype],isnull (ag.required_copies_to_commit, 0)  as [requiredcopiestocommit]from# tmpag_availability_groups as agleft outer join  #tmpag_availability_group_states   as agstates on ag.group_id = agstates.group_idinner join  #tmpag_ availability_replicas as ar2 on ag.group_id = ar2.group_idinner join # tmpag_availability_replica_states as arstates2 on ar2.replica_id =  Arstates2.replica_id and arstates2.is_local = 1where(ag.name= ' app1on ') drop table  #tmpag_availability_groupsdrop  table  #tmpag_availability_ group_statesdrop table  #tmpag_availability_replicasdrop  table  #tmpag_availability_replica_ States

The following error is obtained:

MSG 207, Level 16, State 1, line 10th

Column name ' Required_copies_to_commit ' is not valid.

In the statement

Used to be

Master.sys.availability_groups

In the Required_copies_to_commit column, in fact the system view does not have the column at all, only the Required_synchronized_secondaries_to_commit column.

Check the following online have the same problem:

https://social.msdn.microsoft.com/Forums/azure/en-US/3e5db95a-0231-4e29-b0c0-68c4d8e9583d/ Exception-occurred-microsoftsqlservermanagementcommonexecutionfailureexception?forum=sqltools

SQL Server 2017 that is currently running does not have time to update sqlps.

• Try to hit the patch

Download the latest cumulative patch CU6 from https://support.microsoft.com/en-us/help/4047329, install Sqlserver2017-kb4101464-x64.exe. The Sqlps module is not actually updated. The problem still exists.

“

There is the SQL Server PowerShell modules; SQL Server and SQLPS. The SQLPS module is included with the SQL Server installation (for backwards compatibility), but is no longer bei ng updated. The most up-to-date PowerShell module is the SQL Server module. The SQL Server module contains updated versions of the cmdlets in SQLPS, and also includes new cmdlets to Support for the latest SQL features.
Previous versions of the SQL Server module were included with SQL servers Management Studio (SSMS), but on Ly with the 16.x versions of SSMS. To use PowerShell with SSMS 17.0 and later, the SQL Server module must is installed from the PowerShell Gallery. To install the SQL Server module.

”

• Attempt to install SQL Server module

Download the Save module first:

Save-module-name Sqlserver-path C:\powershellgallery
Copy the downloaded SQL Server module folder to%programfiles%/windowspowershell/module/.

Load-ready, time-to-register:

Import-module-name SQL Server

After using the new SQL Server module, you are not experiencing problems.

SQL Sqlps Execution Ad-sqlavailabilitydatabase exception