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.
This question is related to
sql-server
Identify the fields for which it is throwing this error and add following to them: COLLATE DATABASE_DEFAULT
There are two tables joined on Code field:
...
and table1.Code = table2.Code
...
Update your query to:
...
and table1.Code COLLATE DATABASE_DEFAULT = table2.Code COLLATE DATABASE_DEFAULT
...
Check the level of collation that is mismatched (server, database,table,column,character).
If it is the server, these steps helped me once:
Run this command:
sqlservr -m -T4022 -T3659 -s"name_of_insance"
-q "name_of_collation"
Start your sql server:
net start name_of_instance
Check the collation of your server again.
Here is more info:
https://www.mssqltips.com/sqlservertip/3519/changing-sql-server-collation-after-installation/
ALTER DATABASE test2 --put your database name here
COLLATE Latin1_General_CS_AS --replace with the collation you need
Thanks to marc_s's answer I solved my original problem - inspired to take it a step further and post one approach to transforming a whole table at a time - tsql script to generate the alter column statements:
DECLARE @tableName VARCHAR(MAX)
SET @tableName = 'affiliate'
--EXEC sp_columns @tableName
SELECT 'Alter table ' + @tableName + ' alter column ' + col.name
+ CASE ( col.user_type_id )
WHEN 231
THEN ' nvarchar(' + CAST(col.max_length / 2 AS VARCHAR) + ') '
END + 'collate Latin1_General_CI_AS ' + CASE ( col.is_nullable )
WHEN 0 THEN ' not null'
WHEN 1 THEN ' null'
END
FROM sys.columns col
WHERE object_id = OBJECT_ID(@tableName)
gets: ALTER TABLE Affiliate ALTER COLUMN myTable NVARCHAR(4000) COLLATE Latin1_General_CI_AS NOT NULL
I'll admit to being puzzled by the need to col.max_length / 2 -
To resolve this problem in the query without changing either database, you can cast the expressions on other side of the "=" sign with
COLLATE SQL_Latin1_General_CP1_CI_AS
as suggested here.
This can easily happen when you have 2 different databases and specially 2 different databases from 2 different servers. Best option is to change it to a common collection and do the join or comparison.
SELECT
*
FROM sd
INNER JOIN pd ON sd.SCaseflowID COLLATE Latin1_General_CS_AS = pd.PDebt_code COLLATE Latin1_General_CS_AS
If this occurs across the whole of your DB then it's better to change your DB collation like so:
USE master;
GO
ALTER DATABASE MyOptionsTest
COLLATE << INSERT COLATION REQUIRED >> ;
GO
--Verify the collation setting.
SELECT name, collation_name
FROM sys.databases
WHERE name = N'<< INSERT DATABASE NAME >>';
GO
Reference here
For those who have a CREATE DATABASE script (as was my case) for the database that is causing this issue you can use the following CREATE script to match the collation:
-- Create Case Sensitive Database
CREATE DATABASE CaseSensitiveDatabase
COLLATE SQL_Latin1_General_CP1_CS_AS -- or any collation you require
GO
USE CaseSensitiveDatabase
GO
SELECT *
FROM sys.types
GO
--rest of your script here
or
-- Create Case In-Sensitive Database
CREATE DATABASE CaseInSensitiveDatabase
COLLATE SQL_Latin1_General_CP1_CI_AS -- or any collation you require
GO
USE CaseInSensitiveDatabase
GO
SELECT *
FROM sys.types
GO
--rest of your script here
This applies the desired collation to all the tables, which was just what I needed. It is ideal to try and keep the collation the same for all databases on a server. Hope this helps.
More info on the following link: SQL SERVER – Creating Database with Different Collation on Server
I have had something like this before, and what we found was that the collation between 2 tables were different.
Check that these are the same.
Use the collate clause in your query:
LEFT JOIN C tO_C on tA.FieldName = 'CID' AND tA.oldValue COLLATE Latin1_General_CI_AS = tO_C.Name
I may not have the syntax exactly right (check BOL), but you can do this to change the collation on-the-fly for the query - you may need to add the clause for each join.
edit: I realized this was not quite right - the collate clause goes after the field you need to change - in this example I changed the collation on the tA.oldValue field.
You may not have any collation issues in your database whatsoever, but if you restored a copy of your database from a backup on a server with a different collation than the origin, and your code is creating temporary tables, those temporary tables would inherit collation from the server and there would be conflicts with your database.
I had a similar requirement; documenting my approach here for anyone with a similar scenario...
Use SQL Server Schema Comparison (from SQL Server Data Tools / Visual Studio) to compare source (clean install) with destination (the db with invalid collation).
In my case I compared the two DBs directly; though you could work via a project to allow you to manually tweak pieces in between...
Object Types select only those types you're interested in (for me it was only Views and Tables)General select:
DELETE folder and selecting EXCLUDE.CREATE objects (here since they don't exist in the target they can't have the wrong collation there; whether they should exist is a question for another topic).Update to push changesThis does still involve some manual effort (e.g. checking that you're only impacting the collation) - but it handles dependencies for you.
Also you can keep a database project of the valid schema so you can use a universal template for your DBs should you have more than 1 to update, assuming all target DBs should end up with the same schema.
You can also use find/replace on the files in a database project should you wish to mass amend settings there (e.g. so you could create the project from the invalid database using schema compare, amend the project files, then toggle the source/target in the schema compare to push your changes back to the DB).
INSERT INTO eSSLSmartOfficeSource2.[dbo].DeviceLogs (DeviceId,UserId,LogDate,UpdateFlag)
SELECT DL1.DeviceId ,DL1.UserId COLLATE DATABASE_DEFAULT,DL1.LogDate
,0 FROM eSSLSmartOffice.[dbo].DeviceLogs DL1
WHERE NOT EXISTS
(SELECT DL2.DeviceId ,DL2.UserId COLLATE DATABASE_DEFAULT
,DL2.LogDate ,DL2.UpdateFlag
FROM eSSLSmartOfficeSource2.[dbo].DeviceLogs DL2
WHERE DL1.DeviceId =DL2.DeviceId
and DL1.UserId collate Latin1_General_CS_AS=DL2.UserId collate Latin1_General_CS_AS
and DL1.LogDate =DL2.LogDate )
Added code to @JustSteve's answer to deal with varchar and varchar(MAX) columns:
DECLARE @tableName VARCHAR(MAX)
SET @tableName = 'first_notes'
--EXEC sp_columns @tableName
SELECT 'Alter table ' + @tableName + ' alter column ' + col.name
+ CASE ( col.user_type_id )
WHEN 231
THEN ' nvarchar(' + CAST(col.max_length / 2 AS VARCHAR) + ') '
WHEN 167
THEN ' varchar(' + CASE col.max_length
WHEN -1
THEN 'MAX'
ELSE
CAST(col.max_length AS VARCHAR)
end
+ ') '
END + 'collate Latin1_General_CI_AS ' + CASE ( col.is_nullable )
WHEN 0 THEN ' not null'
WHEN 1 THEN ' null'
END
FROM sys.columns col
WHERE object_id = OBJECT_ID(@tableName)
The root cause is that the sql server database you took the schema from has a collation that differs from your local installation. If you don't want to worry about collation re install SQL Server locally using the same collation as the SQL Server 2008 database.
@Valkyrie awesome answer. Thought I put in here a case when performing the same with a subquery insides a stored procedure, as I wondered if your answer works in this case, and it did awesome.
...WHERE fieldname COLLATE DATABASE_DEFAULT in (
SELECT DISTINCT otherfieldname COLLATE DATABASE_DEFAULT
FROM ...
WHERE ...
)
I do the following:
...WHERE
fieldname COLLATE DATABASE_DEFAULT = otherfieldname COLLATE DATABASE_DEFAULT
Works every time. :)
I have used the content from this site to create the following script which changes collation of all columns in all tables:
CREATE PROCEDURE [dbo].[sz_pipeline001_collation]
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SELECT 'ALTER TABLE [' + SYSOBJECTS.Name + '] ALTER COLUMN [' + SYSCOLUMNS.Name + '] ' +
SYSTYPES.name +
CASE systypes.NAME
WHEN 'text' THEN ' '
ELSE
'(' + RTRIM(CASE SYSCOLUMNS.length
WHEN -1 THEN 'MAX'
ELSE CONVERT(CHAR,SYSCOLUMNS.length)
END) + ') '
END
+ ' ' + ' COLLATE Latin1_General_CI_AS ' + CASE ISNULLABLE WHEN 0 THEN 'NOT NULL' ELSE 'NULL' END
FROM SYSCOLUMNS , SYSOBJECTS , SYSTYPES
WHERE SYSCOLUMNS.ID = SYSOBJECTS.ID
AND SYSOBJECTS.TYPE = 'U'
AND SYSTYPES.Xtype = SYSCOLUMNS.xtype
AND SYSCOLUMNS.COLLATION IS NOT NULL
AND NOT ( sysobjects.NAME LIKE 'sys%' )
AND NOT ( SYSTYPES.name LIKE 'sys%' )
END
You could easily do this by using 4 easy steps
error (Cannot resolve the collation conflict between .... ) usually occurs while comparing data from multiple databases.
since you cannot change the collation of databases now, use COLLATE DATABASE_DEFAULT.
----------
AND db1.tbl1.fiel1 COLLATE DATABASE_DEFAULT =db2.tbl2.field2 COLLATE DATABASE_DEFAULT
here is what we did, in our situation we need an ad hoc query to be executed using a date restriction on demand, and the query is defined in a table.
Our new query needs to match data between different databases and include data from both of them.
It seems that the COLLATION is different between the db that imports data from the iSeries/AS400 system, and our reporting database - this could be because of the specific data types (such as Greek accents on names and so on).
So we used the below join clause:
...LEFT Outer join ImportDB..C4CTP C4 on C4.C4CTP COLLATE Latin1_General_CS_AS=CUS_Type COLLATE Latin1_General_CS_AS
In the where criteria add collate SQL_Latin1_General_CP1_CI_AS
This works for me.
WHERE U.Fullname = @SearchTerm collate SQL_Latin1_General_CP1_CI_AS
Source: Stackoverflow.com