I have the following code
SELECT tA.FieldName As [Field Name], COALESCE(tO_A.[desc], tO_B.[desc], tO_C.Name, tA.OldVAlue) AS [Old Value], COALESCE(tN_A.[desc], tN_B.[desc], tN_C.Name, tA.NewValue) AS [New Value], U.UserName AS [User Name], CONVERT(varchar, tA.ChangeDate) AS [Change Date] FROM D tA JOIN [DRTS].[dbo].[User] U ON tA.UserID = U.UserID LEFT JOIN A tO_A on tA.FieldName = 'AID' AND tA.oldValue = CONVERT(VARCHAR, tO_A.ID) LEFT JOIN A tN_A on tA.FieldName = 'AID' AND tA.newValue = CONVERT(VARCHAR, tN_A.ID) LEFT JOIN B tO_B on tA.FieldName = 'BID' AND tA.oldValue = CONVERT(VARCHAR, tO_B.ID) LEFT JOIN B tN_B on tA.FieldName = 'BID' AND tA.newValue = CONVERT(VARCHAR, tN_B.ID) LEFT JOIN C tO_C on tA.FieldName = 'CID' AND tA.oldValue = tO_C.Name LEFT JOIN C tN_C on tA.FieldName = 'CID' AND tA.newValue = tN_C.Name WHERE U.Fullname = @SearchTerm ORDER BY tA.ChangeDate
When running the code I am getting the error pasted in the title after adding the two joins for table C. I think this may have something to do with the fact I'm using SQL Server 2008 and have restored a copy of this db on to my machine which is 2005.
The SQL_Latin1_General_CP1_CI_AS collation is a SQL collation and the rules around sorting data for unicode and non-unicode data are different. The Latin1_General_CI_AS collation is a Windows collation and the rules around sorting unicode and non-unicode data are the same.
The collate clause is used for case sensitive and case insensitive searches in the columns of the SQL server. There are two types of collate clause present: SQL_Latin1_General_CP1_CS_AS for case sensitive. SQL_Latin1_General_CP1_CI_AS for case insensitive.
Set or change the database collation using SSMS Alternatively, if the database already exists, right-click the database that you want and select Properties. Select the Options page, and select a collation from the Collation drop-down list. After you are finished, select OK.
I do the following:
...WHERE fieldname COLLATE DATABASE_DEFAULT = otherfieldname COLLATE DATABASE_DEFAULT
Works every time. :)
You have a mismatch of two different collations in your table. You can check what collations each column in your table(s) has by using this query:
SELECT col.name, col.collation_name FROM sys.columns col WHERE object_id = OBJECT_ID('YourTableName')
Collations are needed and used when ordering and comparing strings. It's generally a good idea to have a single, unique collation used throughout your database - don't use different collations within a single table or database - you're only asking for trouble....
Once you've settled for one single collation, you can change those tables / columns that don't match yet using this command:
ALTER TABLE YourTableName ALTER COLUMN OffendingColumn VARCHAR(100) COLLATE Latin1_General_CI_AS NOT NULL
Marc
UPDATE: to find the fulltext indices in your database, use this query here:
SELECT fti.object_Id, OBJECT_NAME(fti.object_id) 'Fulltext index', fti.is_enabled, i.name 'Index name', OBJECT_NAME(i.object_id) 'Table name' FROM sys.fulltext_indexes fti INNER JOIN sys.indexes i ON fti.unique_index_id = i.index_id
You can then drop the fulltext index using:
DROP FULLTEXT INDEX ON (tablename)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With