Error Installing Module
Last Post 02-23-2009 12:26 AM by Wesley Geland. 3 Replies.
|
|
Wesley Geland
going with the flow Posts:37
|
02-16-2009 12:25 AM |
|
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
|
|
|
|
|
|
Wesley Geland
going with the flow Posts:37
|
02-16-2009 01:18 AM |
|
Hi Guys...
I've checked the sql query in 02.40.20.SqlDataProvider file. I'm using SQL 2000 so i changed where you declare the @SQLToExecute nvarchar(max) to @SQLToExecute nvarchar(4000) and installed with no problems. I hope this doesn't change anything in the module. I'll check if the module gives me anymore errors during use with this change and keep you posted.
Thanks
Wesley |
|
|
|
|
Wesley Geland
going with the flow Posts:37
|
02-22-2009 09:43 PM |
|
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? |
|
|
|
|
Wesley Geland
going with the flow Posts:37
|
02-23-2009 12:26 AM |
|
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 |
|
|
|
|
|