Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2004
    Posts
    32

    Unanswered: Error "Cannot resolve the collation conflict between"

    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?

    PHP Code:
    Msg 468Level 16State 9Procedure aspnet_UsersInRoles_RemoveUsersFromRolesLine 50
    Cannot resolve the collation conflict between 
    "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.
    Msg 468Level 16State 9Procedure aspnet_UsersInRoles_RemoveUsersFromRolesLine 58
    Cannot resolve the collation conflict between 
    "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.
    Msg 468Level 16State 9Procedure aspnet_UsersInRoles_RemoveUsersFromRolesLine 84
    Cannot resolve the collation conflict between 
    "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.
    Msg 468Level 16State 9Procedure aspnet_UsersInRoles_RemoveUsersFromRolesLine 92
    Cannot resolve the collation conflict between 
    "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation
    PHP Code:
    /****** 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(*)
        FROM    dbo.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 
    Thanks

  2. #2
    Join Date
    May 2007
    Location
    somewhere in dbforums
    Posts
    221
    This is because the collations are not consistent across the database tables and the tables that have been created.

    Try adding the COLLATE to your insert queries.

    something like
    INSERT INTO @tbUsers
    SELECT UserId COLLATE SQL_Latin1_General_CP1_CI_AS
    FROM dbo.aspnet_Users ar, @tbNames t
    WHERE LOWER(t.Name) = ar.LoweredUserName AND ar.ApplicationId = @AppId
    or
    INSERT INTO @tbUsers
    SELECT UserId COLLATE Latin1_General_CI_AS
    FROM dbo.aspnet_Users ar, @tbNames t
    WHERE LOWER(t.Name) = ar.LoweredUserName AND ar.ApplicationId = @AppId


    coz i really dont know which one is conflicting......i mean which collation
    Last edited by nick.ncs; 05-23-07 at 02:10.

  3. #3
    Join Date
    Jul 2004
    Posts
    32
    Yes your right the db and the table fields have different collation values but I don't understand how to change this as this was installed by .net, Can I change this and will it have any impact, Its funny because I've scripted the db from my dev machine and its working fine and now that I'm trying to create it on my host .. Wierd

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •