Orion SWIS Query Error: Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CS_AS" in the UNION operation

equalswql

Here is my query:

SELECT DISTINCT Community as Test FROM Orion.Nodes
UNION ( Select DISTINCT RWCommunity as Test FROM Orion.Nodes)

I was looking for a function to resolve this error, but I wasn't able to find anything at https://github.com/solarwinds/OrionSDK/wiki/SWQL-Functions

This was a test to demonstrate the error, as I had a larger query I was trying to build to summarize all of the credentials from different tables in our environment. I may have another way to do this, but thought I would share this in the case it is something that can be resolved in a future release (I'm using the web console for queries @(/Orion/Admin/swis.aspx), as I currently don't have access to SWQL studio. 

Error log: 

Server error: Error: A query to the SolarWinds Information Service failed.RunQuery failed, check fault information.Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CS_AS" in the UNION operation.System.ServiceModel.FaultException`1[SolarWinds.InformationService.Contract2.InfoServiceFaultContract]: RunQuery failed, check fault information.Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CS_AS" in the UNION operation. (Fault Detail is equal to InfoServiceFaultContract [ System.Data.SqlClient.SqlException (0x80131904): Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CS_AS" in the UNION operation.   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.SqlDataReader.TryConsumeMetaData()   at System.Data.SqlClient.SqlDataReader.get_MetaData()   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCac...).

HerrDoktor

Collation conflict is something that needs to be resolved on the Database Level. Have you performed a Database Migration in the past, where you have changed the default collation of your SQL Server?

Check the property of the tables you are using in your query via SQL management Studio. There you should see the collation.

If there is a DBA responsible for the Database, speak to them about „mixed collations“ and if they can resolve this.

hth