Welcome to the DNN Community Forums, your preferred source of online community support for all things related to DNN.
In order to participate you must be a registered DNNizen

HomeHomeUsing DNN Platf...Using DNN Platf...Administration ...Administration ...Help needed with a schedule task error.Help needed with a schedule task error.
Previous
 
Next
New Post
8/13/2016 12:48 AM
 
Hi,
I am getting the following error every 60 minutes when the schedule task "Search: Site Crawler" is executed. To troubleshoot the problem, I have downloaded a copy of the database and the website to a local server and I ws able to pinpoint the problem.

 

=========================================================================================

2016-08-13 00:14:12
General Exception 
 
 
AbsoluteURL DefaultDataProvider DotNetNuke.Data.SqlDataProvider DotNetNuke 

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

 
New Post
8/13/2016 6:02 AM
 
Stephen,
these are indeed modifications of TurboDNN. You are not running latest version; the issue, you are facing, has been fixed meanwhile and replaced by a more efficient method (using a calculated column with an index on it). Please update your site with latest turbo scripts from https://dnnscript.codeplex.com. Make sure to run TurboUnschema first, before re-applying TurboDNN and TurboSchema. Thanks.

Cheers from Germany,
Sebastian Leupold (Microsoft MVP)

dnnWerk - The DotNetNuke Experts   German Spoken DotNetNuke User Group

Speed up your DNN Websites with TurboDNN
 
New Post
8/13/2016 9:56 AM
 
Thank you Sebastien for a quick reply.

Do I need to run the unschema from the script version that is already installed?

If it is the case, where I can see what version it is actually installed?
 
New Post
8/13/2016 4:55 PM
 
Stephan,
please use unschema included with latest package.

Cheers from Germany,
Sebastian Leupold (Microsoft MVP)

dnnWerk - The DotNetNuke Experts   German Spoken DotNetNuke User Group

Speed up your DNN Websites with TurboDNN
 
Previous
 
Next
HomeHomeUsing DNN Platf...Using DNN Platf...Administration ...Administration ...Help needed with a schedule task error.Help needed with a schedule task error.


These Forums are dedicated to discussion of DNN Platform and Evoq Solutions.

For the benefit of the community and to protect the integrity of the ecosystem, please observe the following posting guidelines:

  1. No Advertising. This includes promotion of commercial and non-commercial products or services which are not directly related to DNN.
  2. No vendor trolling / poaching. If someone posts about a vendor issue, allow the vendor or other customers to respond. Any post that looks like trolling / poaching will be removed.
  3. Discussion or promotion of DNN Platform product releases under a different brand name are strictly prohibited.
  4. No Flaming or Trolling.
  5. No Profanity, Racism, or Prejudice.
  6. Site Moderators have the final word on approving / removing a thread or post or comment.
  7. English language posting only, please.

Content Layout

Subscribe to DNN Digest

Subscribe to DNN Digest

DNN Digest is our monthly email newsletter. It highlights news and content from around the DNN ecosystem, such as new modules and themes, messages from leadership, blog posts and notable tweets. Keep your finger on the pulse of the ecosystem by subscribing.  


Copyright 2017 by DNN Corp Terms of Use Privacy
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out