PHISSUG

hi guys, whe previously migrate our databse to 2005, but suddenly we received error, can u help us configure it out, heres the error:

 

Command attempted:

-- =============================================
-- Author: <>
-- Create date: <May 8, 2008>
-- Description: <Delay Summary>
-- =============================================
CREATE FUNCTION "dbo"."fn_delaySummary" (@lendingInstitution char(10), @startDate datetime, @endDate dateTime)
RETURNS @delaySummaryReport TABLE(valuationFirmName char(100), totalDelayRequest int, atCustRequest int, missingDocs int, isolatedLocation int,
tenantDeniedAccess int, accessIssue int, awaitingBldgInfo in
(Transaction sequence number: 0x00019AB200003E47010700000000, Command ID: 441)

Error messages:

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation. (Source: MSSQLServer, Error number: 468)
Get help: http://help/468

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation. (Source: MSSQLServer, Error number: 468)
Get help: http://help/468

 Thanks

Obviously you had varchar or nvarchar fields that depended on collation.

You have to check the default collation of your SQL Server database and the collation that is set in your CREATE TABLE statements (with the COLLATE clause). It's possible that these were different such that when you created the database on VS2005, you had the collation set to SQL_Latin1_General... while your CREATE TABLE scripts had Latin1_General... as value in its COLLATE statements.

 To solve this, check the Properties of your SQL Server database and then set the collation to the other, e.g., if the database has "SQL_Latin1..." as its collation, set it to "Latin1..." or if it has "Latin1..." as collation, set it to "SQL_Latin1...". This should solve the problem
 

could you post the actual query statement? You also might want to see the collations of columns/objects that are involved in your "=" confition. Both might have different different collation orders (either explicitly defined in the statement or via server/database/table settings). there are many possible causes for your error. Hope you can post more info on this.

How can i check the properties of the SQL Server database?

using SSMS, right click on the instance of sql server and click on properties and see Server Collation. To see database level collation, right click on the database and click on properties, then see collation. To see, table level collation, right click on table, click on properties, and click on the Extended Properties page. do thesame thing with a Column by selecting a column first.

The problem with incorrect collation is that you have no other choice but to reinstall SQL Server with the correct collation setting...or do it the quick and dirty way - install a separate SQL Server instance on a test machine, copy the MODEL database from the test instance to the working one and overwrite. TEMPDB collation setting is taken from the MODEL database dring server restarts.  I'm not saying this is supported, all I'm saying is it works :-)

---

Check out SQL Server tips at http://www.mssqltips.com
Check out SQL Server 2008 videos at
http://blogcastrepository.com

as an alternative you can have Dictionary Order Case InSensitive (DOCI) Collation settings