Implications of changing SQL Server collation
I posted a detailed analysis of the implications of making any collation changes to an instance and/or database:

Changing the Collation of the Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong?

There are quite a few areas that could be affected, and it depends greatly on if you are talking just about one particular database, or the instance and one or more of the databases on that instance.

HOWEVER, perhaps in addition to asking what might the affect of such a change be, you should probably also be asking the vendor:


Why they are making this recommendation? If they do not have a very specific reason for it, then maybe you shouldn't do it.
What is the specific recommendation: is it to make the instance case-sensitive, or just this particular database?
Is the vendor installing objects into an existing database, or do they create their own? If they create their own database, they can ensure that it is case-sensitive by setting the collation when the database is created. And if they are installing into an existing database, they can simply use the COLLATE clause in the CREATE TABLE statements to override the default from the database; they can use the COLLATE clause in any expression to override the database's default collation.
Why are they using obsolete SQL Server collations instead of the newer, more appropriate Windows collations? For anyone using SQL Server 2008 or newer , Latin1_General_100_CS_AS_SC would be the preferred equivalent of SQL_Latin1_General_CP1_CS_AS.