Sebastian has this been tested with the newer versions of DotNetNuke, I am currently running version 7.4.2. When trying this under the SQL Host settings of the site I am plagued with errors stating object are not present.
System.Data.SqlClient.SqlException (0x80131904): Cannot find either column "dbo" or the user-defined function or aggregate "dbo.DNN_UserRoleNamesList", or the name is ambiguous.
at System.Data.SqlClient.SqlConnection. (SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.SqlInternalConnection. (SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at DotNetNuke.Data.SqlDataProvider.ExecuteScriptInternal(String connectionString, String script)
ClientConnectionId:d53cfcbf-9125-4cf6-b65f-4e880cbf543d
Error Number:4121,State:1,Class:16
-- =============================================
-- Author: Sebastian Leupold
-- Create date: 2011-04-09
-- Description: returns user data including pro-
-- file properties and role names
-- =============================================
CREATE View DotNetNuke.[UserFullList]
AS
SELECT Top 1000 UP.PortalID, U.UserID, UserName, FirstName, LastName, Displayname, Email,
Unit, Street, City, PostalCode, Region, Country, Cell, Telephone, Telefax, IM, WebSite, [Language], -- << User Profile Properties
dbo.DNN_UserRoleNamesList(UP.PortalID, UP.UserID, Null) Roles
FROM DotNetNuke.[Users] U
INNER JOIN DotNetNuke.[UserPortals] UP ON U.UserID = UP.UserID
-- access each Profile Property:
LEFT JOIN (SELECT PortalID, UserID, PropertyValue Unit FROM DotNetNuke.[ProfilePropertyDefinition] PD INNER JOIN DotNetNuke.[UserProfile] P ON PD.PropertyDefinitionID = P.PropertyDefinitionID WHERE PropertyName = N'Unit' ) P_Unit ON P_Unit.PortalID = UP.PortalID AND P_Unit.UserID = U.UserID
LEFT JOIN (SELECT PortalID, UserID, PropertyValue Street FROM DotNetNuke.[ProfilePropertyDefinition] PD INNER JOIN DotNetNuke.[UserProfile] P ON PD.PropertyDefinitionID = P.PropertyDefinitionID WHERE PropertyName = N'Street' ) P_Street ON P_Street.PortalID = UP.PortalID AND P_Street.UserID = U.UserID
LEFT JOIN (SELECT PortalID, UserID, PropertyValue City FROM DotNetNuke.[ProfilePropertyDefinition] PD INNER JOIN DotNetNuke.[UserProfile] P ON PD.PropertyDefinitionID = P.PropertyDefinitionID WHERE PropertyName = N'City' ) P_City ON P_City.PortalID = UP.PortalID AND P_City.UserID = U.UserID
LEFT JOIN (SELECT PortalID, UserID, PropertyValue PostalCode FROM DotNetNuke.[ProfilePropertyDefinition] PD INNER JOIN DotNetNuke.[UserProfile] P ON PD.PropertyDefinitionID = P.PropertyDefinitionID WHERE PropertyName = N'PostalCode' ) P_ZipCode ON P_ZipCode.PortalID = UP.PortalID AND P_ZipCode.UserID = U.UserID
LEFT JOIN (SELECT PortalID, UserID, PropertyValue Region FROM DotNetNuke.[ProfilePropertyDefinition] PD INNER JOIN DotNetNuke.[UserProfile] P ON PD.PropertyDefinitionID = P.PropertyDefinitionID WHERE PropertyName = N'Region' ) P_Region ON P_Region.PortalID = UP.PortalID AND P_Region.UserID = U.UserID
LEFT JOIN (SELECT PortalID, UserID, PropertyValue Country FROM DotNetNuke.[ProfilePropertyDefinition] PD INNER JOIN DotNetNuke.[UserProfile] P ON PD.PropertyDefinitionID = P.PropertyDefinitionID WHERE PropertyName = N'Country' ) P_Country ON P_Country.PortalID = UP.PortalID AND P_Country.UserID = U.UserID
LEFT JOIN (SELECT PortalID, UserID, PropertyValue Cell FROM DotNetNuke.[ProfilePropertyDefinition] PD INNER JOIN DotNetNuke.[UserProfile] P ON PD.PropertyDefinitionID = P.PropertyDefinitionID WHERE PropertyName = N'Cell' ) P_Cell ON P_Cell.PortalID = UP.PortalID AND P_Cell.UserID = U.UserID
LEFT JOIN (SELECT PortalID, UserID, PropertyValue Telephone FROM DotNetNuke.[ProfilePropertyDefinition] PD INNER JOIN DotNetNuke.[UserProfile] P ON PD.PropertyDefinitionID = P.PropertyDefinitionID WHERE PropertyName = N'Telephone' ) P_Telephone ON P_Telephone.PortalID = UP.PortalID AND P_Telephone.UserID = U.UserID
LEFT JOIN (SELECT PortalID, UserID, PropertyValue Telefax FROM DotNetNuke.[ProfilePropertyDefinition] PD INNER JOIN DotNetNuke.[UserProfile] P ON PD.PropertyDefinitionID = P.PropertyDefinitionID WHERE PropertyName = N'Fax' ) P_Telefax ON P_Telefax.PortalID = UP.PortalID AND P_Telefax.UserID = U.UserID
LEFT JOIN (SELECT PortalID, UserID, PropertyValue IM FROM DotNetNuke.[ProfilePropertyDefinition] PD INNER JOIN DotNetNuke.[UserProfile] P ON PD.PropertyDefinitionID = P.PropertyDefinitionID WHERE PropertyName = N'IM' ) P_IM ON P_IM.PortalID = UP.PortalID AND P_IM.UserID = U.UserID
LEFT JOIN (SELECT PortalID, UserID, PropertyValue Website FROM DotNetNuke.[ProfilePropertyDefinition] PD INNER JOIN DotNetNuke.[UserProfile] P ON PD.PropertyDefinitionID = P.PropertyDefinitionID WHERE PropertyName = N'Website' ) P_Website ON P_Website.PortalID = UP.PortalID AND P_Website.UserID = U.UserID
LEFT JOIN (SELECT PortalID, UserID, PropertyValue [Language] FROM DotNetNuke.[ProfilePropertyDefinition] PD INNER JOIN DotNetNuke.[UserProfile] P ON PD.PropertyDefinitionID = P.PropertyDefinitionID WHERE PropertyName = N'PreferredLocale') P_Language ON P_Language.PortalID = UP.PortalID AND P_Language.UserID = U.UserID
Order by PortalID, LastName, FirstName
|