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 50Cannot resolve the collation conflictbetween "SQL_Latin1_General_CP1_CI_AS"and "Latin1_General_CI_AS"in the equalto operation.Msg 468,Level 16, State 9,Procedure aspnet_UsersInRoles_RemoveUsersFromRoles, Line 58Cannot resolve the collation conflictbetween "SQL_Latin1_General_CP1_CI_AS"and "Latin1_General_CI_AS"in the equalto operation.Msg 468,Level 16, State 9,Procedure aspnet_UsersInRoles_RemoveUsersFromRoles, Line 84Cannot resolve the collation conflictbetween "SQL_Latin1_General_CP1_CI_AS"and "Latin1_General_CI_AS"in the equalto operation.Msg 468,Level 16, State 9,Procedure aspnet_UsersInRoles_RemoveUsersFromRoles, Line 92Cannot resolve the collation conflictbetween "SQL_Latin1_General_CP1_CI_AS"and "Latin1_General_CI_AS"in the equalto operation.
/****** Object: StoredProcedure [dbo].[aspnet_UsersInRoles_RemoveUsersFromRoles] Script Date: 05/20/2007 11:23:33 ******/SET ANSI_NULLSONGOSET QUOTED_IDENTIFIEROFFGOIFNOT EXISTS (SELECT *FROM sys.objectsWHEREobject_id =OBJECT_ID(N'[dbo].[aspnet_UsersInRoles_RemoveUsersFromRoles]')AND typein (N'P', N'PC'))BEGINEXEC dbo.sp_executesql @.statement = N'CREATE PROCEDURE [dbo].[aspnet_UsersInRoles_RemoveUsersFromRoles]@.ApplicationName nvarchar(256),@.UserNames nvarchar(4000),@.RoleNames nvarchar(4000)ASBEGINDECLARE @.AppId uniqueidentifierSELECT @.AppId = NULLSELECT @.AppId = ApplicationId FROM aspnet_Applications WHERE LOWER(@.ApplicationName) = LoweredApplicationNameIF (@.AppId IS NULL)RETURN(2)DECLARE @.TranStarted bitSET @.TranStarted = 0IF( @.@.TRANCOUNT = 0 )BEGINBEGIN TRANSACTIONSET @.TranStarted = 1ENDDECLARE @.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 intDECLARE @.Pos intDECLARE @.NextPos intDECLARE @.Name nvarchar(256)DECLARE @.CountAll intDECLARE @.CountU intDECLARE @.CountR intSET @.Num = 0SET @.Pos = 1WHILE(@.Pos <= LEN(@.RoleNames))BEGINSELECT @.NextPos = CHARINDEX(N'','', @.RoleNames, @.Pos)IF (@.NextPos = 0 OR @.NextPos IS NULL)SELECT @.NextPos = LEN(@.RoleNames) + 1SELECT @.Name = RTRIM(LTRIM(SUBSTRING(@.RoleNames, @.Pos, @.NextPos - @.Pos)))SELECT @.Pos = @.NextPos+1INSERT INTO @.tbNames VALUES (@.Name)SET @.Num = @.Num + 1ENDINSERT INTO @.tbRoles SELECT RoleId FROM dbo.aspnet_Roles ar, @.tbNames t WHERE LOWER(t.Name) = ar.LoweredRoleName AND ar.ApplicationId = @.AppIdSELECT @.CountR = @.@.ROWCOUNTIF (@.CountR <> @.Num)BEGINSELECT TOP 1 N'''', NameFROM @.tbNamesWHERE LOWER(Name) NOT IN (SELECT ar.LoweredRoleName FROM dbo.aspnet_Roles ar, @.tbRoles r WHERE r.RoleId = ar.RoleId)IF( @.TranStarted = 1 )ROLLBACK TRANSACTIONRETURN(2)ENDDELETE FROM @.tbNames WHERE 1=1SET @.Num = 0SET @.Pos = 1WHILE(@.Pos <= LEN(@.UserNames))BEGINSELECT @.NextPos = CHARINDEX(N'','', @.UserNames, @.Pos)IF (@.NextPos = 0 OR @.NextPos IS NULL)SELECT @.NextPos = LEN(@.UserNames) + 1SELECT @.Name = RTRIM(LTRIM(SUBSTRING(@.UserNames, @.Pos, @.NextPos - @.Pos)))SELECT @.Pos = @.NextPos+1INSERT INTO @.tbNames VALUES (@.Name)SET @.Num = @.Num + 1ENDINSERT INTO @.tbUsers SELECT UserId FROM dbo.aspnet_Users ar, @.tbNames t WHERE LOWER(t.Name) = ar.LoweredUserName AND ar.ApplicationId = @.AppIdSELECT @.CountU = @.@.ROWCOUNTIF (@.CountU <> @.Num)BEGINSELECT TOP 1 Name, N''''FROM @.tbNamesWHERE LOWER(Name) NOT IN (SELECT au.LoweredUserName FROM dbo.aspnet_Users au, @.tbUsers u WHERE u.UserId = au.UserId)IF( @.TranStarted = 1 )ROLLBACK TRANSACTIONRETURN(1)ENDSELECT @.CountAll = COUNT(*)FROMdbo.aspnet_UsersInRoles ur, @.tbUsers u, @.tbRoles rWHERE ur.UserId = u.UserId AND ur.RoleId = r.RoleIdIF (@.CountAll <> @.CountU * @.CountR)BEGINSELECT TOP 1 UserName, RoleNameFROM @.tbUsers tu, @.tbRoles tr, dbo.aspnet_Users u, dbo.aspnet_Roles rWHERE 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 TRANSACTIONRETURN(3)ENDDELETE FROM dbo.aspnet_UsersInRolesWHERE UserId IN (SELECT UserId FROM @.tbUsers) AND RoleId IN (SELECT RoleId FROM @.tbRoles)IF( @.TranStarted = 1 )COMMIT TRANSACTIONRETURN(0)END 'ENDGO
Any help appreciated thanks,
What is your database collation set to?
You can check this in MicrosoftSQL Server Management by right clicking the database and selecting "properties".
Now check what collation you have on your aspnet tables. Right click on aspnet_Roles, and select "Script table as","Create to","New query window". Look for the collation settings on each field.
|||
Thank you for your response on the DB I have - Latin1_General_CI_AS
On the table I have -
****** Object:Table [dbo].[aspnet_Roles] Script Date: 05/22/2007 21:28:48 ******/SET ANSI_NULLSONGOSET QUOTED_IDENTIFIERONGOCREATE TABLE [dbo].[aspnet_Roles]([ApplicationId] [uniqueidentifier]NOT NULL,[RoleId] [uniqueidentifier]NOT NULLCONSTRAINT [DF__aspnet_Ro__RoleI__03F0984C]DEFAULT (newid()),[RoleName] [nvarchar](256) COLLATE SQL_Latin1_General_CP1_CI_ASNOT NULL,[LoweredRoleName] [nvarchar](256) COLLATE SQL_Latin1_General_CP1_CI_ASNOT NULL,[Description] [nvarchar](256) COLLATE SQL_Latin1_General_CP1_CI_ASNULL,CONSTRAINT [PK__aspnet_Roles__02084FDA]PRIMARY KEY NONCLUSTERED ([RoleId]ASC)WITH (PAD_INDEX =OFF, IGNORE_DUP_KEY =OFF)ON [PRIMARY])ON [PRIMARY]GOALTER TABLE [dbo].[aspnet_Roles]WITH CHECK ADD CONSTRAINT [FK__aspnet_Ro__Appli__02FC7413]FOREIGN KEY([ApplicationId])REFERENCES [dbo].[aspnet_Applications] ([ApplicationId])GOALTER TABLE [dbo].[aspnet_Roles]CHECK CONSTRAINT [FK__aspnet_Ro__Appli__02FC7413]
So what do I do to fix it?|||
Any body tell me how i can change the collation?
|||
ALTER DATABASE {DatabaseName} COLLATE {NewCollationName}
In this case ALTER DATABASE ASPNETDB COLLATE SQL_Latin1_General_CP1_CI_AS
|||
That worked, thank you.
