Jelle,

Try this, I have not used it in sometime, but I'll assume that it will meet your needs.

--The aspnet_Profile table contains the following fields: UserId, PropertyNames, PropertyValuesString, PropertyValuesBinary, and LastUpdatedDate. The PropertyNames field contains a string delimited with colons (:) that identify which profile fields are stored, what their datatype is and their offset and length. For instance, in the following example it shows us that the Region field is a string field (S) starting at postion 32 and is 7 characters long.

--Ok, from the previous page we now know where the data is and how it's stored, but how do we get at that from T-SQL. SQL Server 2000 brought us UDF's (User Defined Functions) which allow us to bundle complex logic into reusable functions which we can use directly in SQL queries. I'm not going to go into the nitty gritty of UDF's here but we are going to be using two different ones to get at the data.

--The first one we'll need is one that will allow us to grab fields of delimited data from a string. Once we have the field offset and length data string such as 32:7, we will need this function to parse this into two separate integers, namely 32 and 7 so we can use the SUBSTRING function to grab the data from the PropertyValuesString field. The function to do that is here:


--RUN FIRST

CREATE FUNCTION dbo.fn_GetElement
(
@ord AS INT,
@str AS VARCHAR(8000),
@delim AS VARCHAR(1) )
RETURNS INT
AS
BEGIN
  -- If input is invalid, return null.
  IF  @str IS NULL
      OR LEN(@str) = 0
      OR @ord IS NULL
      OR @ord < 1
      -- @ord > [is the] expression that calculates the number of elements.
      OR @ord > LEN(@str) - LEN(REPLACE(@str, @delim, '')) + 1
    RETURN NULL
  DECLARE @pos AS INT, @curord AS INT
  SELECT @pos = 1, @curord = 1
  -- Find next element's start position and increment index.
  WHILE @curord < @ord
    SELECT
      @pos    = CHARINDEX(@delim, @str, @pos) + 1,
      @curord = @curord + 1
  RETURN    CAST(SUBSTRING(@str, @pos, CHARINDEX(@delim, @str + @delim, @pos) - @pos) AS INT)
END

--This function takes 3 parameters; @ord, @str and @delim. @ord is the field number you wish to return, @str is the string that you wish to parse, and @delim is the delimiter you are folding the string with. This is a very general UDF that can be used to return delimited fields for any t-sql and isn't just handy for this task. I found the source for this function at the Windows IT Pro site in an article by Itzik Ben-Gan. We'll be calling this function in our next function.

--The last UDF we need, is one that will take the field values from aspnet_Profile and parse them to return the proper data. This one, named fn_GetProfileElement also takes 3 parameters; @fieldName (the profile fieldname we want to grab), @fields (the string from PropertyNames), and @values (the string values from PropertyValuesString).


--RUN SECOND

CREATE FUNCTION dbo.fn_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.fn_getelement(1,@fieldNameToken,':')
SET @valueLength = dbo.fn_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

--Now, all we have to do is provide the proper call in our T-SQL query to this function and we should have our data. To do this, simply call it as follows:

SELECT dbo.fn_GetProfileElement('PostalCode',PropertyNames,PropertyValuesString) FROM aspnet_Profile

--In this example, I'm grabbing only the PostalCode field, but you could grab any field that is stored in the profile data. There are many other fields you can grab as shown in the following list: Unit, Street, City, Region, PostalCode, Country, Telephone, Fax, Cell, Website, IM, TimeZone and PreferredLocale.


--here's another sample which shows the proper joins to get at most of the information, plus a call to our new function to grab the profile data field named PostalCode.

select users.userid, users.username, users.firstname, users.lastname, roles.rolename, aspnet_membership.email,
dbo.fn_GetProfileElement('PostalCode',PropertyNames,PropertyValuesString) PostalCode

from users

inner join userroles on users.userid = userroles.userid
inner join roles on userroles.roleid = roles.roleid
inner join aspnet_users on users.username = aspnet_users.username
inner join aspnet_membership on aspnet_users.userid = aspnet_membership.userid
inner join aspnet_profile on aspnet_profile.userid = aspnet_membership.userid



 --CREATE view based on code

CREATE VIEW dbo.vw_ProfileProperties
AS
SELECT
Users_1.userid,
dbo.fn_GetProfileElement('FirstName', PropertyNames, PropertyValuesString) AS FirstName,
dbo.fn_GetProfileElement('LastName', PropertyNames, PropertyValuesString) AS LastName,
dbo.fn_GetProfileElement('Street', PropertyNames, PropertyValuesString) AS Street,
dbo.fn_GetProfileElement('City', PropertyNames, PropertyValuesString) AS City,
dbo.fn_GetProfileElement('Region', PropertyNames, PropertyValuesString) AS Region,
dbo.fn_GetProfileElement('Country', PropertyNames, PropertyValuesString) AS Country,
dbo.fn_GetProfileElement('PostalCode', PropertyNames, PropertyValuesString) AS PostalCode,
dbo.fn_GetProfileElement('Telephone', PropertyNames, PropertyValuesString) AS Telephone

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

---

there is a view so you can select * from vw_Users

it will return a recordset, with some of the columns being:

UserId, PortalId, Username, FirstName, LastName, DisplayName,...

While not all the profile fields you do get something.   Check it out

mj

 

---

Michael Jackson
Brillnat.com
Custom module development
Database access tokenized HTML modules