Developer forum

Forum » Dynamicweb 9.0 Upgrade issues » Getting Sql Exception occurred In ParagraphRepository.GetParagraphs after upgrade

Getting Sql Exception occurred In ParagraphRepository.GetParagraphs after upgrade

Martin Møller Christensen
Reply

Hi DW,

I have getting this error whenever I try and open some content from within DW. It is version 9.6.3.

Any ideas?

Sql Exception occurred In ParagraphRepository.GetParagraphs

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

Exception Details: System.ApplicationException: Sql Exception occurred In ParagraphRepository.GetParagraphs

Source Error: 

 

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.


Stack Trace: 
 

[ApplicationException: Sql Exception occurred In ParagraphRepository.GetParagraphs]
   Dynamicweb.Content.Data.ParagraphRepository.GetParagraphs(CommandBuilder commandBuilder) +372
   Dynamicweb.Content.ParagraphService.InitializeIndex() +78
   System.Lazy`1.CreateValue() +236
   System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() +31
   System.Lazy`1.get_Value() +14628573
   Dynamicweb.Content.ParagraphService.GetParagraphsByPageId(Int32 pageId, ParagraphSort sortBy) +215
   Dynamicweb.Admin.ParagraphList1.Page_Load(Object sender, EventArgs e) in E:\agent\_work\126\s\01 - Applications\Dynamicweb.Admin\DW96\Dynamicweb.Admin\Admin\Content\ParagraphList.aspx.vb:214
   System.Web.UI.Control.OnLoad(EventArgs e) +106
   System.Web.UI.Control.LoadRecursive() +68
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3785

 


Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.7.3282.0


Replies

 
Nicolai Pedersen
Nicolai Pedersen
Reply

What version of SQL server and compatibility mode of the database are you running?

BR Nicolai

 
Martin Møller Christensen
Reply

Running SQL server 12 with compatibility mode SQL Server 2005.

Changed the compatibility mode to SQL Server 2012, but the backoffice doesn't seem to load. It just spins.

 
Martin Møller Christensen
Reply

Okay it seems our test server was overheating a bit, which caused the spining. It seemed to work with the compatibility mode.

 
Nicolai Pedersen
Nicolai Pedersen
Reply
This post has been marked as an answer

Great!

Votes for this answer: 1
 
Lars Larsen
Lars Larsen
Reply

Hi

Just want to add my experience with the exact same problem but on v9.6.5. Changed the compatibility mode to "SQL Server 2014 (120)", but that didn't fix the problem. Changed again to compatibility mode "SQL Server 2012 (110)" and that fixed it smiley

 
Anders Ebdrup
Anders Ebdrup
Reply

Dear Nicolai,

 

We are starting to see this issue as well. The solution has +100.000 records in AccessUser and +13.000 paragraphs, and it looks like a timeout issue due to the very big join.

Can this sql statement somehow be optimized?

SELECT Paragraph.*, AccessUserCreated.AccessUserName AS AccessUserNameCreated,
                        AccessUserEdit.AccessUserName AS AccessUserNameEdit,
                        IIF(globalParagraph.Paragraphid is null, 0, 1) AS IsUsedAsGlobalParagraph FROM AccessUser AS AccessUserCreated RIGHT OUTER JOIN
                         AccessUser AS AccessUserEdit RIGHT OUTER JOIN
                         Paragraph AS Paragraph ON AccessUserEdit.AccessUserID = Paragraph.ParagraphUserEdit ON
                         AccessUserCreated.AccessUserID = Paragraph.ParagraphUserCreate
                         LEFT JOIN Paragraph AS globalParagraph ON globalParagraph.ParagraphGlobalID = Paragraph.Paragraphid

 

Best regards,

Anders

 
Anders Ebdrup
Anders Ebdrup
Reply

Dear Nicolai,

 

We have localized the issue to be the amount of text in this field: ParagraphText, which really slows down the query. The issue is from a solution with approx. 40 websites.

How can that issue be solved?

 

Best regards, Anders

 
Nicolai Pedersen
Nicolai Pedersen
Reply

Hi Anders

I have made an alternative way of loading. Attached a pirate dll you can try.

What field type is you ParagraphText? Is it nvachar(max) or something else?

 
Anders Ebdrup
Anders Ebdrup
Reply

Dear Nicolai,

 

The column is nvarchar(max) and we are somewhat getting nearly the same error (just with an extra step in the stacktrace):

 

Server Error in '/' Application.


Sql Exception occurred In ParagraphRepository.GetParagraphs

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.ApplicationException: Sql Exception occurred In ParagraphRepository.GetParagraphs

Source Error:

 

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.


Stack Trace:
 

[ApplicationException: Sql Exception occurred In ParagraphRepository.GetParagraphs]
   Dynamicweb.Content.Data.ParagraphRepository.GetParagraphs(CommandBuilder commandBuilder) +417
   Dynamicweb.Content.Data.ParagraphRepository.GetParagraphs() +176
   Dynamicweb.Content.ParagraphService.InitializeCache() +129
   Dynamicweb.Caching.DictionaryCache`2.InitializeCacheDictionary() +155
   System.Lazy`1.CreateValue() +734
   System.Lazy`1.LazyInitValue() +450
   Dynamicweb.Caching.DictionaryCache`2.GetAllValuesInternal() +27
   Dynamicweb.Caching.ServiceCache`2.GetAllValues() +106
   Dynamicweb.Content.ParagraphService.GetParagraphs() +64
   Dynamicweb.Content.ParagraphService.GetParagraphsByPageId(Int32 pageId, ParagraphSort sortBy) +200
   Dynamicweb.Frontend.Content.GetParagraphs(Int32 pageId) +404
   Dynamicweb.Frontend.Content.CreateContent(Int32 contentId) +184
   Dynamicweb.Frontend.PageView.GetContent() +94
   Dynamicweb.Frontend.ContentViewModelFactory.CreatePageViewModel(PageView pageView) +487
   Dynamicweb.Frontend.PageView.SetPageTemplateValues() +164
   Dynamicweb.Frontend.PageView.Output() +2234
   Dynamicweb.AspNet.DynamicwebHttpHandler.ProcessRequest(HttpContext context) +77
   System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +542
   System.Web.HttpApplication.ExecuteStepImpl(IExecutionStep step) +75
   System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +93
 
Nicolai Pedersen
Nicolai Pedersen
Reply

I think maybe the dll you got has errors in it. It did not pass our QA and code review...!

We have made another change completely removing parts of this. But we think it might be something else - maybe indexes on the accessuser or paragraph table.

Can you share which indexes you have on these tables. Is the database in our hosting so we can get hold of it?

BR Nicolai

 
Anders Ebdrup
Anders Ebdrup
Reply

Dear Nicolai,

 

The database is not hosted in your environment. But from what I can see the exception is caused by a timeout due to the amount of data in this field: ParagraphText.

The query is fast enough without the field, but very slow when the field is a part of the query.

 

Best regards,

Anders

 
Nicolai Pedersen
Nicolai Pedersen
Reply

ok - we need a copy of the database pls. Can you make that available - I'll have DW Care install a copy for us to look at.

Thanks, Nicolai

 

You must be logged in to post in the forum