The stored procedure Messaging_GetNewMessageCount was created (along with other messaging related database objects) in DotNetNuke 5.03.00. Do you recall seeing any errors during upgrade to that version?
I suspect that there may have been other issues as well during the upgrade(s) but would suggest that you first try re-creating this stored procedure using the Host --> SQL menu function:
1. Paste the following script into the SQL text area.
2. Check the "Run as Script" box.
3. Click Execute script.
//*** Create Messaging_GetNewMessageCount Procedure ***//
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'{databaseOwner}[{objectQualifier}Messaging_GetNewMessageCount]') AND OBJECTPROPERTY(id, N'IsPROCEDURE') = 1)
DROP PROCEDURE {databaseOwner}{objectQualifier}Messaging_GetNewMessageCount
GO
CREATE PROCEDURE {databaseOwner}[{objectQualifier}Messaging_GetNewMessageCount]
@PortalID int,
@UserID int
AS
SELECT count(*) FROM {objectQualifier}Messaging_Messages WHERE ToUserID = @UserID AND Status = 1
GO
/*** End of Procedure Creation **/
In general I don't recommend an incremental upgrade such as this, particularly when including 5.3.0 and 5.4.2 which were known to have a number of issues. I've had good succes with going directly from 5.xx to 5.6.1. Others recommend including 5.4.4 as an intermediate version step which for a number of reasons (particularly if also switching to ASP.Net 4.0 or not planning to use content localization) also makes good sense.