none
Project Sevrer not accessable after Migration RRS feed

  • Question

  • Hello,

    I migrated my Project Server 2013 to Project Server 2016. Migration was successful, after 2 days PWA Portal is not accessable after investigation via Correlation ID i got below error:

    SqlError: 'Could not find stored procedure 'pub.MSP_WEB_SP_QRY_GetAllWebAdminSettingsIfChanged'.'

    Error log give below:

    Entering Monitored Scope (Project Server SubDal:FillTypedDataSet -- pub.MSP_WEB_SP_QRY_GetAllWebAdminSettingsIfChanged(StoredProcedure)). Parent=PlatformContext main ctor
    911f639e-8d65-3064-cf71-a3427b651b7a
    05/02/2018 10:41:09.09 w3wp.exe (:0x1A34)       
    0x6A2C Project Server                Database                      880i High
    System.Data.SqlClient.SqlException (0x80131904): Could not find stored procedure 'pub.MSP_WEB_SP_QRY_GetAllWebAdminSettingsIfChanged'.     at System.Data.SqlClient.SqlConnection.OnError(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.TryConsumeMetaData()     at System.Data.SqlClient.SqlDataReader.get_MetaData()     at System.Data.SqlClient.SqlCommand.FinishExecuteRe...
    911f639e-8d65-3064-cf71-a3427b651b7a
    05/02/2018 10:41:09.09* w3wp.exe (:0x1A34)       
    0x6A2C Project Server                Database                      880i High
    ...ader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption)     at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)     at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)     at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean re...
    911f639e-8d65-3064-cf71-a3427b651b7a
    05/02/2018 10:41:09.09* w3wp.exe (:0x1A34)       
    0x6A2C Project Server                Database                      880i High
    ...turnStream, String method)     at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)     at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)     at Microsoft.Office.Project.Server.Data.SqlSession.<>c__DisplayClass4.<Fill>b__3(SqlCommand cmd)     at Microsoft.Office.Project.Server.Data.SqlSession.ProcessRetryableSqlCommand(SqlCommand command, Boolean retryForDeadlock, Action`1 commandAction)  ClientConnectionId:6bdd56eb-25f3-41a1-b211-8b22958801c6  Error Number:2812,State:62,Class:16
    911f639e-8d65-3064-cf71-a3427b651b7a
    05/02/2018 10:41:09.09 w3wp.exe (:0x1A34)       
    0x6A2C Project Server                Database                      880k High
    at Microsoft.Office.Project.Server.DataAccessLayer.SqlSessionManager.SqlSessionAdapter.<>c__DisplayClass13.<FillDataSetUsingExistingTables>b__12()     at Microsoft.Office.Project.Server.DataAccessLayer.SqlSessionManager.SqlSessionAdapter.RetryableExecuteHandlerWithSession(Action retryableAction, Boolean allowRetry)     at Microsoft.Office.Project.Server.DataAccessLayer.SqlSessionManager.SqlSessionAdapter.LogSpecificSqlExceptions(Action action)     at Microsoft.Office.Project.Server.DataAccessLayer.SqlSessionManager.SqlSessionAdapter.FillTypedDataSet(DataSet typedDataSet, String[] tableNames, SqlCommand command, Boolean enforceConstraints)     at Microsoft.Office.Project.Server.DataAccessLayer.DAL.SubDal.<>c__DisplayClass1d.<FillTypedDataSet>b__1b(IProjectServerSqlSession session)     at... 911f639e-8d65-3064-cf71-a3427b651b7a
    05/02/2018 10:41:09.09* w3wp.exe (:0x1A34)       
    0x6A2C Project Server                Database                      880k High
    ... Microsoft.Office.Project.Server.DataAccessLayer.DAL.RunInActiveSession(Action`1 actionHandler)     at Microsoft.Office.Project.Server.DataAccessLayer.DAL.SubDal.FillTypedDataSet(DataSet typedDataSet, String[] tables, SqlCommand sqlCommand, Boolean enforceConstraints, String scopeName)     at Microsoft.Office.Project.Server.DataAccessLayer.DAL.SubDal.FillTypedDataSet(DataSet typedDataSet, String[] tables, String storedProcedureName, Int32 timeout, SqlParameter[] parameters)     at Microsoft.Office.Project.Server.DataAccessLayer.Generated.AdminDalBase.GetAllWebAdminSettingsIfChanged(Nullable`1 cachedVersion)     at Microsoft.Office.Project.Server.BusinessLayer.ServerSettings.SynchronousDataProvider(IPlatformContext context, Nullable`1 version)     at Microsoft.Office.Project.Server.Business...
    911f639e-8d65-3064-cf71-a3427b651b7a
    05/02/2018 10:41:09.09* w3wp.exe (:0x1A34)       
    0x6A2C Project Server                Database                      880k High
    ...Layer.ServerSettings.GetSettingsInternal(IPlatformContext context, ISqlBatchRequest batchRequest)     at Microsoft.Office.Project.Server.DataAccessLayer.AdminDal.ReadWebAdminSettings()     at Microsoft.Office.Project.Server.DataAccessLayer.AdminDal.GetDatabaseTimeout(DatabaseTimeoutType timeoutType)     at Microsoft.Office.Project.Server.Base.CachedContextProperties.GetCachedProperties(Guid siteId, Lazy`1 dal)     at Microsoft.Office.Project.Server.Base.ContextFactory.PlatformContext.InitCachedContextProperties()     at Microsoft.Office.Project.Server.Base.ContextFactory.PlatformContext..ctor(Guid trackingId, Guid siteId, SPUrlZone urlZone, CultureInfo languageCulture, CultureInfo localeCulture, Guid ulsCorrelationUid, DatabaseAccessLevel databaseAccessLevel, Boolean omitDatasetSchema, Boo... 911f639e-8d65-3064-cf71-a3427b651b7a
    05/02/2018 10:41:09.09* w3wp.exe (:0x1A34)       
    0x6A2C Project Server                Database                      880k High
    ...lean useSystemAccountForSPSite)     at Microsoft.Office.Project.Server.Base.ContextFactory.PlatformContext..ctor(Guid siteId, SPUrlZone urlZone, DatabaseAccessLevel databaseAccessLevel)     at Microsoft.Office.Project.Server.Base.ContextFactory.CreatePlatformContextAsSystemAccount(Guid siteId, SPUrlZone urlZone)     at Microsoft.Office.Project.PWA.PJContextIdentityAuthenticator.AuthenticateUser()     at Microsoft.Office.Project.Server.SecurityAuthorization.Authenticate()     at Microsoft.Office.Project.PWA.PJContext.Authenticate(IIdentity identity)     at Microsoft.Office.Project.PWA.PJContext.GetContext(Boolean isWebServiceCall, Boolean ignoreCachedContext, SPWeb web)     at Microsoft.Office.Project.PWA.PJContext.GetContext(Boolean isWebServiceCall, Boolean ignoreCachedContext)     at Mic... 911f639e-8d65-3064-cf71-a3427b651b7a
    05/02/2018 10:41:09.09* w3wp.exe (:0x1A34)       
    0x6A2C Project Server                Database                      880k High
    ...rosoft.Office.Project.PWA.PJBaseWebPartPage..ctor()     at ASP.DEFAULT_ASPX_915358707..ctor()     at __ASP.FastObjectFactory_app_web_default_aspx_915358707__mfdmpy7.Create_ASP_DEFAULT_ASPX_915358707()     at System.Web.Compilation.BuildManager.CreateInstanceFromVirtualPath(VirtualPath virtualPath, Type requiredBaseType, HttpContext context, Boolean allowCrossApp)     at System.Web.UI.PageHandlerFactory.GetHandlerHelper(HttpContext context, String requestType, VirtualPath virtualPath, String physicalPath)     at System.Web.HttpApplication.MaterializeHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()     at System.Web.HttpApplication.ExecuteStepImpl(IExecutionStep step)     at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)  ...
    911f639e-8d65-3064-cf71-a3427b651b7a
    05/02/2018 10:41:09.09* w3wp.exe (:0x1A34)       
    0x6A2C Project Server                Database                      880k High
    ...   at System.Web.HttpApplication.PipelineStepManager.ResumeSteps(Exception error)     at System.Web.HttpApplication.BeginProcessRequestNotification(HttpContext context, AsyncCallback cb)     at System.Web.HttpRuntime.ProcessRequestNotificationPrivate(IIS7WorkerRequest wr, HttpContext context)     at System.Web.Hosting.PipelineRuntime.ProcessRequestNotificationHelper(IntPtr rootedObjectsPointer, IntPtr nativeRequestContext, IntPtr moduleData, Int32 flags)     at System.Web.Hosting.PipelineRuntime.ProcessRequestNotification(IntPtr rootedObjectsPointer, IntPtr nativeRequestContext, IntPtr moduleData, Int32 flags)     at System.Web.Hosting.UnsafeIISMethods.MgdIndicateCompletion(IntPtr pHandler, RequestNotificationStatus& notificationStatus)     at System.Web.Hosting.UnsafeIISMethods.MgdIndicat...
    911f639e-8d65-3064-cf71-a3427b651b7a
    05/02/2018 10:41:09.09* w3wp.exe (:0x1A34)       
    0x6A2C Project Server                Database                      880k High
    ...eCompletion(IntPtr pHandler, RequestNotificationStatus& notificationStatus)     at System.Web.Hosting.PipelineRuntime.ProcessRequestNotificationHelper(IntPtr rootedObjectsPointer, IntPtr nativeRequestContext, IntPtr moduleData, Int32 flags)     at System.Web.Hosting.PipelineRuntime.ProcessRequestNotification(IntPtr rootedObjectsPointer, IntPtr nativeRequestContext, IntPtr moduleData, Int32 flags)
    911f639e-8d65-3064-cf71-a3427b651b7a
    05/02/2018 10:41:09.09 w3wp.exe (:0x1A34)       
    0x6A2C Project Server                Database                      880j High
    SqlError: 'Could not find stored procedure 'pub.MSP_WEB_SP_QRY_GetAllWebAdminSettingsIfChanged'.'    Source: '.Net SqlClient Data Provider' Number: 2812 State: 62 Class: 16 Procedure: '' LineNumber: 1 Server: 'SS-JSD-SPD6-DB1'
    911f639e-8d65-3064-cf71-a3427b651b7a
    05/02/2018 10:41:09.09 w3wp.exe (:0x1A34)       
    0x6A2C Project Server                Database                      tzku High
    ConnectionString: 'Data Source=SS-JSD-SPD6-DB1;Initial Catalog=EPM_SABIC2013_Prod_PWA_WSS_Content_DB;Integrated Security=True;Enlist=False;Pooling=True;Min Pool Size=0;Max Pool Size=100;Connect Timeout=60;Packet Size=8000;Application Name=SharePoint[w3wp][2][EPM_SABIC2013_Prod_PWA_WSS_Content_DB]'    Partition: NULL ConnectionState: Closed ConnectionTimeout: 60 ConnectionClientId:00000000-0000-0000-0000-000000000000
    911f639e-8d65-3064-cf71-a3427b651b7a
    05/02/2018 10:41:09.09 w3wp.exe (:0x1A34)       
    0x6A2C Project Server                Database                      tzkv High
    SqlCommand: 'pub.MSP_WEB_SP_QRY_GetAllWebAdminSettingsIfChanged'     CommandType: StoredProcedure CommandTimeout: 0     Parameter: '@siteId' Type: UniqueIdentifier Size: 0 Direction: Input Value: '5670eba9-58f3-44af-9a95-41fafa45596b'     Parameter: '@cachedVersion' Type: UniqueIdentifier Size: 0 Direction: Input Value: ''
    911f639e-8d65-3064-cf71-a3427b651b7a
    05/02/2018 10:41:09.09 w3wp.exe (:0x1A34)       
    0x6A2C Project Server                Project Server Database       
    a01qv Medium
    Checking if the database version changed [SiteId=5670eba9-58f3-44af-9a95-41fafa45596b].
    911f639e-8d65-3064-cf71-a3427b651b7a
    05/02/2018 10:41:09.09 w3wp.exe (:0x1A34)       
    0x6A2C Project Server                Project Server Database       
    a01qx Medium
    Database version is not stale. [SiteId=5670eba9-58f3-44af-9a95-41fafa45596b].
    911f639e-8d65-3064-cf71-a3427b651b7a

    Hammad Umar

    Wednesday, May 2, 2018 10:55 AM

Answers

  • Talk with your SQL Server support team and see if they changed any permissions, firewalls, etc.

    In the past I have had seen similar issues.  The SQL support team, make changes to databases or run maintenance jobs, that may be good for regular SQL databases, but they can break SharePoint. 

    The error messages are coming from SQL and permissions can prevent views from being available.


    Michael Wharton, Project MVP, MBA, PMP and a Great Guy <br/> Website http://www.WhartonComputer.com <br/> Blog http://MyProjectExpert.com contains my field notes and SQL queries

    Saturday, March 30, 2019 1:58 AM
    Moderator

All replies

  • Hello,

    You say after 2 days it is not accessible, did this work at all before this issue?

    Paul


    Paul Mather | Twitter | http://pwmather.wordpress.com | CPS | MVP | Downloads

    Friday, May 4, 2018 7:41 AM
    Moderator
  • Hello,

    Yes Paul, it was working perfectly fine.

    Hammad Umar


    Hammad Umar

    Friday, May 4, 2018 10:17 AM
  • Make sure the service accounts are not locked out with a bad password or two many attempts.  Also, check farmadmin and server account SQL server permissions have not been removed.   Check with the DBA and make sure they haven't made changes or done any maintenance on the SQL Server.

    In past experience, security folks or sql dba start changing things to to align with corporate standards and break things.


    Michael Wharton, Project MVP, MBA, PMP and a Great Guy <br/> Website http://www.WhartonComputer.com <br/> Blog http://MyProjectExpert.com contains my field notes and SQL queries

    Friday, May 4, 2018 10:23 AM
    Moderator
  • I already checked and found no issue, the procedure PWA is trying to call exist for PWA 2013 not in PWA 2016, which is surprize for me.

    Hammad Umar

    Sunday, May 6, 2018 11:42 AM
  • It sounds like you may have hard coded links on your home page. 

    Try accessing server settings

    http://your-domain/pwa/_layouts/15/pwa/Admin/Admin.aspx

    Or the site link page

    http://your-domain/pwa/_layouts/15/pwa/Admin/SiteMap.aspx

    and see if something is hard coded


    Michael Wharton, Project MVP, MBA, PMP and a Great Guy <br/> Website http://www.WhartonComputer.com <br/> Blog http://MyProjectExpert.com contains my field notes and SQL queries

    Tuesday, May 8, 2018 7:12 PM
    Moderator
  • Hi Hammad, 

    I am getting the same thing with my newly migrated PWA 2016 instance. It was working fine until one fine day it wasn't. Been trying to troubleshoot and I also noticed that it keeps referencing the old PWA 2013 stored procedures / views. 

    Did you ever get to the bottom of this mystery? 

    Wednesday, January 30, 2019 9:44 PM
  • The first thing I would do is run the SharePoint configuration wizard on all the nodes in farm and see if that fixes it.   The issue.

    If that doesn't resolve issue, I would open a support ticket with Microsoft and find out what windows patch that was recently applied and see if it can be removed or if Microsoft has another solution.


    Michael Wharton, Project MVP, MBA, PMP and a Great Guy <br/> Website http://www.WhartonComputer.com <br/> Blog http://MyProjectExpert.com contains my field notes and SQL queries

    Saturday, February 2, 2019 3:49 AM
    Moderator
  • I have the configuration wizard on the two front end and the one application server. It made no difference.

    The farm account is not locked. 

    I can access other non PWA site administration pages without any issues. Standard SharePoint admin pages like 

    • _layouts / 15 / settings.aspx
    • _layouts / 15 / AreaNavigationSettings.aspx
    • and so on

    I get error when I access PWA admin pages like 

    • _layouts / 15 / pwa / admin / sitemap.aspx 
    • _layouts / 15 / pwa / admin / Admin.aspx  

    I m igrated PWA from 2013 to 2016, everything went without any issues. Came back after the weekend and got this issue. 

    Scrapped the PWA installation and did the migration again a second time. No issues with the migration, it went smoothly and then two days later the same issue. 

    For some reson project server is trying to access pub.MSP_WEB_SP_QRY_GetAllWebAdminSettingsIfChangedRather Than Accessing  pjpub.MSP_WEB_SP_QRY_GetAllWebAdminSettingsIfChanged.

    I checked the 2013 database and the stored procedure is prefixed with pub, while the 2016 is prefixed with pjpub.

    Any clue what to why this is happening? I suspect it could be a timer job that overwrites the settings because everything works as soon as the migration is over.

    Here's a portion of my log

    w3wp.exe (0x1508) 0x10B8 SharePoint Foundation Monitoring nasq Medium Entering Monitored Scope (Project Server SubDal: FillTypedDataSet - pub.MSP_WEB_SP_QRY_GetAllWebAdminSettingsIfChanged (StoredProcedure)). Parent = Platform Context main ctor dc88c19e-3008-0039-a5b5-b905007ef451 System.Data.SqlClient.SqlException (0x80131904): Could not find stored procedure 'pub.MSP_WEB_SP_QRY_GetAllWebAdminSettingsIfChanged'.     at System.Data.SqlClient.SqlConnection.OnError (SqlException exception, Boolean break connection, 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.TryConsumeMetaData ()    
     at System.Data.SqlClient.SqlDataReader.get_MetaData ()    
     at System.Data.SqlClient.SqlCommand.FinishExecuteReader (SqlDataReader ds, RunBehavior runBehavior, string reset option string, Boolean IsInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)    
     at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds (CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean return stream, Boolean async, Int32 timeout, Task & task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)    
     at System.Data.SqlClient. SqlCommand.RunExecuteReader (CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task & task, Boolean & usedCache, Boolean asyncWrite, Boolean inRetry)    
     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.Office.Project.Server.Data.SqlSession. <> C__DisplayClass4. <Fill> b__3 (SqlCommand cmd)    
     at Microsoft.Office.Project.Server.Data.SqlSession.ProcessRetryableSqlCommand (SqlCommand command, Boolean retryForDeadlock, Action`1 commandAction) ClientConnectionId: 2c5df814-3db8-4338-85e2-0e1c0ad8a0e5 Error Number: 2812, State: 62, Class: 16


    Zuhair Ahmed Khan


    • Edited by Zuhair Khan Tuesday, February 19, 2019 5:38 PM changed text formatting
    Tuesday, February 19, 2019 4:22 PM
  • Hello,

    I think you might need to open a support ticket with Microsoft for this issue unless some one here has a resolution.

    Paul


    Paul Mather | Twitter | http://pwmather.wordpress.com | CPS | MVP | Downloads

    Tuesday, February 19, 2019 6:27 PM
    Moderator
  • The other thing to try, do you have all of the latest SharePoint 2016 (Project Server 2016 updates included) installed? The latest is February 2019: https://support.microsoft.com/en-us/help/4462155/description-of-the-security-update-for-sharepoint-enterprise-server This should be tested on a replicate Test farm first if possible then when you're happy, update Production.

    Paul


    Paul Mather | Twitter | http://pwmather.wordpress.com | CPS | MVP | Downloads

    Wednesday, February 20, 2019 8:36 AM
    Moderator
  • The Feb 2019 update is not installed. I'll install and remigrate and see if it helps. Thank you

    Zuhair Ahmed Khan

    Wednesday, February 20, 2019 3:31 PM
  • FYI.   When re-migrating, be sure that database are copied over and not using the previous migrated database.  The database get updated during the migration, so be sure you start with fresh.

    Also, what was latest SharePoint  (CU) was applied before the migration?


    Michael Wharton, Project MVP, MBA, PMP and a Great Guy <br/> Website http://www.WhartonComputer.com <br/> Blog http://MyProjectExpert.com contains my field notes and SQL queries

    Thursday, February 21, 2019 7:11 PM
    Moderator
  • Check that the service accounts on SQL server have appropriate permission to SharePoint and PWA databases.

    Sometimes, SQL admins come in later and lower the security profile to meet policy. Farm Admin and some services are sysadmin and then somebody removes permission.  May explain why it was working and then failed.


    Michael Wharton, Project MVP, MBA, PMP and a Great Guy <br/> Website http://www.WhartonComputer.com <br/> Blog http://MyProjectExpert.com contains my field notes and SQL queries

    Monday, March 4, 2019 4:51 PM
    Moderator
  • Hi. How are you ?

    I'd like to know if when you installed the CU Fev 2019 the problem was solved.. Or did you find another solution ?

    Thanks, Davi.

    Wednesday, March 27, 2019 2:42 PM
  • The Feb 2019 update is not installed. I'll install and remigrate and see if it helps. Thank you

    Zuhair Ahmed Khan


    Hi, Zuhair. How are you ?

    I'd like to know if when you installed the CU Fev 2019 the problem was solved.. Or did you find another solution ?

    Thanks, Davi.

    Wednesday, March 27, 2019 2:43 PM
  • Talk with your SQL Server support team and see if they changed any permissions, firewalls, etc.

    In the past I have had seen similar issues.  The SQL support team, make changes to databases or run maintenance jobs, that may be good for regular SQL databases, but they can break SharePoint. 

    The error messages are coming from SQL and permissions can prevent views from being available.


    Michael Wharton, Project MVP, MBA, PMP and a Great Guy <br/> Website http://www.WhartonComputer.com <br/> Blog http://MyProjectExpert.com contains my field notes and SQL queries

    Saturday, March 30, 2019 1:58 AM
    Moderator