Error message when you try to log on to a company in Microsoft Great Plains or in Microsoft Dynamics GP: "Stored Procedure smCleanupFilesBeforeLogin returned the following results"

Applies to: Dynamics GP 2010

Symptoms


When you try to log on to a company in Microsoft Dynamics GP or in Microsoft Business Solutions - Great Plains, you receive the following error message:
Stored Procedure smCleanupFilesBeforeLogin returned the following results: DBMS: XXX, Great Plains: XXX

Cause


This problem may occur for any one of the following causes:
  • Cause 1: The smCleanupFilesBeforeLogin stored procedure is damaged or missing. See Resolution 1.
  • Cause 2: The DYNSA user is not the database owner of the company database. See Resolution 2.
  • Cause 3: The permissions for Microsoft Great Plains users who are part of the DYNGRP role in Microsoft SQL Server are incorrect. See Resolution 3.
  • Cause 4: Records are stuck in the ACTIVITY table or in the SY00800 table. See Resolution 4.

Resolution


Resolution 1

Note Make sure that all users are logged off Microsoft Dynamics GP or Microsoft Great Plains before you follow these steps.
  1. Start SQL Server Management Studio or Query Analyzer. To do this, follow the steps for the program that you use.
    • Microsoft SQL Server 2005, Microsoft SQL Server 2005 Express, or Microsoft SQL Server 2008
      1. Click Start, point to All Programs, point to Microsoft SQL Server 2008 or to Microsoft SQL Server 2005, and then click SQL Server Management Studio.
        Note The Connect to Server window opens.
      2. In the Server name box, type the name of the instance of SQL Server.
      3. In the Authentication list, click SQL Authentication.
      4. In the User name box, type sa.
      5. In the Password box, type the password for the sa user, and then click Connect.
    • Microsoft SQL Server 2000
      1. Click Start, point to All Programs, point to Microsoft SQL Server, and then click Query Analyzer.
      2. Type the password for the sa user, and then click OK.
  2. On the File menu, click New.
  3. Click Database Engine Query.
  4. Re-create the stored procedure. Then, run the script against the company database. The following files are available for download from the Microsoft Dynamics File Exchange Server.
    Microsoft scanned these files for viruses. Microsoft used the most current virus-detection software that was available on the date that these files were posted. The files are stored on security-enhanced servers that help prevent any unauthorized changes to the files.

Resolution 2

Note Make sure that all users are logged off Microsoft Dynamics GP or Microsoft Great Plains before you follow these steps.
  1. Start SQL Server Management Studio or Query Analyzer. To do this, follow the steps for the program that you use.
    • Microsoft SQL Server 2005, Microsoft SQL Server 2005 Express, or Microsoft SQL Server 2008
      1. Click Start, point to All Programs, point to Microsoft SQL Server 2008 or to Microsoft SQL Server 2005, and then click SQL Server Management Studio.
        Note The Connect to Server window opens.
      2. In the Server name box, type the name of the instance of SQL Server.
      3. In the Authentication list, click SQL Authentication.
      4. In the User name box, type sa.
      5. In the Password box, type the password for the sa user, and then click Connect.
    • Microsoft SQL Server 2000
      1. Click Start, point to All Programs, point to Microsoft SQL Server, and then click Query Analyzer.
      2. Type the password for the sa user, and then click OK.
  2. On the File menu, click New.
  3. Click Database Engine Query.
  4. Use the following script to determine the database owner.
    sp_helpdb
    If the DYNSA user is not listed as the database owner (db_owner) in the results, make the DYNSA user the owner of the database. To do this, run the following script against the desired database.
    sp_changedbowner 'DYNSA' 

Resolution 3

Note Make sure that all users are logged off Microsoft Dynamics GP or Microsoft Great Plains before you follow these steps.
  1. Start SQL Server Management Studio or Query Analyzer. To do this, follow the steps for the program that you use.
    • Microsoft SQL Server 2005, Microsoft SQL Server 2005 Express, or Microsoft SQL Server 2008
      1. Click Start, point to All Programs, point to Microsoft SQL Server 2008 or to Microsoft SQL Server 2005, and then click SQL Server Management Studio.
        Note The Connect to Server window opens.
      2. In the Server name box, type the name of the instance of SQL Server.
      3. In the Authentication list, click SQL Authentication.
      4. In the User name box, type sa.
      5. In the Password box, type the password for the sa user, and then click Connect.
    • Microsoft SQL Server 2000
      1. Click Start, point to All Programs, point to Microsoft SQL Server, and then click Query Analyzer.
      2. Type the password for the sa user, and then click OK.
  2. On the File menu, click New.
  3. Click Database Engine Query.
  4. Run the following Grant.sql script against the DYNAMICS database and against all the company databases. This script gives the appropriate permissions to all Microsoft Great Plains users who are part of the DYNGRP role in SQL Server.
    /*Count : 1 */

    declare @cStatement varchar(255)

    declare G_cursor CURSOR for select 'grant select,update,insert,delete on [' + convert(varchar(64),name) + '] to DYNGRP' from sysobjects
    where (type = 'U' or type = 'V') and uid = 1

    set nocount on
    OPEN G_cursor
    FETCH NEXT FROM G_cursor INTO @cStatement
    WHILE (@@FETCH_STATUS <> -1)
    begin
    EXEC (@cStatement)
    FETCH NEXT FROM G_cursor INTO @cStatement
    end
    DEALLOCATE G_cursor

    declare G_cursor CURSOR for select 'grant execute on [' + convert(varchar(64),name) + '] to DYNGRP' from sysobjects
    where type = 'P'

    set nocount on
    OPEN G_cursor
    FETCH NEXT FROM G_cursor INTO @cStatement
    WHILE (@@FETCH_STATUS <> -1)
    begin
    EXEC (@cStatement)
    FETCH NEXT FROM G_cursor INTO @cStatement
    end
    DEALLOCATE G_cursor

Resolution 4

Note Make sure that all users are logged off Microsoft Dynamics GP or Microsoft Great Plains before you follow these steps.
  1. Start SQL Server Management Studio or Query Analyzer. To do this, follow the steps for the program that you use.
    • Microsoft SQL Server 2005, Microsoft SQL Server 2005 Express, or Microsoft SQL Server 2008
      1. Click Start, point to All Programs, point to Microsoft SQL Server 2008 or to Microsoft SQL Server 2005, and then click SQL Server Management Studio.
        Note The Connect to Server window opens.
      2. In the Server name box, type the name of the instance of SQL Server.
      3. In the Authentication list, click SQL Authentication.
      4. In the User name box, type sa.
      5. In the Password box, type the password for the sa user, and then click Connect.
    • Microsoft SQL Server 2000
      1. Click Start, point to All Programs, point to Microsoft SQL Server, and then click Query Analyzer.
      2. Type the password for the sa user, and then click OK.
  2. On the File menu, click New.
  3. Click Database Engine Query.
  4. Run the following script to remove any records that are stuck in the ACTIVITY table and in the BATCH ACTIVITY table.
    DELETE DYNAMICS..ACTIVITY
    DELETE DYNAMICS..SY00800