Copied to clipboard

Flag this post as spam?

This post will be reported to the moderators as potential spam to be looked at


  • skiltz 500 posts 699 karma points
    Apr 13, 2010 @ 03:46
    skiltz
    0

    SQL Script for All Members and their Properties

    Does anyone have a SQL script that returns all members and their properties?  I need to be able to bind this to a gridview.  Thanks!

  • Shaun 248 posts 475 karma points
    Jun 16, 2010 @ 17:04
    Shaun
    0

    I too am looking for something like this, can anyone point me in the right direction?

  • ksrao 1 post 21 karma points
    Jun 16, 2010 @ 17:25
    ksrao
    0

    Hi,

     I hope you can get it by using ( just this an rough idea  )

                                                            SELECT  < all The properties what you want>

                                                           FROM   < The Members table> // where the list of users existing// ;

     Here you need not to use WHERE condition as you want to select all members.

     

    For further more SQL questions  <l> http://www.wiziq.com/online-tests/11428-sql-quiz-1 ; </l>

  • Shaun 248 posts 475 karma points
    Jun 16, 2010 @ 17:30
    Shaun
    0

    aah, that only returns the basic member properties, sorry, I should have said, I'm actually after custom member properties.

  • Lee Kelleher 3876 posts 14595 karma points MVP 9x admin c-trib
    Jun 16, 2010 @ 18:09
    Lee Kelleher
    3

    Hey Shaun (and skiltz), are you sure you want it a SQL script for this? Seriously, get ready to open a can-of-worms!!!

    Here's a little something I wrote for a client a year ago...

    SELECT
        mt.pk,
        d.contentNodeId,
        u.text,
        m.Email,
        m.LoginName,
        mt.propertytypeId,
        p.tabId,
        t.text AS tabName,
        p.Alias,
        p.Name,
        p.dataTypeId,
        dt.dbType,
        dt.controlId,
        d.id AS propertyId,
        d.dataInt,
        d.dataDate,
        d.dataNvarchar,
        d.dataNtext,
        mt.viewOnProfile,
        p.sortOrder
    FROM
        dbo.cmsMemberType AS mt INNER JOIN
        dbo.cmsPropertyType AS p ON mt.propertytypeId = p.id INNER JOIN
        dbo.cmsDataType AS dt ON p.dataTypeId = dt.nodeId INNER JOIN
        dbo.cmsTab AS t ON p.tabId = t.id AND mt.NodeId = t.contenttypeNodeId INNER JOIN
        dbo.cmsPropertyData AS d ON p.id = d.propertytypeid INNER JOIN
        dbo.cmsMember AS m ON m.nodeId = d.contentNodeId INNER JOIN
        dbo.umbracoNode AS u ON u.id = m.nodeId

    It's a beast of INNER JOINs, not a big fan of using it... but was forced into it by the client and other technical restrictions at the time.

    Ideally, I'd recommend that you use the ASP.NET Membership Provider API to retrieve member data ... but I'm also realistic, sometimes you need to go for the raw data - just to get the job done.

    Good luck with the SQL script, I can't promise that its going to do what you need it for ... but it's a start.

    Cheers, Lee.

  • Shaun 248 posts 475 karma points
    Jun 16, 2010 @ 19:10
    Shaun
    1

    hehe, cheers Lee. I used something similar in the end.

    It was only for a one-off data grab, so the beastliness of it wasn't really an issue for me.

  • skiltz 500 posts 699 karma points
    Jun 17, 2010 @ 12:53
    skiltz
    1

    This is what I use.

     

    create table myXML (xm_data xml)
    insert
    into MyXML (xm_data)
    select
      cmsContentXml
    .xml from cmsContentXML inner join cmsContent c on c.nodeid = cmsContentXml.nodeid
     
    where c.contentType =  1071
     
    select
    tab
    .col.value('@nodeName','varchar(50)') as 'ID',
    tab
    .col.value('@email','varchar(50)') as 'Email',
    tab
    .col.value('data[@alias][1]','varchar(50)') + ' ' + tab.col.value('data[@alias][2]','varchar(50)') as "Name",
    tab
    .col.value('data[@alias][1]','varchar(50)') as 'First Name',
    tab
    .col.value('data[@alias][2]','varchar(50)') as 'Last Name',
    tab
    .col.value('data[@alias][3]','varchar(50)') as 'Home Phone',
    tab
    .col.value('data[@alias][4]','varchar(50)') as 'Mobile Phone',
    tab
    .col.value('data[@alias][5]','varchar(50)') as 'Address',
    tab
    .col.value('data[@alias][6]','varchar(50)') as 'AvartarMediaID',
    tab
    .col.value('data[@alias][7]','varchar(50)') as 'ShowOnProfile'
    from myXML cross apply xm_data.nodes('node') tab (col)




    drop table
    MyXml
  • Rasmus Østergård 17 posts 78 karma points
    Sep 08, 2010 @ 13:28
    Rasmus Østergård
    0

    Thanks Lee - you saved me a lot of tedious work

  • Rob Watkins 343 posts 591 karma points
    Jul 09, 2012 @ 17:05
    Rob Watkins
    0

    Can of worms as may be, but it's a very good technique when the client has asked for a list of everyone missing a particular property :o)

    Thanks Lee, worked perfectly.

    For anyone else using MySQL, just lose the dbo.

  • TikTakToe 57 posts 97 karma points
    May 26, 2016 @ 13:17
    TikTakToe
    1

    following on from lee's snippet, slight tweak for later versions (as tab table has gone) and filtering on username

    SELECT        mt.pk, d.contentNodeId, u.text, m.Email, m.LoginName, mt.propertytypeId, p.Alias, p.Name, p.dataTypeId, dt.dbType, d.id AS propertyId, d.dataInt, d.dataDate, d.dataNvarchar, d.dataNtext, mt.viewOnProfile, 
                             p.sortOrder
    FROM            dbo.cmsMemberType AS mt INNER JOIN
                             dbo.cmsPropertyType AS p ON mt.propertytypeId = p.id INNER JOIN
                             dbo.cmsDataType AS dt ON p.dataTypeId = dt.nodeId INNER JOIN
                             dbo.cmsPropertyData AS d ON p.id = d.propertytypeid INNER JOIN
                             dbo.cmsMember AS m ON m.nodeId = d.contentNodeId INNER JOIN
                             dbo.umbracoNode AS u ON u.id = m.nodeId
    WHERE        (m.LoginName = N'iantest@iantest.com')
    
  • Bjarne Fyrstenborg 1140 posts 3225 karma points MVP 3x c-trib
    Oct 03, 2018 @ 07:17
    Bjarne Fyrstenborg
    0

    I extended the SQL snippet a bit, so it also included the content type alias.

    SELECT      mt.pk, d.contentNodeId, c.Alias, u.text, m.Email, m.LoginName, mt.propertytypeId, p.Alias, p.Name, p.dataTypeId, dt.dbType, d.id AS propertyId, d.dataInt, d.dataDate, d.dataNvarchar, d.dataNtext, mt.viewOnProfile, 
                             p.sortOrder
    FROM        dbo.cmsMemberType AS mt INNER JOIN
                dbo.cmsPropertyType AS p ON mt.propertytypeId = p.id INNER JOIN
                dbo.cmsDataType AS dt ON p.dataTypeId = dt.nodeId INNER JOIN
                dbo.cmsPropertyData AS d ON p.id = d.propertytypeid INNER JOIN
                dbo.cmsMember AS m ON m.nodeId = d.contentNodeId INNER JOIN
                dbo.umbracoNode AS u ON u.id = m.nodeId INNER JOIN
                dbo.cmsContentType AS c ON c.nodeId = mt.NodeId
    

    So I could use this to query for a specific content type (e.g. a member type):

    SELECT      mt.pk, d.contentNodeId, c.Alias, u.text, m.Email, m.LoginName, mt.propertytypeId, p.Alias, p.Name, p.dataTypeId, dt.dbType, d.id AS propertyId, d.dataInt, d.dataDate, d.dataNvarchar, d.dataNtext, mt.viewOnProfile, 
                             p.sortOrder
    FROM        dbo.cmsMemberType AS mt INNER JOIN
                dbo.cmsPropertyType AS p ON mt.propertytypeId = p.id INNER JOIN
                dbo.cmsDataType AS dt ON p.dataTypeId = dt.nodeId INNER JOIN
                dbo.cmsPropertyData AS d ON p.id = d.propertytypeid INNER JOIN
                dbo.cmsMember AS m ON m.nodeId = d.contentNodeId INNER JOIN
                dbo.umbracoNode AS u ON u.id = m.nodeId INNER JOIN
                dbo.cmsContentType AS c ON c.nodeId = mt.NodeId
    
    WHERE       c.Alias = 'employee' AND (p.Alias = 'company' AND d.dataNvarchar IS NULL)
    

    /Bjarne

  • Tim 164 posts 325 karma points
    Jul 06, 2016 @ 07:51
    Tim
    101

    For anyone still looking for this sort of thing, here's a slightly neater one that produces a table of all the properties in a slightly more usable format, I think you'll need SQL Server 2005+ to use it.

    I'll keep the code updated on my blog: http://thesitedoctor.co.uk/blog/export-umbraco-members-as-csvtable/ but here it is if you want it:

    DECLARE @cols AS NVARCHAR(MAX), @query  AS NVARCHAR(MAX);
    
    SET @cols = STUFF((
                SELECT ',' + QUOTENAME(pt.Name)
                FROM
                    (SELECT [Id] FROM dbo.umbracoNode WHERE nodeObjectType = '9b5416fb-e72f-45a9-a07b-5a9a2709ce43') AS mt
                    LEFT OUTER JOIN (SELECT NodeID, contentType FROM dbo.cmsContent) AS ml ON ml.contentType = mt.id
                    LEFT JOIN dbo.cmsPropertyType AS pt ON pt.contentTypeId = ml.contentType
                GROUP BY pt.sortOrder, ',' + QUOTENAME(pt.Name)
                ORDER BY pt.sortOrder ASC
                FOR XML PATH(''), TYPE
                ).value('.', 'NVARCHAR(MAX)'),1,1,'')
    
    set @query = '
    SELECT
        LoginName,
        Email,
        createDate,
        MemberGroup,
        ' + @cols + '
    FROM
    (
        SELECT
        pt.Name AS MemberFieldName
        ,    ISNULL(CASE
                WHEN dt.DBTYPE = ''Ntext'' THEN CAST(d.[dataNtext] AS NVARCHAR(MAX))
                WHEN dt.DBTYPE = ''Nvarchar'' THEN d.dataNvarchar
                WHEN dt.DBTYPE = ''Date'' THEN CONVERT(nvarchar, d.[dataDate])
                WHEN dt.DBTYPE = ''Integer'' THEN CONVERT(nvarchar, d.[dataInt])
                ELSE NULL
            END, NULL)
            AS MemberData
        ,    m.LoginName
        ,    m.Email
        ,    n.createDate
        ,   g.[Text] AS MemberGroup
        FROM
            (SELECT [Id] FROM dbo.umbracoNode WHERE nodeObjectType = ''9b5416fb-e72f-45a9-a07b-5a9a2709ce43'') AS mt
                LEFT OUTER JOIN (SELECT NodeID, contentType FROM dbo.cmsContent) AS ml ON ml.contentType = mt.id
                LEFT JOIN dbo.cmsPropertyType AS pt ON pt.contentTypeId = ml.contentType
                LEFT JOIN [dbo].[cmsDataType] AS dt ON pt.datatypeID = dt.NodeId
                LEFT JOIN dbo.cmsPropertyData AS d ON d.contentNodeId = ml.NodeID AND d.propertytypeid = pt.id
                LEFT JOIN dbo.cmsMember AS m ON m.NodeID = ml.NodeID
                LEFT JOIN dbo.cmsMember2MemberGroup AS xmg ON xmg.Member = m.NodeID
                LEFT JOIN dbo.umbracoNode AS g ON g.id = xmg.MemberGroup
                LEFT JOIN dbo.umbracoNode AS n ON n.id = m.nodeId
    ) As src
    PIVOT (
        MAX(MemberData)
        FOR MemberFieldName in (' + @cols + ')
    ) aS pvt
    ORDER BY LoginName'
    
    print(@query)
    execute(@query);
    
  • Nino 1 post 71 karma points
    Oct 23, 2016 @ 13:25
    Nino
    0

    thank you Tim

    it works perectly

  • Tim 164 posts 325 karma points
    Oct 23, 2016 @ 19:54
    Tim
    0

    Ah good to hear Nino, you're welcome :)

  • g3t 32 posts 144 karma points
    Feb 25, 2019 @ 17:29
    g3t
    0

    In the script above from Tim how would you include lastlogindate. Thanks

  • John Bergman 429 posts 982 karma points
    Oct 03, 2018 @ 15:39
    John Bergman
    0

    If you have a LOT of fields in the member record, it doesn't work because they query is longer than what fits in the NVARCHAR(max)

Please Sign in or register to post replies

Write your reply to:

Draft