Error While Calling The Roles.AddUserToRole (error Message: Cannot Resolve Collation Conflict For Equal To Operation)
Feb 5, 2006
Hi, I have developed a website in asp.net 2. I have tester it and it is working fine on my computer but when I have uploaded it to my server I'm getting an error message when the user signup. The error occurs when I'm setting the user role to 'members'.
Error line > Roles.AddUserToRole(user.UserName,
"members")
The strage thig is that it is working on my computer but not on the server. My home computer and the server are running the same software versions and the website database is the same as well.
To double check that my code is not generating the error I have lonched 'SQL Query Analizer' and executed the folowing code on my database:
NOTE: In my database I have create the user “teeluk12� and a role “members�
Exception information:System.Data.SqlClient.SqlException: Cannot resolve collation conflict for equal to operation. Who can tell me how to resolve this problem? Thx
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'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,
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
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
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
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
I am trying to update one table on server A/database A with new records from a server B/database B.
Both databases use the same collation. The key-fields of both table do use the same collation, but not the server.
Help: I was thinking, when both databases and the fields inside the tables do both use the same collation, then there will not upcome any conflict ?
Do I have to adapt the database collation, too ? This would'nt be nice as I am retreiving data from different servers with different server collations... Anybody else got the same problem ?
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 practicing on vmware 2 node failover cluster for doing Sql failover clustering understanding.I did proper Ad Dc setup and configured san storage through iscsi software that I downloaded from net.I did target and initiator configuration for san storage on same Domain controller vm.Name of target is Target1 and has two initialtor for node1 and node2.It has two virtual disk assigned Quorumdisk.vhd and sqldisk.vhd.ON vmnode1 I have initialzed and formatted my both disk with proper volume and label.
Node which are participating in cluster are node1 and node2.while my windows clustering went fine,while Sql server installation on node 1,i got error IP Address conflict occured.
my DC has IP address:192.168.1.10 my Node1 is 192.168.1.20 node2:192.168.1.30 Cluster IP address is 192.168.1.35
What has cause this situation.What configuration is needed.How do I troubleshoot this problem as now I can not install my sql for clustering as also gave me Invalid network name error.Is my storage configuration not proper or IP address is in correct?Do I need to put my virtual disk in Clustered shared volume?
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!
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'
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]
My apologies...I wasn't for sure where to post an error like this...
Over the last 2 months I have gotten this SQL Server error (twice). All existing processes will continue to work, however no new processes can be created and users cannot connect to the server. This is the exact text of the message in the SQL Server error log.
Operating system error 10038: An operation was attempted on something that is not a socket...
Error: 17059, Severity: 18, State: 0
Error accepting connection request via Net-Library 'SSNETLIB'. Execution continuing.
Error: 17882, Severity: 18, State:
While we can typically just stop SQL Server Service and restart the services...I have found it is best to restart the machine during non-production times to take care of any 'residual' effects of this error.
The SQL Server 2000 SP4 box with Windows 2003 Standard SP1 is well maintained by our I.T. team and it typically will run 4 or 5 months without a reboot.
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 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?
dear friends, i started using Asp.net for developing webparts, web parts automatically connect to a database to be created and saved in the database. i had already Sql Server 2005 Express Edition installed so my webpart page ran and automatically created the database "AspNetDb" in the "App_Data" folder. but when i uninstalled Sql Server 2005 Express Edition and then installed the Sql Server 2005 Enterprise Edition, it gave me the following Error 26 as below :
--------------------------------------------------------- Beginning of Error Message -------------------------------------------------- An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. SQLExpress database file auto-creation error: The connection string specifies a local Sql Server Express instance using a database location within the applications App_Data directory. The provider attempted to automatically create the application services database because the provider determined that the database does not exist. The following configuration requirements are necessary to successfully check for existence of the application services database and automatically create the application services database:
If the applications App_Data directory does not already exist, the web server account must have read and write access to the applications directory. This is necessary because the web server account will automatically create the App_Data directory if it does not already exist. If the applications App_Data directory already exists, the web server account only requires read and write access to the applications App_Data directory. This is necessary because the web server account will attempt to verify that the Sql Server Express database already exists within the applications App_Data directory. Revoking read access on the App_Data directory from the web server account will prevent the provider from correctly determining if the Sql Server Express database already exists. This will cause an error when the provider attempts to create a duplicate of an already existing database. Write access is required because the web server accounts credentials are used when creating the new database. Sql Server Express must be installed on the machine. The process identity for the web server account must have a local user profile. See the readme document for details on how to create a local user profile for both machine and domain accounts. --------------------------------------------------------- End of Error Message -------------------------------------------------- then when i checked the Machine.config file, i found this.... <connectionStrings> <add name="LocalSqlServer" connectionString="data source=.SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true" providerName="System.Data.SqlClient" /> </connectionStrings> then i thought it's becoz, of the datasource thing that the Machine.Config file supports by default the default free edition of Sql Server 2005 which is the Sql Server 2005 Express Edition. but friend in my company we use Sql Server 2005 Enterprise Edition, so can anybody pls guide me and get me out of this soup on helping me to use Sql Server 2005 Enterprise Edition by modifying either 1) Machine.Config File and/or 2) Web.Config file and/or 3) any other way, but please help i want to do this, please...... regards and thanks Gurjit Singh
l've a raw data which contains list of device name and # of wires used, i.e. DEVICE NAME # of Wires --------------------- ------------- A 10 B 11 C 17 D 5 A 0 E 0
So l would like to import these data into my table (with device name as primary key) which each device only appear once. If the device name appear twices, then l should ignore device with # of wires = 0. If device name appear twice and # of wires <> 0, then l should log it.
I have an File System Task that copies a file from one directory ot another. When I hard code the target directory (c:dirfile.txt) it works fine. When I change it to a virtual directory (\serverdirfile.txt) I get a security error:
[File System Task] Error: An error occurred with the following error message: "Access to the path '\gracehbtest oS2TMM_Live_Title_000002.xml' is denied.".
I'm trying to use an XML Task to do a simple XSLT operation, but it fails with this error message:
[XML Task] Error: An error occurred with the following error message: "There are multiple root elements. Line 5, position 2.".
The source XML file validates fine and I've successfully used it as the XML Source in a data flow task to load some SQL Server tables. It has very few line breaks, so the first 5 lines are pretty long: almost 4000 characters, including 34 start-tags, 19 end-tags, and 2 empty element tags. Here's the very beginning of it:
<?xml version="1.0" encoding="UTF-8"?> <ESDU releaselevel="2006-02" createdate="26 May 2006"><package id="1" title="_standard" shorttitle="_standard" filename="pk_stan" supplementdate="01/05/2005" supplementlevel="1"><abstract><![CDATA[This package contains the standard ESDU Series.]]></abstract>
There is only 1 ESDU root element and only 1 package element.
Of course, the XSLT stylesheet is also an XML document in its own right. I specify it directly in the XML Task:
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.
