AbsoluteURL:
DefaultDataProvider:DotNetNuke.Data.SqlDataProvider, DotNetNuke
ExceptionGUID:7dbcc787-8939-40ce-aacf-3f9cb5759728
AssemblyVersion:
PortalId:-1
UserId:-1
TabId:-1
RawUrl:
Referrer:
UserAgent:
ExceptionHash:2UdMDrykxHA+mjhsRpvW7Q==
Message:Conversion failed when converting the nvarchar value 'M.' to data type int.
StackTrace:
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.SqlDataReader.TryHasMoreRows(Boolean& moreRows)
at System.Data.SqlClient.SqlDataReader.TryReadInternal(Boolean setTimeout, Boolean& more)
at System.Data.SqlClient.SqlDataReader.Read()
at DotNetNuke.Services.Search.UserIndexer.FindModifiedUsers(Int32 portalId, DateTime startDateLocal, IDictionary`2 searchDocuments, IList`1 profileDefinitions, IList`1& indexedUsers, Int32& startUserId)
at DotNetNuke.Services.Search.UserIndexer.IndexSearchDocuments(Int32 portalId, Int32 scheduleId, DateTime startDateLocal, Action`1 indexer)
InnerMessage:
InnerStackTrace:
Source:.Net SqlClient Data Provider
FileName:
FileLineNumber:0
FileColumnNumber:0
Method:
Nom du serveur: SRVWEB1
=========================================================================================
I noticed that the error happen when the SQL vew vw_Profile is executed. I compare the SQL database views with a freshly loaded DNN installation and the views are different. My web site has extra views and the vw_Profile is calling an extra view called vw_ProfileBase.
The following code is the view from a freshly DNN installation
=========================================================================================
USE [DNN]
GO
/****** Object: View [dbo].[DNN_vw_Profile] Script Date: 08/12/2016 23:09:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[DNN_vw_Profile]
AS
SELECT
UP.UserID,
PD.PortalID,
PD.PropertyName,
CASE WHEN PropertyText IS NULL THEN PropertyValue ELSE PropertyText END AS PropertyValue,
UP.Visibility,
UP.ExtendedVisibility,
UP.LastUpdatedDate,
PD.PropertyDefinitionID
FROM dbo.[DNN_UserProfile] AS UP
INNER JOIN dbo.[DNN_ProfilePropertyDefinition] AS PD ON PD.PropertyDefinitionID = UP.PropertyDefinitionID
GO
=========================================================================================
The next is the modified code from my website
=========================================================================================
USE [apsqadmin]
GO
/****** Object: View [dbo].[DNN_vw_Profile] Script Date: 08/12/2016 23:08:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[DNN_vw_Profile]
AS
SELECT
P.UserID,
P.PortalID,
P.PropertyName,
CASE
WHEN P.TypeName = N'List' THEN IsNull(L.[Text], P.PropertyValue)
WHEN P.TypeName IN (N'Region', N'Country') THEN IsNull(M.[Text], M.[Value])
WHEN IsNull(P.PropertyText, N'') = N'' THEN IsNull(P.PropertyText, P.PropertyValue)
ELSE P.PropertyText
END AS PropertyValue,
P.Visibility,
P.ExtendedVisibility,
P.Deleted,
P.DataType,
P.TypeName,
P.LastUpdatedDate,
P.PropertyDefinitionID
FROM [dbo].[DNN_vw_ProfileBase] AS P
LEFT JOIN [dbo].[DNN_Lists] AS L ON P.PropertyName = L.ListName AND P.PropertyValue = L.Value
LEFT JOIN [dbo].[DNN_Lists] AS M ON Cast(P.PropertyValue AS Int) = M.EntryID
GO
=========================================================================================
And finally, the next code is the extrat view vw_ProfileBase
=========================================================================================
USE [apsqadmin]
GO
/****** Object: View [dbo].[DNN_vw_ProfileBase] Script Date: 08/13/2016 00:32:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- View vw_ProfileBase
CREATE VIEW [dbo].[DNN_vw_ProfileBase]
AS
SELECT
UP.UserID,
PD.PortalID,
PD.PropertyName,
UP.PropertyValue,
UP.PropertyText,
CASE WHEN PD.visible = 0 THEN 1
ELSE UP.Visibility
END Visibility,
CASE WHEN PD.visible = 1 and UP.Visibility = 3
THEN UP.ExtendedVisibility
ELSE N''
END ExtendedVisibility,
PD.Deleted,
PD.DataType,
DT.TypeName,
PD.PropertyDefinitionID,
CASE WHEN UP.LastUpdatedDate > PD.LastModifiedOnDate
THEN UP.LastUpdatedDate
ELSE PD.LastModifiedOnDate
END LastUpdatedDate
FROM [dbo].[DNN_UserProfile] AS UP
INNER JOIN [dbo].[DNN_ProfilePropertyDefinition] AS PD ON PD.PropertyDefinitionID = UP.PropertyDefinitionID
INNER JOIN [dbo].[DNN_vw_ProfileDataTypes] AS DT ON PD.DataType = DT.DataTypeID
GO
=========================================================================================
Any idea where that extra view comes? I am using Dynamic Registration, it could have been created by that module since this module added custom profile fields.
Is that could have been added by the turboScript?
When I run the view directly from SQL Server Management Studio, I am getting the following error:
Msg 245, Level 16, State 1, Line 2
Conversion failed when converting the nvarchar value 'Host' to data type int.
The following 3 lines are the data return by the view vw_ProfileBase. Not sure if the PortalID NULL is ok
UserID PortalID PropertyName PropertyValue PropertyText Visibility ExtendedVisibility Deleted DataType TypeName PropertyDefinitionID LastUpdatedDate
1 NULL FirstName Host NULL 2 0 349 Text 2 2015-05-28 06:43:16.653
1 NULL LastName User NULL 2 0 349 Text 4 2015-05-28 06:43:16.747
1 NULL Street NULL 2 0 349 Text 7 2015-05-04 22:14:17.747
Thank you for any help