The other day I was chatting with David Silverlight about the new Profile feature in ASP.NET 2.0. First of all I think this is really a great feature and VERY useful. I would like to thank the asp.net team for creating that feature.
However if you want to retrieve the profile properties/values via TSQL, then this is not an easy task. After chatting a while with David, he found an article written by Richard Edwards - he basically created some SQL UDF functions to retrieve specific profile values. Maybe the asp.net team can include these in the next sp/update :)
The udf functions can be pretty much used like this:
SELECT
Users_1.userid,
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
....
The complete article from Richard about the functions and how the profiles are stored in SQL can be read here.
Sonu
ps: Did you know that can have a similar personalization functionality in asp.net 1.1? Read more here.