hofa_7
دوشنبه 11 مرداد 1389, 21:01 عصر
سلام
وقتی می خوام کوئری های مربوط به Membership رو روی sql هاست اجرا کنم با خطای زیر مواجه می شم
کسی می دونه مشکل کجاست :
Exception:
An error occurred during the execution of the SQL file 'InstallRoles.sql'. The SQL error number is 468 and the SqlException message is: Cannot resolve the collation conflict between "Arabic_CI_AI" and "SQL_Latin1_General_CP1256_CI_AS" in the equal to operation.
Cannot resolve the collation conflict between "Arabic_CI_AI" and "SQL_Latin1_General_CP1256_CI_AS" in the equal to operation.
Cannot resolve the collation conflict between "Arabic_CI_AI" and "SQL_Latin1_General_CP1256_CI_AS" in the equal to operation.
Cannot resolve the collation conflict between "Arabic_CI_AI" and "SQL_Latin1_General_CP1256_CI_AS" in the equal to operation.
Cannot resolve the collation conflict between "Arabic_CI_AI" and "SQL_Latin1_General_CP1256_CI_AS" in the equal to operation.
Cannot resolve the collation conflict between "Arabic_CI_AI" and "SQL_Latin1_General_CP1256_CI_AS" in the equal to operation.
Cannot resolve the collation conflict between "Arabic_CI_AI" and "SQL_Latin1_General_CP1256_CI_AS" in the equal to operation.
Cannot resolve the collation conflict between "Arabic_CI_AI" and "SQL_Latin1_General_CP1256_CI_AS" in the equal to operation.
Canno...
----------------------------------------
Details of failure
----------------------------------------
SQL Server:
Database: [DatabaseName]
SQL file loaded:
InstallRoles.sql
Commands failed:
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 Exception:
System.Data.SqlClient.SqlException: Cannot resolve the collation conflict between "Arabic_CI_AI" and "SQL_Latin1_General_CP1256_CI_AS" in the equal to operation.
Cannot resolve the collation conflict between "Arabic_CI_AI" and "SQL_Latin1_General_CP1256_CI_AS" in the equal to operation.
Cannot resolve the collation conflict between "Arabic_CI_AI" and "SQL_Latin1_General_CP1256_CI_AS" in the equal to operation.
Cannot resolve the collation conflict between "Arabic_CI_AI" and "SQL_Latin1_General_CP1256_CI_AS" in the equal to operation.
Cannot resolve the collation conflict between "Arabic_CI_AI" and "SQL_Latin1_General_CP1256_CI_AS" in the equal to operation.
Cannot resolve the collation conflict between "Arabic_CI_AI" and "SQL_Latin1_General_CP1256_CI_AS" in the equal to operation.
Cannot resolve the collation conflict between "Arabic_CI_AI" and "SQL_Latin1_General_CP1256_CI_AS" in the equal to operation.
Cannot resolve the collation conflict between "Arabic_CI_AI" and "SQL_Latin1_General_CP1256_CI_AS" in the equal to operation.
Cannot resolve the collation conflict between "Arabic_CI_AI" and "SQL_Latin1_General_CP1256_CI_AS" in the equal to operation.
at System.Data.SqlClient.SqlConnection.OnError(SqlExc eption exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnErro r(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndW arning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQuer yTds(String methodName, Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNo nQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at System.Web.Management.SqlServices.ExecuteFile(Stri ng file, String server, String database, String dbFileName, SqlConnection connection, Boolean sessionState, Boolean isInstall, SessionStateType sessionStatetype)
وقتی می خوام کوئری های مربوط به Membership رو روی sql هاست اجرا کنم با خطای زیر مواجه می شم
کسی می دونه مشکل کجاست :
Exception:
An error occurred during the execution of the SQL file 'InstallRoles.sql'. The SQL error number is 468 and the SqlException message is: Cannot resolve the collation conflict between "Arabic_CI_AI" and "SQL_Latin1_General_CP1256_CI_AS" in the equal to operation.
Cannot resolve the collation conflict between "Arabic_CI_AI" and "SQL_Latin1_General_CP1256_CI_AS" in the equal to operation.
Cannot resolve the collation conflict between "Arabic_CI_AI" and "SQL_Latin1_General_CP1256_CI_AS" in the equal to operation.
Cannot resolve the collation conflict between "Arabic_CI_AI" and "SQL_Latin1_General_CP1256_CI_AS" in the equal to operation.
Cannot resolve the collation conflict between "Arabic_CI_AI" and "SQL_Latin1_General_CP1256_CI_AS" in the equal to operation.
Cannot resolve the collation conflict between "Arabic_CI_AI" and "SQL_Latin1_General_CP1256_CI_AS" in the equal to operation.
Cannot resolve the collation conflict between "Arabic_CI_AI" and "SQL_Latin1_General_CP1256_CI_AS" in the equal to operation.
Cannot resolve the collation conflict between "Arabic_CI_AI" and "SQL_Latin1_General_CP1256_CI_AS" in the equal to operation.
Canno...
----------------------------------------
Details of failure
----------------------------------------
SQL Server:
Database: [DatabaseName]
SQL file loaded:
InstallRoles.sql
Commands failed:
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 Exception:
System.Data.SqlClient.SqlException: Cannot resolve the collation conflict between "Arabic_CI_AI" and "SQL_Latin1_General_CP1256_CI_AS" in the equal to operation.
Cannot resolve the collation conflict between "Arabic_CI_AI" and "SQL_Latin1_General_CP1256_CI_AS" in the equal to operation.
Cannot resolve the collation conflict between "Arabic_CI_AI" and "SQL_Latin1_General_CP1256_CI_AS" in the equal to operation.
Cannot resolve the collation conflict between "Arabic_CI_AI" and "SQL_Latin1_General_CP1256_CI_AS" in the equal to operation.
Cannot resolve the collation conflict between "Arabic_CI_AI" and "SQL_Latin1_General_CP1256_CI_AS" in the equal to operation.
Cannot resolve the collation conflict between "Arabic_CI_AI" and "SQL_Latin1_General_CP1256_CI_AS" in the equal to operation.
Cannot resolve the collation conflict between "Arabic_CI_AI" and "SQL_Latin1_General_CP1256_CI_AS" in the equal to operation.
Cannot resolve the collation conflict between "Arabic_CI_AI" and "SQL_Latin1_General_CP1256_CI_AS" in the equal to operation.
Cannot resolve the collation conflict between "Arabic_CI_AI" and "SQL_Latin1_General_CP1256_CI_AS" in the equal to operation.
at System.Data.SqlClient.SqlConnection.OnError(SqlExc eption exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnErro r(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndW arning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQuer yTds(String methodName, Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNo nQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at System.Web.Management.SqlServices.ExecuteFile(Stri ng file, String server, String database, String dbFileName, SqlConnection connection, Boolean sessionState, Boolean isInstall, SessionStateType sessionStatetype)