Customer Account Change - Error executing SQL statement when updating CRM Company ID! Error Code = 133

we are receiving this error when attempting to change a customer account code.  There seems to be a lot of issues with the account change programs these days.  I found in the past that it was missing files that needed to be copied over but this error has me baffled.  They do not even run CRM.  has anyone ran into this?  Is there something about what I copied over for program files that in some what would make it think they have CRM running?

Parents Reply Children
  • 0 in reply to ValerieH

    My apologies I misread what you wrote earlier.  If CRM isn't installed anywhere, and you haven't activated the EW module (CRM integration from Sage 300 side) then it must be some other issue.  Firstly run RVSPY logger when you next attempt the Customer Code Change and see if that can pickup any references to strange tables/subclassing at the time the error occurs. Also, open the Activity Monitor on the SQL server at the same time and look at what connections are being made to your Sage 300 database at the time you kick off the Customer Number Change.  Finally, if none of that helps, I just googled SQL Error 133 and it could be a deadlock!  I have a query that will really help.  Run the Customer Number Change then immediately at the time the error occurs, execute the following query:

    --Copy Below----------------------------------------------------------------------------------->

        --SET @SPID = 10
        --SET @Status = 'SUSPENDED'
        --SET @LOGIN = 'sa'
        --SET @HostName = 'MSSQL-1'
        --SET @BlkBy <> 0
        --SET @DBName = 'accpac_DIXDAT'
        --SET @Command = 'SELECT INTO'
        --SET @CPUTime = 1000
        --SET @DiskIO = 1000
        --SET @LastBatch = '10/24 10:00:00'
        --SET @ProgramName = 'Microsoft SQL Server Management Studio - Query'
        --SET @SPID_1 = 10
        --SET @REQUESTID = 0
        DECLARE @Table TABLE(
                SPID INT,
                Status VARCHAR(MAX),
                LOGIN VARCHAR(MAX),
                HostName VARCHAR(MAX),
                BlkBy VARCHAR(MAX),
                DBName VARCHAR(MAX),
                Command VARCHAR(MAX),
                CPUTime INT,
                DiskIO INT,
                LastBatch VARCHAR(MAX),
                ProgramName VARCHAR(MAX),
                SPID_1 INT,
                REQUESTID INT
        INSERT INTO @Table EXEC sp_who2
        SELECT  *
        FROM    @Table
        (@Spid IS NULL OR SPID = @Spid)
        AND (@Status IS NULL OR Status = @Status)
        AND (@Login IS NULL OR Login = @Login)
        AND (@HostName IS NULL OR HostName = @HostName)
        AND (@BlkBy IS NULL OR BlkBy = @BlkBy)
        AND (@DBName IS NULL OR DBName = @DBName)
        AND (@Command IS NULL OR Command = @Command)
        AND (@CPUTime IS NULL OR CPUTime >= @CPUTime)
        AND (@DiskIO IS NULL OR DiskIO >= @DiskIO)
        AND (@LastBatch IS NULL OR LastBatch >= @LastBatch)
        AND (@ProgramName IS NULL OR ProgramName = @ProgramName)
        AND (@SPID_1 IS NULL OR SPID_1 = @SPID_1)
    --Copy Above----------------------------------------------------------------------------------->
    Before you execute this when the error occurs, make sure you've changed the database name at the top there where the variables are to be the database name in which you're making changes i.e. in this example the database above is 'SAMINC' but in your case it may be 'SAGE300-MYDATA' etc then check the results.  What you're looking for is a row where the BlkBy column has a number in it instead of just '-'.  This means one of the processes in SQL is being blocked by another process.  The number in BlkBy will correspond to the SPID letting you see what process in that database is killing your Customer Number Change.  Feel free to just copy/paste a screenshot from the Management Studio showing your results for us to see if we can identify the issue.
    Here is an example of me running the query during a Customer Number Change in SAMINC.  In this case the Customer Number Change ran fine, no locks or blocks, and this is reflected by no values in Blkby:
    Good luck!