Error when creating DPA repository failing with DPA 2024.2 with Chinese_Hong_Kong_Stroke_90_CI_AS Collation

7/25/2024 4:38 PM

2/19/2025 1:30 AM

The errors below can be seen in the wizard log after experiencing these collation errors following the creation of DPA Repo on 2024.2:
 

Repo Schema Creation Error
com.microsoft.sqlserver.jdbc.SQLServerException: The size (6000) given to the parameter 'ICON' exceeds the maximum allowed (4000).
  at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:265) ~[mssql-jdbc-12.2.0.jre11.jar:?]
  at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1695) ~[mssql-jdbc-12.2.0.jre11.jar:?]
  at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:920) ~[mssql-jdbc-12.2.0.jre11.jar:?]
  at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:814) ~[mssql-jdbc-12.2.0.jre11.jar:?]
  at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7685) ~[mssql-jdbc-12.2.0.jre11.jar:?]
  at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:4048) ~[mssql-jdbc-12.2.0.jre11.jar:?]
  at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:272) ~[mssql-jdbc-12.2.0.jre11.jar:?]
  at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:246) ~[mssql-jdbc-12.2.0.jre11.jar:?]
  at com.microsoft.sqlserver.jdbc.SQLServerStatement.execute(SQLServerStatement.java:787) ~[mssql-jdbc-12.2.0.jre11.jar:?]
  at com.confio.idc.wizard.dao.impl.ScriptDaoImpl.executeWithTimeout(SourceFile:428) ~[pi.jar:?]
  at com.confio.idc.wizard.dao.impl.ScriptDaoImpl.runDDL(SourceFile:322) ~[pi.jar:?]
  at com.confio.idc.wizard.dao.impl.ScriptDaoImpl.runDDL(SourceFile:304) ~[pi.jar:?]
  at jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:?]
  at jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[?:?]
  at jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:?]
  at java.lang.reflect.Method.invoke(Method.java:566) ~[?:?]
  at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:344) ~[spring-aop-5.3.29.jar:5.3.29]
  at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:234) ~[spring-aop-5.3.29.jar:5.3.29]
  at com.sun.proxy.$Proxy275.runDDL(Unknown Source) ~[?:?]
  at com.confio.idc.wizard.services.impl.RepositorySchemaCreationService.execute(SourceFile:127) [pi.jar:?]
  at com.confio.idc.wizard.services.impl.RepositoryCreationServiceImpl.createRepositoryImpl(SourceFile:1065) [pi.jar:?]
  at com.confio.idc.wizard.services.impl.RepositoryCreationServiceImpl$1.run(SourceFile:509) [pi.jar:?]
  at java.lang.Thread.run(Thread.java:829) [?:?]
DEBUG  (2024-05-23T14:37:34,871+0800) [RepositoryCreationThread [-591456ed:18fa41ab12d:-7ffc]]  RepositorySchemaCreationService:302 - Closing datasource [Connection Properties [databaseType=SQL Server,source=Database,host=10.15.146.113,port=1433,databaseName=dpa_dpa_test2,lastUsername=null,overrideUrl=null,socketTimeout=300, userValidationAttributes [[CREATE_REPO_WIZARD, ADMIN_USER_AVAILABLE]]]]

 

Database Performance Analyzer

This issue is due to server collation and the effect it has on the SQL Server Schema when creating a repository for DPA on 2024.2.

Resolution Steps to implement the workaround:

1.  We can stop DPA server and make below file changes.
2.   We can go to file idc_repo_base_sqlserver.sql present in  below location for Windows. 

C:\Program Files\SolarWinds\DPA\iwc\tomcat\webapps\iwc\WEB-INF\classes\resources\repository\SQLServer\idc_repo_base_sqlserver.sql

and for linux.

{DPA_HOME}/iwc/tomcat/webapps/iwc/WEB-INF/classes/resources/repository/SQLServer/idc_repo_base_sqlserver.sql

and change sql script for create table CON_CONTACT_CNS to:
 

create table [CON_CONTACT_CNS] (
  [ID]           SMALLINT  not null,
  [CONFIG_ID]    SMALLINT  not null,
  [ICON]    VARCHAR(6000)  not null,
  [SERVICE_TYPE]     VARCHAR(255)    not null,
  [SERVICE_NAME]  VARCHAR(255)    not null,
  primary key ([ID])
)
go

3. Save the file idc_repo_base_sqlserver.sql
4. Restart the server and create the repo.

-- Scripts are not supported under any SolarWinds support program or service.
-- Scripts are provided AS IS without warranty of any kind. SolarWinds further
-- disclaims all warranties including, without limitation, any implied warranties
-- of merchantability or of fitness for a particular purpose. The risk arising
-- out of the use or performance of the scripts and documentation stays with you.
-- In no event shall SolarWinds or anyone else involved in the creation,
-- production, or delivery of the scripts be liable for any damages whatsoever
-- (including, without limitation, damages for loss of business profits, business
-- interruption, loss of business information, or other pecuniary loss) arising
-- out of the use of or inability to use the scripts or documentation.