Search for text in all SQL Server stored procedures

By Steve Endow

A customer emailed me this morning saying they were getting this error when posting some batches in their test environment:

The stored procedure glpPostBatch returned the following results:  DBMS: 0, Microsoft Dynamics GP: 20507

I opened up the glpPostBatch stored procedure in SQL Server Management studio and searched the script, but there was no error number 20507.

Hmmm.  So my guess was that glpPostBatch was calling other procedures (which might themselves be calling other procedures), and one of those sub-procedures was raising error 20507.  Rather than dig through the stored procedure and try and track down the sub-procedure, I figured I should be able to search all stored procedures for the error number 20507.

Thanks to this Stack Overflow post on this very topic, I used this script:

SELECT DISTINCT o.name AS Object_Name,o.type_desc
FROM sys.sql_modules m 
INNER JOIN sys.objects o 
ON m.object_id=o.object_id
WHERE m.definition Like '%20507%'


In a few seconds, it found that the number was present in a procedure called smAddPostingSecurityRecord.  I then scripted that procedure, and found this code:

select   @sCompanyID = CMPANYID  from  DYNAMICS.dbo.SY01500  (NOLOCK)  where  CMPNYNAM = @I_cCompanyName   
if @@rowcount <> 1  begin  
select @O_iErrorState = 20507  
break  
end  

So it is querying SY01500 for the company record, and if it doesn't get exactly 1 record, it returns that error number.  Makes sense.  But obviously there should always be exactly 1 company record in SY01500, so that doesn't help explain the error.  My only guess is that something is locking or blocking the table, causing the query to fail or return NULL--I'm skeptical that it is returning 0 or 2, but I've asked the client to check the table to confirm the company record is valid.

If that is what is causing the error during batch posting, then my assumption is that there is little I can do to troubleshoot it.  I've been down this road with glpBatchCleanup errors, and have learned that these types of errors are generally best handled by GP support.

Fortunately, the error is only occurring in the customer's test environment and not in their production or UAT environments, so it does appear to be isolated.  I recommended that they try and restore the Dynamics and company databases to see if that helps.

Steve Endow is a Dynamics GP Certified Trainer and Dynamics GP Certified IT Professional in Los Angeles.  He is also the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Google+ and Twitter

http://www.precipioservices.com