Collation Conflict After Configuring Distribution SQLServer 2005
May 5, 2006
Hi
I (absolute beginner) configured a distribution on a publisher wich acts as its own distributor. I did it by right click on replication "configure distribution", followed all instructions and chose all default values. Nothing more. But now I want to view the distributor properties and following message appears:
SQL Server is unable to complete the Distributor Properties.
ZUSÄTZLICHE INFORMATIONEN:
Ausnahme beim Ausführen einer Transact-SQL-Anweisung oder eines Transact-SQL-Batches. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation. (Microsoft SQL Server, Fehler: 468)
My collegue has no problems viewing these properties. We have both XP and SQLServer 2005 SP1. What is the reason for this error message? And how can I solve it?
I have just upgraded to SQL Server 2005 from SQL Server 2000.In Microsoft SQL Server Management Studio, when I click on databaseproperties, I receive the following error:-Cannot resolve the collation conflict between"Latin1_General_CI_AS"and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.(Microsoft SQL Server, Error: 468)Some reference suggest that I can change the database collation byclicking database properties!What can I do?
why do i get collation conflict when i used temp table ??Cannot resolve the collation conflict between "Latin1_General_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.i solved it by using COLLATE Latin1_General_CI_AS (the column name)will i have collation conflicts again when i put my web app on a web hosting company??
I have a db server which was installed with Collation 'SQL_Latin_General_CP1_CS_AS' and now I have to replace this server with a new server and I want to install this server with Collation 'SQL_Latin_General_CP1_CI_AS'.
I am using Linked Servers to collect some data from my Sybase database which has "Code Page 850 (Multilingual) character set,us_english,Binary ordering, for use with Code Page 850 (cp850)."
But with new SQL server(case-insensitive settings), I am unable to run a select query (joining a local server and sybase server) and I get an error message "cannot resolve collation conflict for equal to operation"
I have tried changing property of linked server by specifying Remote server collation to true and Collation Name as 'SQL_Latin_General_CP1_CS_AS' as well as 'SQL_Latin_General_CP850_Bin' but I do get the same error message.
Could any of you please help me in this regard ?
Thanks in advance.
Wilson
Edit: Sorted out. Somehow it is working now after specifying Remote server collation to true and Collation Name as 'SQL_Latin_General_CP1_CS_AS'
Moving code from Sybase to SQL Server 2005. I have two tables. One table (vINID) is reset and populated right before this code runs. The other table (vINPA) is a table in the database used by the Front End App. This is the error I get when I try and run on our SQL Server 2005.
Msg 468, Level 16, State 9, Line 1 Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_BIN" in the equal to operation.
Here is the SQL code:
SELECT DISTINCT vINID.BLEI_CK, vINID.BLIV_ID, ISNULL(vINPA.RCPT_AMT,0) AS RCPT_AMT
FROM Reporting_DEV.dbo.RPT_04597_INID AS vINID -- table re-populated
LEFT JOIN fauafpr0_pids.dbo.CDS_INPA_PMT_ACT AS vINPA ON vINPA.BLIV_ID = vINID.BLIV_ID
While I execute one query its showing this error: Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Arabic_CI_AS" in the equal to operation.
I have installed SQL Server 2005 Express (I have also kep my SQL Server 2000 installation), created a blank database (using the <default> Collation which I believe is Latin1_General_CI_AS), and restored a backup (from SQL Server 2000) into the newly created database.
All the tables and records appear to be there but I get the following error when trying to make any changes to the database (through my front end application that sits over the database).
Unable to get objects. Additional information: Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation..
Looking at the properties of the database, the collation is SQL_Latin1_General_CP1_CI_AS. I have tried changing this to Latin1_General_CI_AS with no success.
Am I supposed to set the collation when I create the empty database before restoring the backup into it or is there something I am missing?
hi allin my local machine there was no problem. however, when i put my database on the the hosting company's database server i got this error System.Data.SqlClient.SqlException: Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AI" in the equal to operationplease help it's urgent thanks
UPDATE #TMPC SET EsaItem = ItemClass FROM #TMPC A, dbo.EisAdj_ItemClass_V B WHERE A.EsaItem = B.ItemClassCODE
This code is part of my procedure. I always get error--Server: Msg 446, Level 16, State 9, Procedure GetAdjDataFromShipment, Line 282 Cannot resolve collation conflict for equal to operation. when program execute at this section. I try to add this statement "COLLATE SQL_Latin1_General_CP1_CI_AS", but it seems not working. I do not know why!
Okely dokely, here it is.I have a database that has a differing collation to that of the tempdb. And obviously because of this I've run into problems when referencing table variables and temp tables. BUT! Given the following example, what am I doing wrong, or is there no solution to this. <Start Example>/*================================================= ================================================== ==========================fnPM_ForeignKey ================================================== ================================================== =========================*/IF exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fnPM_ForeignKey]')) BEGINDROP FUNCTION [dbo].[fnPM_ForeignKey]ENDGOCREATE FUNCTION fnPM_ForeignKey (@ChildTable as sysname, @ChildColumn as sysname)RETURNS @ForeignKey TABLE (FKName sysname,ParentTable sysname,ParentColumn sysname,DescriptionColumn sysname COLLATE SQL_Latin1_General_CP1_CI_AS)ASBEGININSERT INTO @ForeignKey (FKName, ParentTable, ParentColumn)SELECT FK.Name, Parent.Name, ParentCol.NameFROM sysforeignkeysINNER JOIN sysobjects FK on sysforeignkeys.constid = FK.IDINNER JOIN sysobjects Parent on sysforeignkeys.rkeyid = Parent.idINNER JOIN syscolumns ParentCol on ParentCol.id = Parent.id and sysforeignkeys.rkey = ParentCol.colidINNER JOIN sysobjects Child on sysforeignkeys.fkeyid = Child.idINNER JOIN syscolumns ChildCol on ChildCol.id = Child.id and sysforeignkeys.fkey = ChildCol.colid WHERE Child.Name = @ChildTable and ChildCol.Name = @ChildColumn--> UPDATE @ForeignKey Set DescriptionColumn = syscolumns.Name COLLATE SQL_Latin1_General_CP1_CI_ASFROM @ForeignKey ForeignKeyINNER JOIN sysobjects on ForeignKey.ParentTable = sysobjects.NameINNER JOIN syscolumns on syscolumns.id = sysobjects.idWHERE syscolumns.Name like '%Name%'UPDATE @ForeignKey Set DescriptionColumn = syscolumns.NameFROM @ForeignKey ForeignKeyINNER JOIN sysobjects on ForeignKey.ParentTable = sysobjects.NameINNER JOIN syscolumns on syscolumns.id = sysobjects.idWHERE ForeignKey.DescriptionColumn is null and syscolumns.Name like '%Description%'RETURN ENDGO< end example >So here I'm defining my function which basically returns the parent table details of a given foreign key relationship. But when trying to run the above script I get this,Server: Msg 446, Level 16, State 9, Procedure fnPM_ForeignKey, Line 22Cannot resolve collation conflict for equal to operation.First up I find this weird because isn't sysname equvalent to nvarchar(128)? And aren't nvarchar fields independant of collation? But it gets better,I thought, ok maybe I'm wrong about sysname needing collation, so I figured I'll check the collation of the syscolumns.name column,Heres the queryselect sysobjects.Name, syscolumns.name, syscolumns.collationfrom sysobjects inner join syscolumns on sysobjects.id = syscolumns.idwhere sysobjects.name ='syscolumns' and syscolumns.name = 'name'This is what it returns,syscolumnsnameSQL_Latin1_General_CP1_CI_ASSo, let me get this straight, I've explicitly stated that the table variable column is collation SQL_Latin1_General_CP1_CI_ASand the column I'm comparing it to also has a collation of SQL_Latin1_General_CP1_CI_AS, and it doesn't work. . . .I smell Microsoft, or is there something equally as hideous going on here.RegardsAdam.
I am getting the following error message when trying to run a script: "Cannot resolve collation conflict for equal to operation."
Any help on resolving this would be most appreciated, thanks!
my SQL code is as follows:
CREATEVIEW IP_SLAM_EXTRACT_1 AS SELECT LEFT(a.provider,3) AS [Trust Code], CASEWHEN a.practice_code='F86687'THEN 'F86723' WHEN a.practice_code='F86733'THEN 'F86068' WHEN a.practice_code='F86677' OR a.practice_code='F86693'THEN 'F86675' WHEN a.practice_code='F86714' OR a.practice_code='F86129'THEN 'F86634' WHEN a.practice_code='F86050'THEN 'Y00090' WHEN a.practice_code='F86669' OR a.practice_code='F86662'THEN 'F86702' WHEN a.practice_code='F886051' THEN 'F86634' WHEN a.practice_code='F86059' OR a.practice_code='F86055'THEN 'F86060' WHEN a.practice_code='F86690'THEN 'F86637' WHEN a.practice_code='F86089'THEN 'F86060' WHEN a.practice_code='F86717' OR a.practice_code='F86728'THEN 'F86716' WHEN a.practice_code='F86727' OR a.practice_code='F86728'THEN 'F86721' WHEN a.practice_code IS NULL AND a.postcode NOT IN (SELECT DISTINCT p.POSTCODE FROM REPOSITORY..[Exeter PostCode]p)THEN 'Null_not_5NA' WHEN a.practice_code IS NULL AND a.postcode IN (SELECT DISTINCT p.POSTCODE FROM REPOSITORY..[Exeter PostCode]p)THEN 'Null_5NA'
ELSE a.practice_code END AS [GP Practice], LEFT( CASE WHEN a.treatment_function_code LIKE 'mis%' OR a.treatment_function_code LIKE '?%' OR a.treatment_function_code LIKE '#%' THEN a.main_specialty_code WHEN a.treatment_function_code ='' OR a.treatment_function_code IS NULL AND a.main_specialty_code IS NOT NULL THEN a.main_specialty_code WHEN a.treatment_function_code ='' OR a.treatment_function_code IS NULL AND a.main_specialty_code IS NULL THEN '999' WHEN a.treatment_function_code LIKE '0%'THEN '999' ELSE a.treatment_function_code END,3) AS [Specialty Code], a.hrg_code_calculated AS [HRG Code], a.local_patient_identifier as [Patient id Code], a.patient_classificationAS [Pat Class], a.admission_method AS [Adm Method], a.spell_discharge_date, a.consultant_code, a.diagnosis_primary_icd, a.gp AS [GP Code], a.length_of_stay AS [Spell LOS], --pat_type, --prv_typ, a.spell_special_service AS Spell_Service_ID, a.date_of_birth, a.administrative_category, a.cds_type --FROMSUS_5NA_NEW_0607 a-- FROM SUS_apc_5NA_view a WHEREa.fin_month < 4 AND a.fin_year = 2008 ANDa.main_episode='M' ANDa.pbr_exclusion IS NULL ANDa.pct_calculated = '5NA' ANDa.commissioner LIKE '5NA%' ANDa.Spell_Discharge_Date<'30/06/2007'
Hi,The tempdb db is having different collation than the application db.I rebuilt the master db with the appropriate collation after backing upmaster, model, msdb, appln databases.On restore of the databases (master, model, msdb, appln) from the backuprestores the backed up database collation.Is there any idea to restore the db with different collation than thebacked up collation.Thanks,*** Sent via Developersdex http://www.developersdex.com ***
I'm getting the following errors when trying to execute the following script on the server, its part of the standard asp.net membership and roles, anybody have any ideas how I get get round this?
Msg 468, Level 16, State 9, Procedure aspnet_UsersInRoles_RemoveUsersFromRoles, Line 50 Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation. Msg 468, Level 16, State 9, Procedure aspnet_UsersInRoles_RemoveUsersFromRoles, Line 58 Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation. Msg 468, Level 16, State 9, Procedure aspnet_UsersInRoles_RemoveUsersFromRoles, Line 84 Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation. Msg 468, Level 16, State 9, Procedure aspnet_UsersInRoles_RemoveUsersFromRoles, Line 92 Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.
/****** Object: StoredProcedure [dbo].[aspnet_UsersInRoles_RemoveUsersFromRoles] Script Date: 05/20/2007 11:23:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_UsersInRoles_RemoveUsersFromRoles]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N' CREATE PROCEDURE [dbo].[aspnet_UsersInRoles_RemoveUsersFromRoles] @ApplicationName nvarchar(256), @UserNames nvarchar(4000), @RoleNames nvarchar(4000) AS BEGIN DECLARE @AppId uniqueidentifier SELECT @AppId = NULL SELECT @AppId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName IF (@AppId IS NULL) RETURN(2)
DECLARE @TranStarted bit SET @TranStarted = 0
IF( @@TRANCOUNT = 0 ) BEGIN BEGIN TRANSACTION SET @TranStarted = 1 END
DECLARE @tbNames table(Name nvarchar(256) NOT NULL PRIMARY KEY) DECLARE @tbRoles table(RoleId uniqueidentifier NOT NULL PRIMARY KEY) DECLARE @tbUsers table(UserId uniqueidentifier NOT NULL PRIMARY KEY) DECLARE @Num int DECLARE @Pos int DECLARE @NextPos int DECLARE @Name nvarchar(256) DECLARE @CountAll int DECLARE @CountU int DECLARE @CountR int
SET @Num = 0 SET @Pos = 1 WHILE(@Pos <= LEN(@RoleNames)) BEGIN SELECT @NextPos = CHARINDEX(N'','', @RoleNames, @Pos) IF (@NextPos = 0 OR @NextPos IS NULL) SELECT @NextPos = LEN(@RoleNames) + 1 SELECT @Name = RTRIM(LTRIM(SUBSTRING(@RoleNames, @Pos, @NextPos - @Pos))) SELECT @Pos = @NextPos+1
INSERT INTO @tbNames VALUES (@Name) SET @Num = @Num + 1 END
INSERT INTO @tbRoles SELECT RoleId FROM dbo.aspnet_Roles ar, @tbNames t WHERE LOWER(t.Name) = ar.LoweredRoleName AND ar.ApplicationId = @AppId SELECT @CountR = @@ROWCOUNT
IF (@CountR <> @Num) BEGIN SELECT TOP 1 N'''', Name FROM @tbNames WHERE LOWER(Name) NOT IN (SELECT ar.LoweredRoleName FROM dbo.aspnet_Roles ar, @tbRoles r WHERE r.RoleId = ar.RoleId) IF( @TranStarted = 1 ) ROLLBACK TRANSACTION RETURN(2) END
DELETE FROM @tbNames WHERE 1=1 SET @Num = 0 SET @Pos = 1
WHILE(@Pos <= LEN(@UserNames)) BEGIN SELECT @NextPos = CHARINDEX(N'','', @UserNames, @Pos) IF (@NextPos = 0 OR @NextPos IS NULL) SELECT @NextPos = LEN(@UserNames) + 1 SELECT @Name = RTRIM(LTRIM(SUBSTRING(@UserNames, @Pos, @NextPos - @Pos))) SELECT @Pos = @NextPos+1
INSERT INTO @tbNames VALUES (@Name) SET @Num = @Num + 1 END
INSERT INTO @tbUsers SELECT UserId FROM dbo.aspnet_Users ar, @tbNames t WHERE LOWER(t.Name) = ar.LoweredUserName AND ar.ApplicationId = @AppId
SELECT @CountU = @@ROWCOUNT IF (@CountU <> @Num) BEGIN SELECT TOP 1 Name, N'''' FROM @tbNames WHERE LOWER(Name) NOT IN (SELECT au.LoweredUserName FROM dbo.aspnet_Users au, @tbUsers u WHERE u.UserId = au.UserId)
IF( @TranStarted = 1 ) ROLLBACK TRANSACTION RETURN(1) END
SELECT @CountAll = COUNT(*) FROMdbo.aspnet_UsersInRoles ur, @tbUsers u, @tbRoles r WHERE ur.UserId = u.UserId AND ur.RoleId = r.RoleId
IF (@CountAll <> @CountU * @CountR) BEGIN SELECT TOP 1 UserName, RoleName FROM @tbUsers tu, @tbRoles tr, dbo.aspnet_Users u, dbo.aspnet_Roles r WHERE u.UserId = tu.UserId AND r.RoleId = tr.RoleId AND tu.UserId NOT IN (SELECT ur.UserId FROM dbo.aspnet_UsersInRoles ur WHERE ur.RoleId = tr.RoleId) AND tr.RoleId NOT IN (SELECT ur.RoleId FROM dbo.aspnet_UsersInRoles ur WHERE ur.UserId = tu.UserId) IF( @TranStarted = 1 ) ROLLBACK TRANSACTION RETURN(3) END
DELETE FROM dbo.aspnet_UsersInRoles WHERE UserId IN (SELECT UserId FROM @tbUsers) AND RoleId IN (SELECT RoleId FROM @tbRoles) IF( @TranStarted = 1 ) COMMIT TRANSACTION RETURN(0) END ' END GO
I am facing the Follwing error: Cannot resolve the collation conflict between "Latin1_General_BIN" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
Follwing is my Code: code]select * from #temp_Table a where ltrim(rtrim(Model_ID))+''+ltrim(rtrim(Set_Serial_Number)) in (select ltrim(rtrim(b.Model_SerialNo)) from #temp_Table2 b where b.Auto_Id=1)[[/code]
In database persons I have a table named persons_class1 as follows: person_id char(13) not null [primary key] name varchar(20) not null
In database cars I have a table named cars_blue as follows: car_id char(13) not null [primary key] model varchar(20) not null person_id char(13) not null
In my program the initial catalog is: cars and I´m executing the following SQL:
select cars_blue.model,persons.dbo.persons_class1.name from cars_blue,persons.dbo.persons_class1 where cars_blue.person_id=persons.dbo.persons_class1.person_id
and the following error occurs: Cannot resolve collation conflict for equal operation
I also tried like instead of = and the error is: Cannot resolve collation conflict for like operation
In database persons I have a table named persons_class1 as follows: person_id char(13) not null [primary key] name varchar(20) not null
In database cars I have a table named cars_blue as follows: car_id char(13) not null [primary key] model varchar(20) not null person_id char(13) not null
In my program the initial catalog is: cars and I´m executing the following SQL:
select cars_blue.model,persons.dbo.persons_class1.name from cars_blue,persons.dbo.persons_class1 where cars_blue.person_id=persons.dbo.persons_class1.per son_id
and the following error occurs: Cannot resolve collation conflict for equal operation
I also tried like instead of = and the error is: Cannot resolve collation conflict for like operation
Cannot resolve collation conflict for equal to operation.
but the situation is that I can run the query perfectly with one user (windows user) but using an standar user, I've got the error, as far as I know the collation feature applies to db's an objects not to users, what can I do to run this query with the standar user? both users have the same permission on the db. Below is the query attached.
Thank you Gabriel
SELECT B.EmpSupervisorId, a.Info_ID as A_Info_ID, ISNULL(a.Owner_SSO_ID,'') as A_Owner_SSO_ID, ISNULL(a.Ref_SSO_ID,'') as A_Ref_SSO_ID, ISNULL(a.Prev_Owner_SSO_ID,0) as A_Prev_Owner_SSO_ID, ISNULL(a.MgmtTypeFlag,0) as A_MgmtTypeFlag, ISNULL(a.CurrentStatusID, 1) as A_CurrentStatusID, ISNULL(a.PrevStatusID,0) as A_PrevStatusID, ISNULL(C.ConTypeOption, '') as C_ConTypeOption, ISNULL(CAST(a.Last_Status_Update_Date AS VARCHAR),'') as A_Last_Status_Update_Date, ISNULL(B.sfBUID,'0') as A_Bus_Group_ID, 0 AS A_Bus_Group_Seg_ID, ISNULL(a.Organization,'') as A_Organization, ISNULL(a.ContactName,'') as A_ContactName, ISNULL(A.Title, '') as A_ContactTitle, ISNULL(a.ContactComName,'') as A_ContactComName, ISNULL(a.Phone,'') as A_Phone, ISNULL(a.NatureOfOppID,'') as A_NatureOfOppID, ISNULL(a.DealAmount,0) as A_DealAmount, ISNULL(CAST(a.Cust_Contacted_Date AS VARCHAR),'') A_Cust_Contacted_Date, ISNULL(CAST(a.Lead_Qualified_Date AS VARCHAR),'') A_Lead_Qualified_Date, ISNULL(CAST(a.Tran_Processed_Date AS VARCHAR),'') A_Tran_Processed_Date, ISNULL(CAST(a.Quote_Accepted_Date AS VARCHAR),'') as A_Quote_Accepted_Date, ISNULL(CAST(a.Approved_By_HFS_Date AS VARCHAR),'') as A_Approved_By_HFS_Date, ISNULL(CAST(a.Funded_By_HFS_Date AS VARCHAR),'') as A_Funded_By_HFS_Date, ISNULL(a.Lead_In_Amount,0) as A_Lead_In_Amount, ISNULL(a.Cust_Contacted_Amount,0) as A_Cust_Contacted_Amount, ISNULL(a.Lead_Qualified_Amount,0) as A_Lead_Qualified_Amount, ISNULL(a.Tran_Processed_Amount,0) as A_Tran_Processed_Amount, ISNULL(a.Quote_Accepted_Amount,0) as A_Quote_Accepted_Amount, ISNULL(a.Approved_By_HFS_Amount,0) as A_Approved_By_HFS_Amount, ISNULL(a.Funded_By_HFS_Amount,0) as A_Funded_By_HFS_Amount, ISNULL(CAST(CreationDate AS VARCHAR),'') as A_CreationDate, ISNULL(a.BusType,'') as A_BusType, ISNULL(a.NonHFS_XLink_ContactName,'') as A_NonHFS_XLink_ContactName, ISNULL(a.NonHFS_XLink_Bus_ID,'0') as A_NonHFS_XLink_Bus_ID, ISNULL(a.Comments,'') as A_Comments, ISNULL(a.ExistCustomerID,'') as A_ExistCustomerID, ISNULL(a.FinancialNeedID,'') as A_FinancialNeedID, ISNULL(a.CampaignID,'') as A_CampaignID, (GEC_HFS_CORE.dbo.GetBusinessDays(Last_Status_Upda te_Date, getdate())-1) as BusDateDiff, ISNULL(A.DealTypeID, '') as A_DealTypeID FROM tblInformation a LEFT OUTER JOIN GEC_HFS_LM_SSOInfo B ON A.Owner_SSO_ID = B.sfSSOID LEFT OUTER JOIN tblContactType C ON a.Bustype = C.ConType_ID WHERE a.CurrentStatusID NOT IN (9, 10, 11) AND ISNULL(a.isNoChange,'NO') = 'NO' AND ISNULL(a.IsDeleted,'NO') = 'NO' AND DATEDIFF(DAY, Last_Status_Update_Date, GETDATE())<> 0 AND ISNULL(a.isNonHFS, 'NO') = 'NO' ORDER BY A_Info_ID
I am fairly new to MS-SQL so excuse my ignorance if I have any.
I am trying to add and link a new table to an existing database. I can add the database and colums and add some data into it, and even run a query on it. However, if I try to run a query using the new table and and existing table (linked) I get the following error:
ODBC--call failed. [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot resolve collation conflict for equal to operation. (#446)
I've stumbled across the above error and am a little stuck.I've just installed SQL2000 (sp3) on my PC and restored a database fromour Live server. On a simple Update statement on a #temp table, itfails with the above message. I think I understand what it means andfound some old posts suggesting using the following :select name, databasepropertyex(name, 'collation')from master..sysdatabasesselect serverproperty('collation')All of the databases that are there by default are set to'Latin1_General_CI_AS' and the restored db is'SQL_Latin1_General_CP1_CI_AS'.The live server has all of these set to the 'SQL...' version, but astandard install points to the other. So, how do I change mine to the'newer' setting ? All I need to do is mimic the live environment fortesting and development. There is only me using it, and it's not aproblem to bin it and re-install, or tweak if I need to.I've tried using :ALTER DATABASE Northwind COLLATE SQL_Latin1_General_CP1_CI_ASas a test (thought this was the best example to show), but it failsstating the following :Server: Msg 5075, Level 16, State 1, Line 5The object 'CK_Products_UnitPrice' is dependent on database collation.Server: Msg 5075, Level 16, State 1, Line 5The object 'CK_ReorderLevel' is dependent on database collation.Server: Msg 5075, Level 16, State 1, Line 5The object 'CK_UnitsInStock' is dependent on database collation.Server: Msg 5075, Level 16, State 1, Line 5The object 'CK_UnitsOnOrder' is dependent on database collation.Server: Msg 5075, Level 16, State 1, Line 5The object 'CK_Discount' is dependent on database collation.Server: Msg 5075, Level 16, State 1, Line 5The object 'CK_Quantity' is dependent on database collation.Server: Msg 5075, Level 16, State 1, Line 5The object 'CK_UnitPrice' is dependent on database collation.Server: Msg 5075, Level 16, State 1, Line 5The object 'CK_Birthdate' is dependent on database collation.Server: Msg 5072, Level 16, State 1, Line 5ALTER DATABASE failed. The default collation of database 'Northwind'cannot be set to SQL_Latin1_General_CP1_CI_AS.On the Live server, the Northwind database is set to the 'SQL...'version, so it MUST be do-able somehow.Any pointers would be appreciated.Thanks in advanceRyan
I have collation defined on one column and default on another being used in where clause. How can I resolve this problem without changing collation in table.
I'm getting the following errors when trying to execute the following script on the server, its part of the standard asp.net membership and roles, anybody have any ideas how I get get round this? Msg 468, Level 16, State 9, Procedure aspnet_UsersInRoles_RemoveUsersFromRoles, Line 50 Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation. Msg 468, Level 16, State 9, Procedure aspnet_UsersInRoles_RemoveUsersFromRoles, Line 58 Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation. Msg 468, Level 16, State 9, Procedure aspnet_UsersInRoles_RemoveUsersFromRoles, Line 84 Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation. Msg 468, Level 16, State 9, Procedure aspnet_UsersInRoles_RemoveUsersFromRoles, Line 92 Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation. /****** Object: StoredProcedure [dbo].[aspnet_UsersInRoles_RemoveUsersFromRoles] Script Date: 05/20/2007 11:23:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_UsersInRoles_RemoveUsersFromRoles]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N' CREATE PROCEDURE [dbo].[aspnet_UsersInRoles_RemoveUsersFromRoles] @ApplicationName nvarchar(256), @UserNames nvarchar(4000), @RoleNames nvarchar(4000) AS BEGIN DECLARE @AppId uniqueidentifier SELECT @AppId = NULL SELECT @AppId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName IF (@AppId IS NULL) RETURN(2)
DECLARE @TranStarted bit SET @TranStarted = 0
IF( @@TRANCOUNT = 0 ) BEGIN BEGIN TRANSACTION SET @TranStarted = 1 END
DECLARE @tbNames table(Name nvarchar(256) NOT NULL PRIMARY KEY) DECLARE @tbRoles table(RoleId uniqueidentifier NOT NULL PRIMARY KEY) DECLARE @tbUsers table(UserId uniqueidentifier NOT NULL PRIMARY KEY) DECLARE @Num int DECLARE @Pos int DECLARE @NextPos int DECLARE @Name nvarchar(256) DECLARE @CountAll int DECLARE @CountU int DECLARE @CountR int
SET @Num = 0 SET @Pos = 1 WHILE(@Pos <= LEN(@RoleNames)) BEGIN SELECT @NextPos = CHARINDEX(N'','', @RoleNames, @Pos) IF (@NextPos = 0 OR @NextPos IS NULL) SELECT @NextPos = LEN(@RoleNames) + 1 SELECT @Name = RTRIM(LTRIM(SUBSTRING(@RoleNames, @Pos, @NextPos - @Pos))) SELECT @Pos = @NextPos+1
INSERT INTO @tbNames VALUES (@Name) SET @Num = @Num + 1 END
INSERT INTO @tbRoles SELECT RoleId FROM dbo.aspnet_Roles ar, @tbNames t WHERE LOWER(t.Name) = ar.LoweredRoleName AND ar.ApplicationId = @AppId SELECT @CountR = @@ROWCOUNT
IF (@CountR <> @Num) BEGIN SELECT TOP 1 N'''', Name FROM @tbNames WHERE LOWER(Name) NOT IN (SELECT ar.LoweredRoleName FROM dbo.aspnet_Roles ar, @tbRoles r WHERE r.RoleId = ar.RoleId) IF( @TranStarted = 1 ) ROLLBACK TRANSACTION RETURN(2) END
DELETE FROM @tbNames WHERE 1=1 SET @Num = 0 SET @Pos = 1
WHILE(@Pos <= LEN(@UserNames)) BEGIN SELECT @NextPos = CHARINDEX(N'','', @UserNames, @Pos) IF (@NextPos = 0 OR @NextPos IS NULL) SELECT @NextPos = LEN(@UserNames) + 1 SELECT @Name = RTRIM(LTRIM(SUBSTRING(@UserNames, @Pos, @NextPos - @Pos))) SELECT @Pos = @NextPos+1
INSERT INTO @tbNames VALUES (@Name) SET @Num = @Num + 1 END
INSERT INTO @tbUsers SELECT UserId FROM dbo.aspnet_Users ar, @tbNames t WHERE LOWER(t.Name) = ar.LoweredUserName AND ar.ApplicationId = @AppId
SELECT @CountU = @@ROWCOUNT IF (@CountU <> @Num) BEGIN SELECT TOP 1 Name, N'''' FROM @tbNames WHERE LOWER(Name) NOT IN (SELECT au.LoweredUserName FROM dbo.aspnet_Users au, @tbUsers u WHERE u.UserId = au.UserId)
IF( @TranStarted = 1 ) ROLLBACK TRANSACTION RETURN(1) END
SELECT @CountAll = COUNT(*) FROMdbo.aspnet_UsersInRoles ur, @tbUsers u, @tbRoles r WHERE ur.UserId = u.UserId AND ur.RoleId = r.RoleId
IF (@CountAll <> @CountU * @CountR) BEGIN SELECT TOP 1 UserName, RoleName FROM @tbUsers tu, @tbRoles tr, dbo.aspnet_Users u, dbo.aspnet_Roles r WHERE u.UserId = tu.UserId AND r.RoleId = tr.RoleId AND tu.UserId NOT IN (SELECT ur.UserId FROM dbo.aspnet_UsersInRoles ur WHERE ur.RoleId = tr.RoleId) AND tr.RoleId NOT IN (SELECT ur.RoleId FROM dbo.aspnet_UsersInRoles ur WHERE ur.UserId = tu.UserId) IF( @TranStarted = 1 ) ROLLBACK TRANSACTION RETURN(3) END
DELETE FROM dbo.aspnet_UsersInRoles WHERE UserId IN (SELECT UserId FROM @tbUsers) AND RoleId IN (SELECT RoleId FROM @tbRoles) IF( @TranStarted = 1 ) COMMIT TRANSACTION RETURN(0) END ' END GOAny help appreciated thanks,
For my ASP.net Application on a hosted MSSQL server, i have had to re-ceate using the scripts generated from my local machine using sql tools with SSMSE. This worked beautifully and all appears to work. My ASP.net application users can create their accounts and login normally. Unfortunately, my create user wizard does not automatically generate roles for the clients. So, i am using a routine that includes the following Protected Sub CreateWizard1_OnUserCrated(ByVal sender As Object, ByVal e As System.EventArgs) Handles CreateUserWizard1.CreatedUser Dim userInfo As MembershipUser = Membership.GetUser(CreateUserWizard1.UserName) userInfo.IsApproved = False Membership.UpdateUser(userInfo) Roles.AddUserToRole(CreateUserWizard1.UserName, "Friends") End Sub which does not work because it is looking for a AddUserToRole stored procedure. This is confusing because it this SP IS on my local version and works fine. So i attemtped to create a new SP called AddUserToRole on the server but get an error message Msg 468, Level 16, State 9, Procedure aspnet_UsersInRoles_AddUsersToRoles, Line 49 Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation. (actually it occurs 5 times in the routine). Further research from the various SQL forums and even MSDN points the problem as one of Collation versions - and suggest the answer is to re-install. I was wondering if any might know of a way of changing the collation version . Loking at db properties on my server db has the collation version set to SQL_Latin1_General_CP1_CI_AS. I do not understand enough about sql to know if changing the version the Stored Procedure wants to use as its default ( which appears to be Latin1_General_CI_AS). ANY IDEAS????? As i have already deleted and re-installed my mssql db, i would rather find an alternative / better solution. many thanks in anticipation. steve
Exception information:System.Data.SqlClient.SqlException: Cannot resolve collation conflict for equal to operation. Who can tell me how to resolve this problem? Thx
I have a query that I need to run where I join two tables that both reside on different servers. I use an INNER JOIN statement to attempt to join these tables, but for some reason I am getting the following error message...
"
Cannot resolve collation conflict for equal to operation."
The query is as follows...
SELECT TABLE_NAME, LEFT(TABLE_NAME, CHARINDEX('_', TABLE_NAME + '_') - 1) AS Abbreviation FROM mrcsmis.INFORMATION_SCHEMA.TABLES INNER JOIN DEVELCAD1.Portal.dbo.dnl_db_names_log AS imp ON (imp.dnl_table_name = TABLE_NAME) WHERE (TABLE_TYPE = 'BASE TABLE')
I hit this error when I tried to do something like:
Code Snippet create table #tempTable (ID int IDENTITY(1,1) NOT NULL , column1 varchar(200) NOT NULL) INSERT INTO #tempTable Values('test') create table tempTable (ID int IDENTITY(1,1) NOT NULL , column2 varchar(200) NOT NULL) INSERT INTO tempTable Values('test') Select * from #tempTable t inner join tempTable p on t.ID = p.ID where t.column1 = p.column2
After a thorough search on sysobjects and syscolumns, I found the collation problem on database "temp" is set to "Latin1_General_CI_AS"
since it's an system db, I cannot alter. My db also cannot alter because some SP is encrypted, though I cna de-crypt it. Is that anyway to solve it by running the script??
I tried to do something like: start /wait setup.exe /qb INSTANCENAME= {my MSSQL 2005 insatnce name} REINSTALL=SQL_Engine REBUILDDATABASE=SAPWD= {my sa password} SQLCOLLATION=SQL_Latin1_General_CP1_CI_AI
but i get no error at Summamry.txt: Setup succeeded with the installation, inspect the log file completely for status on all the components.
while at "SQLSetup0011__Core.log":
Error: Action "LaunchPatchedBootstrapAction" threw an exception during execution. Error information reported during run: "C:Program FilesMicrosoft SQL Server90Setup Bootstrapsetup.exe" finished and returned: 0 Aborting queue processing as nested installer has completed Message pump returning: 0
Hello,I currently have Table1 and View1.View1 is a query from 2 or 3 tables that works fine on its own.However in my current query if I try to use it...something like...SELECT a.col1, a.col2, a.col3, b.col1, b.col2, b.col3FROM View1 a JOIN Table1 b on a.col1 = b.col1WHERE a.col2 <b.col2 OR a.col3 <b.col3It throws an error "Server: Msg 446, Level 16, State 9, Line 1 Cannotresolve collation conflict for not equal to operation."Clearly I need to use collation between Table1 and View1, But I dontknow where I need to use "COLLATE SQL_Latin1_General_CP850_CI_AI" andhow? this is the collation set on Table1.Thank you!Yas
I am learning dotnet thru Microsoft Official Curriculum and I got stuck up at one point. That is I couldn't configure the required databases according to the sql server I have installed and configured. I created a doctors database and while configuring using the below code I couldn't know what is 'ASPNET' in "SELECT @s = @@servername + 'ASPNET'" Also what is 'webuser'
I've restored a SQL Server 2000 database with a Latin1_General_BIN collation from a .dmp file to a SQL Server 2005 server with a default collation of SQL_Latin1_General_CP1_CI_AS. When I try to change the database collation I get hundreds of the following error:
The object 'CK_PM10200_GLPOSTD_00AF8CF' is dependent on database collation. So, in this case, is it even possible to change the collation if there are objects in the database that are dependent on it?
