Finidng which stored proc causes the error ?

Question

Finidng which stored proc causes the error ?

mw_sql_developer

SSCoach

Points: 19445
More actions
January 27, 2014 at 8:11 am

#303498

We have a commercial app that fires several stored procs at the begining. Now sure which one of them is throwing an error ( Says something like column ferral_orr not found ).
I know for sure it is coming from some code in a stored proc. Is there a guru out there who can write a SQl statemet that can parse the DB for all stored proc definitions and find out which stored proc is responsible for the alert ?
So to make a long story short, can I search the database for stored proc definitions and search for a key word "referall_orr"

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

mw112009 (1/27/2014)
We have a commercial app that fires several stored procs at the begining. Now sure which one of them is throwing an error ( Says something like column ferral_orr not found ).
I know for sure it is coming from some code in a stored proc. Is there a guru out there who can write a SQl statemet that can parse the DB for all stored proc definitions and find out which stored proc is responsible for the alert ?
So to make a long story short, can I search the database for stored proc definitions and search for a key word "referall_orr"

pretty straight forward to find, there's a built in meta data view to use!:

select object_name(object_id), definition from sys.sql_modules WHERE definition like '%referall_orr%'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

mw_sql_developer SSCoach Points: 19445 More actions · Answer 2

Works well but here is a related question. How can we search specifically for the the word "referrals_orr"

I know that SQL server thinks of "_" as any common character. I know for sure there is a way to search for

special characters. Can it be the '\' character infront of the _ ?

The following will returns entries like referralsaorr ( I don't want that )

SELECT

obj_name

FROM

#r

WHERE

PATINDEX('%'+ 'referrals_orr' +'%', sp_def) > 0;

Ed Wagner SSC Guru Points: 287026 More actions · Answer 3

Quote the special character. Example:

create table #test (

string varchar(100));

insert into #test(string) values('001');

insert into #test(string) values('101');

insert into #test(string) values('1_1');

insert into #test(string) values('0_1');

select * from #test where string like '1[_]%';

select * from #test where string like '1%';

drop table #test;

HTH

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Lowell SSC Guru Points: 323518 More actions · Answer 4

and another way:

you can declare an explicit escape char like this:

select

object_name(object_id),

definition

from sys.sql_modules

WHERE definition like '%referall!_orr%' ESCAPE '!'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Ed Wagner SSC Guru Points: 287026 More actions · Answer 5

Lowell (1/27/2014)
and another way:
you can declare an explicit escape char like this:
select
object_name(object_id),
definition
from sys.sql_modules
WHERE definition like '%referall!_orr%' ESCAPE '!'

I like this way better - easier to read.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions