Abstract: On one Skype for Business Frontend Server you constantly see an error message related to the QoE Adaptor which Failed to execute a stored procedure on the back-end.
Failed to execute a stored procedure on the back-end.
Component: QoE Adaptor
Stored Procedure: QoeInsertSessionReport2
Error: System.Data.SqlClient.SqlException (0x80131904): Trying to pass a table-valued parameter with 109 column(s) where the corresponding user-defined table type requires 101 column(s).
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.Rtc.Common.Data.DBCore.Execute(SprocContext sprocContext, SqlConnection sqlConnection, SqlTransaction sqlTransaction)
ClientConnectionId:fa0e732b-a040-40a4-91a2-7e1aac9c30b9
Error Number:500,State:1,Class:16
Cause: Configuration issues, an unreachable back-end or an unexpected condition has resulted in the error.
Resolution:
Verify the back-end is up and this Skype for Business Server has connectivity to it. If the problem persists, notify your organization's support team with the relevant details.
The “stored procedure” lead to the conclusion that something is not fine with the SQL Database behind that pool. To troubleshoot that we need to run a Skype for Business (SfB) powershell and run the following command:
Test-CsDatabase -ConfiguredDatabases -SqlServerFqdn <the FQND from our SQL Server for that pool>
which will output a similar export like seen below:
AlwaysOnPrimaryNodeFqdn : sql01.contoso.local
DatabaseHighAvailability : AlwaysOn
SqlServerFqdn : sql01.contoso.local
SqlInstanceName :
DatabaseName : mgc
DataSource : sql01.contoso.local
SQLServerVersion : 11.0.5343 SP2 Enterprise Edition: Core-based
Licensing (64-bit)
ExpectedVersion : 1.42.1
InstalledVersion : 1.42.1
Succeed : True
AlwaysOnPrimaryNodeFqdn : sql01.contoso.local
DatabaseHighAvailability : AlwaysOn
SqlServerFqdn : sql01.contoso.local
SqlInstanceName :
DatabaseName : rtcxds
DataSource : sql01.contoso.local
SQLServerVersion : 11.0.5343 SP2 Enterprise Edition: Core-based
Licensing (64-bit)
ExpectedVersion : 15.13.9
InstalledVersion : 15.13.9
Succeed : True
AlwaysOnPrimaryNodeFqdn : sql01.contoso.local
DatabaseHighAvailability : AlwaysOn
SqlServerFqdn : sql01.contoso.local
SqlInstanceName :
DatabaseName : rtcshared
DataSource : sql01.contoso.local
SQLServerVersion : 11.0.5343 SP2 Enterprise Edition: Core-based
Licensing (64-bit)
ExpectedVersion : 5.0.1
InstalledVersion : 5.0.1
Succeed : True
AlwaysOnPrimaryNodeFqdn : sql01.contoso.local
DatabaseHighAvailability : AlwaysOn
SqlServerFqdn : sql01.contoso.local
SqlInstanceName :
DatabaseName : rtcab
DataSource : sql01.contoso.local
SQLServerVersion : 11.0.5343 SP2 Enterprise Edition: Core-based
Licensing (64-bit)
ExpectedVersion : 62.42.12
InstalledVersion : 62.42.12
Succeed : True
AlwaysOnPrimaryNodeFqdn : sql01.contoso.local
DatabaseHighAvailability : AlwaysOn
SqlServerFqdn : sql01.contoso.local
SqlInstanceName :
DatabaseName : rgsconfig
DataSource : sql01.contoso.local
SQLServerVersion : 11.0.5343 SP2 Enterprise Edition: Core-based
Licensing (64-bit)
ExpectedVersion : 5.5.1
InstalledVersion : 5.5.1
Succeed : True
AlwaysOnPrimaryNodeFqdn : sql01.contoso.local
DatabaseHighAvailability : AlwaysOn
SqlServerFqdn : sql01.contoso.local
SqlInstanceName :
DatabaseName : rgsdyn
DataSource : sql01.contoso.local
SQLServerVersion : 11.0.5343 SP2 Enterprise Edition: Core-based
Licensing (64-bit)
ExpectedVersion : 2.2.1
InstalledVersion : 2.2.1
Succeed : True
AlwaysOnPrimaryNodeFqdn : sql01.contoso.local
DatabaseHighAvailability : AlwaysOn
SqlServerFqdn : sql01.contoso.local
SqlInstanceName :
DatabaseName : cpsdyn
DataSource : sql01.contoso.local
SQLServerVersion : 11.0.5343 SP2 Enterprise Edition: Core-based
Licensing (64-bit)
ExpectedVersion : 1.1.2
InstalledVersion : 1.1.2
Succeed : True
AlwaysOnPrimaryNodeFqdn : sql01.contoso.local
DatabaseHighAvailability : AlwaysOn
SqlServerFqdn : sql01.contoso.local
SqlInstanceName :
DatabaseName : LcsCDR
DataSource : sql01.contoso.local
SQLServerVersion : 11.0.5343 SP2 Enterprise Edition: Core-based
Licensing (64-bit)
ExpectedVersion : 39.85.10
InstalledVersion : 39.85.10
Succeed : True
AlwaysOnPrimaryNodeFqdn : sql01.contoso.local
DatabaseHighAvailability : AlwaysOn
SqlServerFqdn : sql01.contoso.local
SqlInstanceName :
DatabaseName : QoEMetrics
DataSource : sql01.contoso.local
SQLServerVersion : 11.0.5343 SP2 Enterprise Edition: Core-based
Licensing (64-bit)
ExpectedVersion : 62.93.9
InstalledVersion : 62.93.8
Succeed : True
If we check that result we can see that for any database the expected version and the installed version is the same. However NOT for the QoEMetrics DB. To fix that we need to run a new SfB powershell command:
Install-CsDatabase -DatabaseType Monitoring -SqlServerFqdn <the FQND from our SQL Server for that pool> -DatabasePaths “H:\Databases”,”G:\T-Logs”
Change the path names above so that it fits YOUR environment!
You should stop all SfB frontend services in the affected pool!
which will result in the following:
PS C:\Users\contosoadmin> Install-CsDatabase -DatabaseType Monitoring -SqlServerFqdn sql01.contoso.local -DatabasePaths "H:\Databases\","G:\T-Logs\"
****Creating DbSetupInstance for 'Microsoft.Rtc.Common.Data.MonitoringDatabase'****
Trying to connect to Sql Server sql01.contoso.local. using windows authentication...
Sql version: Major: 11, Minor: 0, Build 5343.
Sql version is acceptable.
Checking state for database LcsCDR.
****Creating DbSetupInstance for 'Microsoft.Rtc.Common.Data.QoEMetricsDatabase'****
Trying to connect to Sql Server sql01.contoso.local. using windows authentication...
Sql version: Major: 11, Minor: 0, Build 5343.
Sql version is acceptable.
Checking state for database QoEMetrics.
****Creating DbSetupInstance for 'Microsoft.Rtc.Common.Data.MonitoringDatabase'****
Trying to connect to Sql Server sql01.contoso.local. using windows authentication...
Sql version: Major: 11, Minor: 0, Build 5343.
Sql version is acceptable.
Checking state for database LcsCDR.
Database created by script "MonitoringDatabase" already exists and is current.
****Creating DbSetupInstance for 'Microsoft.Rtc.Common.Data.QoEMetricsDatabase'****
Trying to connect to Sql Server sql01.contoso.local. using windows authentication...
Sql version: Major: 11, Minor: 0, Build 5343.
Sql version is acceptable.
Checking state for database QoEMetrics.
Checking state for database QoEMetrics.
Checking state for database QoEMetrics.
State of database QoEMetrics is DbState_RequiresMinorUpgrade.
Dropping all procedures, functions and views from database QoEMetrics.
Executing QoEDb.sql...
Setting database version: Schema Version 62, Sproc Version 93, Update Version 9.
Setting owner for database QoEMetrics to sa.
Adding account ROOT\RTCComponentUniversalServices to role ServerRole.
Setting the database QoEMetrics to multi user mode.
Database QoEMetrics is set to multi user mode.
WARNING: SQL Server Agent start mode was detected as Manual. It must be Auto to ensure that jobs are executed.
Executing QoEJobs.sql...
WARNING: "Install-CsDatabase" processing has completed with warnings. "2"
warnings were recorded during this run.
WARNING: Detailed results can be found at
"C:\Users\contosoadmin\AppData\Local\Temp\2\Install-CsDatabase-638631a7-9cba-411a-a3c0-51e0e7c63bef.html".
If we now run again a:
Test-CsDatabase -ConfiguredDatabases -SqlServerFqdn <the FQND from our SQL Server for that pool>
We can see the version is the same now and the event log error message above should no longer be appear. So the issue is solved with above solution.
The error above in my example could be ignored as the SQL Server Agent is running on a cluster.
