x Want to participate? Join the group to interact
  • State Not Answered
  • Replies 5 replies
  • Subscribers 114 subscribers
  • Views 717 views
  • Users 0 members are here
Browse Forums
Announcements
297
topics
270
replies
Databases and Operating Systems
753
topics
3k
replies
Financials Suite
1k
topics
3k
replies
General Discussion
2k
topics
10k
replies
Operational Suite
761
topics
2k
replies
Reporting and Analytics
1k
topics
4k
replies
Reports, Macros, and Customizations
801
topics
3k
replies
Sage CRM for Sage 300
190
topics
422
replies
ValerieH

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

Posted By

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?

  • 0

    Hi ValerieH, 

    Can you confirm what version and Product Update you are running?  is EW activated in System Information, ie.. is there a red checkmark next to EW in the System Info screen?

    Regards,

    DCrampton

  • 0 in reply to DCrampton

    I am running V2023 PU1.      I do not see a red checkmark by EW in system info screen but I do see a red checkmark beside SG70A (account code change) SP70A (vendor number change) SR70A (customer number change)

  • 0

    Hi  this is just an idea, but have you ensured CRM/Sage 300 integration is disabled before running the Customer Code Change?  Maybe CRM is trying to sync up the Customers again as the change is occurring?

  • 0 in reply to Accsys Consulting AU

    how would I confirm that?  CRM has never been installed to my knowledge

  • 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----------------------------------------------------------------------------------->

    DECLARE @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
     
     
        --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
     
     
        SET NOCOUNT ON
        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
        SET NOCOUNT OFF
        SELECT  *
        FROM    @Table
        WHERE
        (@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)
        AND (@REQUESTID IS NULL OR REQUESTID = @REQUESTID)
    --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!

*Community Hub is the new name for Sage City