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:
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/
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/
Comments
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!
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.-
our company wanted to delete customers/vendors/items in GP however we
realized that we cannot delete them as there is history against these
records.
We decided to create a temp/dummy record for each (i.e. dummy
customer, vendor, item) and combine those records per module we wanted
to initially deleted into these dummy records using the combiner
tools. Has anyone done this before and is there any issues with doing
it 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
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
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
Well said. Surely running it first on test is always best.
Thank you for your suggestion.
--Jenny