How to search for a text within a SQL Server trigger, stored procedure, or UDF

Just recently I had been helping my friend Cal at EMC to resolve an issue with the Professional Services Tools Library (PSTL) Item Combiner utility. In a previous life, his company had installed the Manufacturing series and decided not to use it. They upgraded to Dynamics GP 10.0 and decided they wanted to take advantage of PSTL’s Item Combiner to do some cleanup work on their product master.

Not so quick! The Item Combiner failed with the message:

‘[Microsoft][SQL Native Client][SQL Server]Invalid object name ‘MOP1014’

We began an extensive troubleshooting process to find the culprit of the problem with no success, that is, until the problem was narrowed down to the following question: How to find a text within a SQL Server trigger, stored procedure, or UDF module.

The following T-SQL query accomplishes just that:


-- run against DYNAMICS and company databases

SELECT OBJECT_NAME([object_id])
FROM sys.sql_modules
WHERE [definition] LIKE '%MOP1024%';

It turns out that SQL Server does have this information available when the trigger, stored procedure, or UDF object is not created with encryption.

My thanks go out to SQL Server MVP Aaron Bertrand for his contribution to this post.

RELATED ARTICLES:

– Finding Columns in Tables – Click here

Until next post!

MG.-
Mariano Gomez, MVP, MCP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Advertisements

6 Responses to How to search for a text within a SQL Server trigger, stored procedure, or UDF

  1. Anonymous says:

    >hi there,do u happen to have any information what are the restrictions/validations needed to be met when combining items using the item combiner tool? it does not seem to be documented anywhere. so far i know you cannot combine items with different item types, quantity decimal places, unit of measure or if one item is tracking serial/lot and the other is not.would appreciate any info.Thanks!

  2. >Hi,Unfortunately, I don't have a list, but you may want to open a support case to get this list of things.However, it would sound from what you have found so far, that anything setting that is in contraposition with another setting would prevent the item combiner from doing its job.MG.-

  3. Anonymous says:

    >hello, i came upon this thread and have a similar question. i hope u can assist me.our company wanted to delete customers/vendors/items in GP however werealized that we cannot delete them as there is history against theserecords.We decided to create a temp/dummy record for each (i.e. dummycustomer, vendor, item) and combine those records per module we wantedto initially deleted into these dummy records using the combinertools. Has anyone done this before and is there any issues with doingit this way?as with Item combiner, yes i found also that some conditions are required before items can be combined. Any ideas if this whole approach is fine?–Jenny

  4. >Jenny,I see no issues with what you are doing, however, and like with all suggestions, make sure you test in a test environment first, evaluate the results, then run against your production environment.MG.-Mariano Gomez, MVP

  5. >Jenny,I see no issues with what you are doing, however, and like with all suggestions, make sure you test in a test environment first, evaluate the results, then run against your production environment.MG.-Mariano Gomez, MVP

  6. Anonymous says:

    >Thanks Mariano!Well said. Surely running it first on test is always best.Thank you for your suggestion.–Jenny

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: