Исключение:
Во время выполнения файла SQL 'InstallRoles.sql' произошла ошибка. Номер ошибки SQL - 468, а сообщение SqlException: Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Cyrillic_General_CI_AS" in the equal to operation.
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Cyrillic_General_CI_AS" in the equal to operation.
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Cyrillic_General_CI_AS" in the equal to operation.
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Cyrillic_General_CI_AS" in the equal to operation.
----------------------------------------
Подробности сбоя
----------------------------------------
SQL Server:
База данных: [AzDAS]
Загружен файл SQL:
InstallRoles.sql
Сбой команд:
DECLARE @ver int
DECLARE @version nchar(100)
DECLARE @dot int
DECLARE @hyphen int
DECLARE @SqlToExec nchar(4000)
SELECT @ver = 7
SELECT @version = @@Version
SELECT @hyphen = CHARINDEX(N' - ', @version)
IF (NOT(@hyphen IS NULL) AND @hyphen > 0)
BEGIN
SELECT @hyphen = @hyphen + 3
SELECT @dot = CHARINDEX(N'.', @version, @hyphen)
IF (NOT(@dot IS NULL) AND @dot > @hyphen)
BEGIN
SELECT @version = SUBSTRING(@version, @hyphen, @dot - @hyphen)
SELECT @ver = CONVERT(int, @version)
END
END
IF (@ver > 7)
SELECT @SqlToExec = N'
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'
ELSE
SELECT @SqlToExec = N'
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 @RoleId uniqueidentifier
DECLARE @UserId uniqueidentifier
DECLARE @UserName nvarchar(256)
DECLARE @RoleName nvarchar(256)
DECLARE @CurrentPosU int
DECLARE @NextPosU int
DECLARE @CurrentPosR int
DECLARE @NextPosR int
SELECT @CurrentPosU = 1
WHILE(@CurrentPosU <= LEN(@UserNames))
BEGIN
SELECT @NextPosU = CHARINDEX(N'','', @UserNames, @CurrentPosU)
IF (@NextPosU = 0 OR @NextPosU IS NULL)
SELECT @NextPosU = LEN(@UserNames) + 1
SELECT @UserName = SUBSTRING(@UserNames, @CurrentPosU, @NextPosU - @CurrentPosU)
SELECT @CurrentPosU = @NextPosU+1
SELECT @CurrentPosR = 1
WHILE(@CurrentPosR <= LEN(@RoleNames))
BEGIN
SELECT @NextPosR = CHARINDEX(N'','', @RoleNames, @CurrentPosR)
IF (@NextPosR = 0 OR @NextPosR IS NULL)
SELECT @NextPosR = LEN(@RoleNames) + 1
SELECT @RoleName = SUBSTRING(@RoleNames, @CurrentPosR, @NextPosR - @CurrentPosR)
SELECT @CurrentPosR = @NextPosR+1
SELECT @RoleId = NULL
SELECT @RoleId = RoleId FROM dbo.aspnet_Roles WHERE LoweredRoleName = LOWER(@RoleName) AND ApplicationId = @AppId
IF (@RoleId IS NULL)
BEGIN
SELECT @RoleName
IF( @TranStarted = 1 )
ROLLBACK TRANSACTION
RETURN(2)
END
SELECT @UserId = NULL
SELECT @UserId = UserId FROM dbo.aspnet_Users WHERE LoweredUserName = LOWER(@UserName) AND ApplicationId = @AppId
IF (@UserId IS NULL)
BEGIN
EXEC dbo.aspnet_Users_CreateUser @AppId, @UserName, 0, @CurrentTimeUtc, @UserId OUTPUT
END
IF (EXISTS(SELECT * FROM dbo.aspnet_UsersInRoles WHERE @UserId = UserId AND @RoleId = RoleId))
BEGIN
SELECT @UserName, @RoleName
IF( @TranStarted = 1 )
ROLLBACK TRANSACTION
RETURN(3)
END
INSERT INTO dbo.aspnet_UsersInRoles (UserId, RoleId) VALUES(@UserId, @RoleId)
END
END
IF( @TranStarted = 1 )
COMMIT TRANSACTION
RETURN(0)
END'
EXEC sp_executesql @SqlToExec
IF (@ver > 7)
SELECT @SqlToExec = 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
'
ELSE
SELECT @SqlToExec = 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 @RoleId uniqueidentifier
DECLARE @UserId uniqueidentifier
DECLARE @UserName nvarchar(256)
DECLARE @RoleName nvarchar(256)
DECLARE @CurrentPosU int
DECLARE @NextPosU int
DECLARE @CurrentPosR int
DECLARE @NextPosR int
SELECT @CurrentPosU = 1
WHILE(@CurrentPosU <= LEN(@UserNames))
BEGIN
SELECT @NextPosU = CHARINDEX(N'','', @UserNames, @CurrentPosU)
IF (@NextPosU = 0 OR @NextPosU IS NULL)
SELECT @NextPosU = LEN(@UserNames)+1
SELECT @UserName = SUBSTRING(@UserNames, @CurrentPosU, @NextPosU - @CurrentPosU)
SELECT @CurrentPosU = @NextPosU+1
SELECT @CurrentPosR = 1
WHILE(@CurrentPosR <= LEN(@RoleNames))
BEGIN
SELECT @NextPosR = CHARINDEX(N'','', @RoleNames, @CurrentPosR)
IF (@NextPosR = 0 OR @NextPosR IS NULL)
SELECT @NextPosR = LEN(@RoleNames)+1
SELECT @RoleName = SUBSTRING(@RoleNames, @CurrentPosR, @NextPosR - @CurrentPosR)
SELECT @CurrentPosR = @NextPosR+1
SELECT @RoleId = NULL
SELECT @RoleId = RoleId FROM dbo.aspnet_Roles WHERE LoweredRoleName = LOWER(@RoleName) AND ApplicationId = @AppId
IF (@RoleId IS NULL)
BEGIN
SELECT N'''', @RoleName
IF( @TranStarted = 1 )
ROLLBACK TRANSACTION
RETURN(2)
END
SELECT @UserId = NULL
SELECT @UserId = UserId FROM dbo.aspnet_Users WHERE LoweredUserName = LOWER(@UserName) AND ApplicationId = @AppId
IF (@UserId IS NULL)
BEGIN
SELECT @UserName, N''''
IF( @TranStarted = 1 )
ROLLBACK TRANSACTION
RETURN(1)
END
IF (NOT(EXISTS(SELECT * FROM dbo.aspnet_UsersInRoles WHERE @UserId = UserId AND @RoleId = RoleId)))
BEGIN
SELECT @UserName, @RoleName
IF( @TranStarted = 1 )
ROLLBACK TRANSACTION
RETURN(3)
END
DELETE FROM dbo.aspnet_UsersInRoles WHERE (UserId = @UserId AND RoleId = @RoleId)
END
END
IF( @TranStarted = 1 )
COMMIT TRANSACTION
RETURN(0)
END
'
EXEC sp_executesql @SqlToExec
Исключение SQL:
System.Data.SqlClient.SqlException: Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Cyrillic_General_CI_AS" in the equal to operation.
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Cyrillic_General_CI_AS" in the equal to operation.
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Cyrillic_General_CI_AS" in the equal to operation.
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Cyrillic_General_CI_AS" in the equal to operation.
в System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
в System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
в System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
в System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
в System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
в System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
в System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
в System.Web.Management.SqlServices.ExecuteFile(String file, String server, String database, String dbFileName, SqlConnection connection, Boolean sessionState, Boolean isInstall, SessionStateType sessionStatetype)
