Hello,
I was writing a SQL query that was joining data on the workspace URL between MSP_EpmProject_UserView and the WSS Content database URL. Since the collations of the databases were different I received the collation error mentioned in the title in this post. Fortunately with some research I was able to resolve the issue quite easily by adding the keywords “Collate Database_Default” as follows to my query:
and WEBS.FULLURL COLLATE DATABASE_DEFAULT
= (SELECT substring(ProjectWorkspaceInternalHRef, charindex('project/sites/', ProjectWorkspaceInternalHRef, 1), LEN(substring(ProjectWorkspaceInternalHRef, charindex('project/sites/', ProjectWorkspaceInternalHRef, 1), 255 )))
from ProjectServer_PWA_Reporting.dbo.msp_epmproject_userview
where ProjectUID = '1991F6CF-9F84-4907-A8D4-F211D9F5FFA2' COLLATE DATABASE_DEFAULT