none
Problem with stored procedure in aspnet.mdf Sql server when publishing. Collate?

    Question

  • Hi guys,

    I´m migrating a database to my web host. Most of it is working fine (I think), but there´s a problem with the membership database aspnet.mdf, particularly to add user to role. The stored procedure for that simply isn´t there.

    when executing the migration script on the web host server it gives me this error:

    Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Finnish_Swedish_CI_AS" in the equal to operation.

    The people at the web host have been very quiet so I´m guessing they don´t know how to solve it.

    I guess I should mention that another database, which holds the main part of my data, is working when being scripted, so the problem is with the aspnet.mdf, particularly stored procedures.

    Thanks

    Saturday, August 17, 2013 7:45 AM

Answers

All replies

  • Sound's like that you restore a database with Swedish collation to one server that installs with Latin collation. In this proc you involved with conflict because you want to access out of your database scope at the server level. So as you cannot change the server, you need to change those sections and fix it. Please see this link:

    COLLATE


    sqldevelop.wordpress.com

    Saturday, August 17, 2013 8:01 AM
  • Thanks for answering!

    I´m very novice to this I´m afraid... This is the generated script for the adduserstoroles sp.

    where does the collate command go?

    thanks again?

    USE [C:\myDir\ASPNETDB.MDF]
    GO

    /****** Object:  StoredProcedure [dbo].[aspnet_UsersInRoles_AddUsersToRoles]    Script Date: 08/15/2013 21:53:24 ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER OFF
    GO


    CREATE PROCEDURE [dbo].[aspnet_UsersInRoles_AddUsersToRoles]
        @ApplicationName  nvarchar(256),
        @UserNames          nvarchar(4000),
        @RoleNames          nvarchar(4000),
        @CurrentTimeUtc   datetime
    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)

        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

        IF (@@ROWCOUNT <> @Num)
        BEGIN
            SELECT TOP 1 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

        IF (@@ROWCOUNT <> @Num)
        BEGIN
            DELETE FROM @tbNames
            WHERE LOWER(Name) IN (SELECT LoweredUserName FROM dbo.aspnet_Users au,  @tbUsers u WHERE au.UserId = u.UserId)

            INSERT dbo.aspnet_Users (ApplicationId, UserId, UserName, LoweredUserName, IsAnonymous, LastActivityDate)
              SELECT @AppId, NEWID(), Name, LOWER(Name), 0, @CurrentTimeUtc
              FROM   @tbNames

            INSERT INTO @tbUsers
              SELECT  UserId
              FROM    dbo.aspnet_Users au, @tbNames t
              WHERE   LOWER(t.Name) = au.LoweredUserName AND au.ApplicationId = @AppId
        END

        IF (EXISTS (SELECT * FROM dbo.aspnet_UsersInRoles ur, @tbUsers tu, @tbRoles tr WHERE tu.UserId = ur.UserId AND tr.RoleId = ur.RoleId))
        BEGIN
            SELECT TOP 1 UserName, RoleName
            FROM         dbo.aspnet_UsersInRoles ur, @tbUsers tu, @tbRoles tr, aspnet_Users u, aspnet_Roles r
            WHERE        u.UserId = tu.UserId AND r.RoleId = tr.RoleId AND tu.UserId = ur.UserId AND tr.RoleId = ur.RoleId

            IF( @TranStarted = 1 )
                ROLLBACK TRANSACTION
            RETURN(3)
        END

        INSERT INTO dbo.aspnet_UsersInRoles (UserId, RoleId)
        SELECT UserId, RoleId
        FROM @tbUsers, @tbRoles

        IF( @TranStarted = 1 )
            COMMIT TRANSACTION
        RETURN(0)
    END                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
    GO

    Saturday, August 17, 2013 8:52 AM
  • Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Finnish_Swedish_CI_AS" in the equal to operation.

    Hello

    The error message indicate that the objects of the equal to (=) operation with different collation.
    For example, the collation of the Stroed Procedure and the column "LoweredApplicationName" of the “aspnet_Applications” are different. Then you can refer to the following statement to solve this issue:

    SELECT  @AppId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName  COLLATE  SQL_Latin1_General_CP1_CI_AS ) = LoweredApplicationName

    You can refer to the following article to find the database or table column collection:
    http://blog.sqlauthority.com/2008/12/16/sql-server-find-collation-of-database-and-table-column-using-t-sql/

    Regards,
    Fanny Liu


    Fanny Liu
    TechNet Community Support

    Monday, August 19, 2013 9:28 AM
    Moderator