Yup, still a problem. I am using uCanUse for registration and have the region required. It is coming through on the reg report just fine.
The proc below, using DNNStuff Sql view worked fine until the 4x upgrade. I have isolated the problem to the line:
INNER JOIN
dbo .aspnet_Profile aspnet_Profile_1 ON aspnet_Profile_1.userid = aspnet_Membership_1.userid
It seems that any new users aded since the upgrade - the UserId in the profile table does not match the UserId in the membership table. There is no common UserId, between the tables, returned when I test query. It returns up to the last user before the upgrade.
I can run the following and the current names, including registerd users post upgrade, return fine:
select Top 11 Users_1.UserName AS [Name]
FROM
dbo .Users Users_1
left join UserPortals UP on Users_1.UserId = UP.UserId
order by UP.CreatedDate DESC
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Here is the original proc that is returning all the information for users prior to the upgrade:
select Top 11 Users_1.UserName AS [Name], dbo.GetProfileElement('Region', PropertyNames, PropertyValuesString) AS Region
FROM
dbo .Users Users_1 INNER JOIN
dbo .aspnet_Users aspnet_Users_1 ON Users_1.username = aspnet_Users_1.username
INNER JOIN
dbo .aspnet_Membership aspnet_Membership_1 ON aspnet_Users_1.userid = aspnet_Membership_1.userid
INNER JOIN
dbo .aspnet_Profile aspnet_Profile_1 ON aspnet_Profile_1.userid = aspnet_Membership_1.userid
left join UserPortals UP on Users_1.UserId = UP.UserId
where (UP.PortalId = 1) and (aspnet_Membership_1.IsApproved = 1)
order by UP.CreatedDate DESC
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Here is the function:
CREATE FUNCTION [dbo].[GetProfileElement]
(
@fieldName AS NVARCHAR(100),
@fields AS NVARCHAR(4000),
@values AS NVARCHAR(4000)
)
RETURNS NVARCHAR(4000)
AS
BEGIN
-- If input is invalid, return null.
IF @fieldName IS NULL
OR LEN(@fieldName) = 0
OR @fields IS NULL
OR LEN(@fields) = 0
OR @values IS NULL
OR LEN(@values) = 0
RETURN NULL
-- locate FieldName in Fields
DECLARE @fieldNameToken AS NVARCHAR(20)
DECLARE @fieldNameStart AS INTEGER, @valueStart AS INTEGER, @valueLength AS INTEGER
-- Only handle string type fields (:S:)
SET @fieldNameStart = CHARINDEX(@fieldName + ':S',@Fields,0)
-- If field is not found, return null
IF @fieldNameStart = 0 RETURN NULL
SET @fieldNameStart = @fieldNameStart + LEN(@fieldName) + 3
-- Get the field token which I've defined as the start of the field offset to the end of the length
SET @fieldNameToken =
SUBSTRING(@Fields,@fieldNameStart,LEN(@Fields)-@fieldNameStart)
-- Get the values for the offset and length
SET @valueStart = dbo.getelement(1,@fieldNameToken,':')
SET @valueLength = dbo.getelement(2,@fieldNameToken,':')
-- Check for sane values, 0 length means the profile item was stored, just no data
IF @valueLength = 0 RETURN ''
-- Return the string
RETURN SUBSTRING(@values, @valueStart+1, @valueLength)
END
It is driving me nuts - LOL |