Hi Guys

I've tried to install the Dynamic User Directory Module (the one that comes with the Collection 20+ pack) and get this error upon installing it.

I'm using DNN version 4.0.9. 

Please help. Thanks Wesley

Info	Executing 02.40.20.SqlDataProvider
StartJob	Start Sql execution: 02.40.20.SqlDataProvider file
Failure	SQL Execution resulted in following Exceptions: System.Data.SqlClient.SqlException: Line 33: Incorrect syntax near 'Max'. Must declare the variable '@SQLToExecute'. Must declare the variable '@SQLToExecute'. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at DotNetNuke.Data.SqlDataProvider.ExecuteADOScript(String SQL) at DotNetNuke.Data.SqlDataProvider.ExecuteScript(String Script, Boolean UseTransactions) CREATE Procedure dbo.[DataSprings_RetrieveDNNUserProfileData3] @PortalID int, @RoleFilter nvarchar(1000), @LimitResults nvarchar(1000) AS Declare curDS Cursor FOR SELECT PropertyName, PropertyDefinitionID FROM dbo.[ProfilePropertyDefinition] where Visible=1 AND PortalID = @PortalID AND Deleted=0 AND PRopertyName <> 'FirstName' AND PropertyName <> 'LastName' AND PRopertyNAme <> '1.FachHG' AND PRopertyNAme <> 'Email' AND PropertyNAme Not Like '%.%' AND PropertyNAme Not Like '%-%' ORDER BY PropertyCategory, Vieworder FOR READ ONLY; Declare @PropertyName VarChar(500); Declare @PropertyDefinitionID Integer; Declare @SQLToUse VarChar(8000); Declare @SQLToUseJoin VarChar(8000); Declare @SQLToExecute nVarChar(Max); Declare @Alias VarChar(10); set @PropertyName = ''; set @SQLToUse = ''; set @SQLToUseJoin = ''; OPEN curDS; FETCH NEXT FROM CurDS INTO @PropertyName, @PropertyDefinitionID; WHILE @@FETCH_STATUS = 0 BEGIN if DataLength(@SQLToUse) = 0 BEGIN set @SQLToUse = 'select ' + @LimitResults + ' UserID,Username,FirstName, LastName, Email, ' set @SQLToUseJoin = 'dbo.DSGetProfileElementByID(userID,''' + Convert(varchar(4), @PropertyDefinitionID) + ''') as ' + @PropertyName END ELSE BEGIN set @SQLToUseJoin = @SQLToUseJoin + ',dbo.DSGetProfileElementByID(userID,''' + Convert(varchar(4), @PropertyDefinitionID) + ''') as ' + @PropertyName END; FETCH NEXT FROM CurDS INTO @PropertyName, @PropertyDefinitionID; END CLOSE CurDS; DEALLOCATE CurDS; set @SQLToUseJoin = @SQLToUseJoin + ' from dbo.[Users] Where UserID In (Select UserID from dbo.[UserPortals] where PortalID = ' + Convert(varchar(2), @PortalID) + ')' Set @SQLToExecute = 'Select * from (' + Convert(varchar(8000), @SQLToUse) + Convert(varchar(8000), @SQLToUseJoin) + ') AS A ' + Convert(varchar(8000), @RoleFilter) --print @SQLToExecute --EXEC(@SQLToExecute); --print @SQLToExecute; exec sp_executesql @SQLToExecute
EndJob	End Sql execution: 02.40.20.SqlDataProvider file

Hi Guys

I've installed this module successfully on my local machine after this HACK i did with the @SQLToExecute parameter. But i've installed it on our production server and installs properly but give this error when I go to Dynamic User Directory Settings or Initial Setup.

Error: Dynamic User Directory is currently unavailable.
DotNetNuke.Services.Exceptions.ModuleLoadException: Incorrect syntax near the keyword 'As'. Line 1: Incorrect syntax near ')'. ---> System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'As'. Line 1: Incorrect syntax near ')'. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.ConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior) at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteReader(SqlConnection connection, SqlTransaction transaction, CommandType commandType, String commandText, SqlParameter[] commandParameters, SqlConnectionOwnership connectionOwnership) at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteReader(String connectionString, CommandType commandType, String commandText, SqlParameter[] commandParameters) at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteReader(String connectionString, String spName, Object[] parameterValues) at DataSprings.DNN.Modules.DynamicUserDirectory.Data.SqlDataProvider.DataSprings_RetrieveDNNUserProfileData(Int32 PortalID, String RoleFilter, String LimitResults) at DataSprings.DNN.Modules.DynamicUserDirectory.Business.DynamicUserDirectoryController.DataSprings_RetrieveDNNUserProfileData(Int32 PortalID, String RoleFilter, String LimitResults) at DataSprings.DNN.Modules.DynamicUserDirectory.Common.GetDotNetNukeUserFields(Int32 PortalID, Int32 TabId, Int32 ModuleID) at DataSprings.DNN.Modules.DynamicUserDirectory.Settings.BindDotNetNukeFields() at DataSprings.DNN.Modules.DynamicUserDirectory.Settings.LoadSettings() --- End of inner exception stack trace --

Please could i get help in this.
works on my local machine but not live. I've got DR installed on our live(production) server as well.
Any ideas?

I found the problem. The problem for me was that some Dynamic Fields in DR had spaces in there names. I removed those and its working 100% now.
Thanks
Wes