Stumped! - stored procedure call problem

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Stumped! - stored procedure call problem

Author

Topic

smircy
Starting Member

7 Posts

Posted - 2006-11-27 : 08:38:17

I have to admit Im stumped.
ms sql 2000 back end.
access front end.
Access has linked tables to Sql db.
passes through an identical query on its table
to the sql db, sql populates a row with the data
and access displays the report.
when this table is queried in sql it uses a stored
procedure to grab the data and make the calculations
and then insert to the table.
????I cant find anywhere how the hell this stored procedure
is being called. Ive looked through access and the report seems to be built off the query..cant see it telling sql to use the sp, and
Ive combed through SQL , generated scripts with everything checked.....Stumped!!! ????
theres no trigger on the table or any other thing
that I can see that calls it.........hoping someone
can suggest something...

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-27 : 08:40:15

If report is built from query and you can't find associated SP, then how you know that SP is being used?

Harsh Athalye
India.
"Nothing is Impossible"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-27 : 08:41:31

I will suggest you run profiler on the SQL Server and then run the report so that you will come to know which SP is being called.

Harsh Athalye
India.
"Nothing is Impossible"

smircy
Starting Member

7 Posts

Posted - 2006-11-27 : 08:43:48

quote:
Originally posted by harsh_athalye

If report is built from query and you can't find associated SP, then how you know that SP is being used?

Harsh Athalye
India.
"Nothing is Impossible"

Because I can see what the code is doing and manipulate
it (within the sp)and see the changes , there are other reports that do the same thing. I know how to change this system Ive been maintaiing it for a year, but this one little thing is eluding me.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-27 : 08:45:36

Post the query which runs this report so that we can identify the problem.

Harsh Athalye
India.
"Nothing is Impossible"

smircy
Starting Member

7 Posts

Posted - 2006-11-27 : 08:45:41

quote:
Originally posted by harsh_athalye

I will suggest you run profiler on the SQL Server and then run the report so that you will come to know which SP is being called.

Harsh Athalye
India.
"Nothing is Impossible"

I know exactly which sp is it, and I have decomposed
the code as to what its doing and how...I just cant
see where/how its being called.

smircy
Starting Member

7 Posts

Posted - 2006-11-27 : 08:47:01

quote:
Originally posted by harsh_athalye

Post the query which runs this report so that we can identify the problem.

Harsh Athalye
India.
"Nothing is Impossible"

SELECT LRS.StartPeriod, LRS.EndPeriod, LRS.CreateDate, LRS.RawBrinepH_Avg, LRS.RawBrinepH_SD, LRS.RawBrineSG_Avg, LRS.RawBrineSG_SD, LRS.SettFeedpH_Avg, LRS.SettFeedpH_SD, LRS.SettFeedNaOH_Avg, LRS.SettFeedNaOH_SD, LRS.SettFeedNa2CO3_Avg, LRS.SettFeedNa2CO3_SD, LRS.SettOvfHard_Avg, LRS.SettOvfHard_SD, LRS.CleanBrineHard_Avg, LRS.CleanBrineHard_SD, LRS.PriIonExHard_Avg, LRS.PriIonExHard_SD, LRS.PolIonExHard_Avg, LRS.PolIonExHard_SD, LRS.PolIonExpH_Avg, LRS.PolIonExpH_SD, LRS.MixTankHard_Avg, LRS.MixTankHard_SD, LRS.MixTankpH_Avg, LRS.MixTankpH_SD, LRS.FeedBrinepH_Avg, LRS.FeedBrinepH_SD, LRS.FeedBrineSG_Avg, LRS.FeedBrineSG_SD, LRS.HeadAnpH_Avg, LRS.HeadAnpH_SD, LRS.HeadAnSG_Avg, LRS.HeadAnSG_SD, LRS.DecAnpH_Avg, LRS.DecAnpH_SD, LRS.DecAnFCl2_Avg, LRS.DecAnFCl2_SD, LRS.AnNa2SO3_Avg, LRS.AnNa2SO3_SD, LRS.FeedNaOH_Avg, LRS.FeedNaOH_SD, LRS.ProductNaOH_Avg, LRS.ProductNaOH_SD, LRS.Pc50NaOH_Avg, LRS.Pc50NaOH_SD, LRS.Cl2HeadIn_Avg, LRS.Cl2HeadIn_SD, LRS.Cl2HeadH2_Avg, LRS.Cl2HeadH2_SD, LRS.ProductHCl_Avg, LRS.ProductHCl_SD, LRS.SpentH2SO4_Avg, LRS.SpentH2SO4_SD, LRS.Hypo10VT01Cl2_Avg, LRS.Hypo10VT01Cl2_SD, LRS.Hypo10VT01NaOH_Avg, LRS.Hypo10VT01NaOH_SD, LRS.Hypo10VT02Cl2_Avg, LRS.Hypo10VT02Cl2_SD, LRS.Hypo10VT02NaOH_Avg, LRS.Hypo10VT02NaOH_SD, LRS.QC_2_501_Avg, LRS.QC_2_501_SD, LRS.QC_3_502_Avg, LRS.QC_3_502_SD, LRS.QI_3_504_Avg, LRS.QI_3_504_SD, LRS.QC_3_501_Avg, LRS.QC_3_501_SD, LRS.QI_3_503_Avg, LRS.QI_3_503_SD, LRS.QC_5_501_Avg, LRS.QC_5_501_SD, LRS.DI_9_402_Avg, LRS.DI_9_402_SD, LRS.TI_9_205_Avg, LRS.TI_9_205_SD, LRS.QC10_501_Avg, LRS.QC10_501_SD, LRS.TI10_202_Avg, LRS.TI10_202_SD, LRS.QC12_501_Avg, LRS.QC12_501_SD, LRS.QC16_503_Avg, LRS.QC16_503_SD, LRS.QI16_501_Avg, LRS.QI16_501_SD
FROM LabReportSumm AS LRS
WHERE (((LRS.StartPeriod)=getFrom()) AND ((LRS.EndPeriod)=getTo()));

smircy
Starting Member

7 Posts

Posted - 2006-11-27 : 08:48:49

there is nothing special about this query , the sql pass through query is identical to this, at least access displays it so.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-27 : 08:56:35

Try to search for the SP's name in the VBA code window. Search whole project.

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-27 : 08:57:29

In the profiler, do you the SP which you think responsible for report, getting called?

Harsh Athalye
India.
"Nothing is Impossible"

smircy
Starting Member

7 Posts

Posted - 2006-11-27 : 09:04:02

quote:
Originally posted by harsh_athalye

In the profiler, do you the SP which you think responsible for report, getting called?

Harsh Athalye
India.
"Nothing is Impossible"

Yes, i know the sp definitely 100%

smircy
Starting Member

7 Posts

Posted - 2006-11-27 : 09:12:44

quote:
Originally posted by Peso

Try to search for the SP's name in the VBA code window. Search whole project.

Peter Larsson
Helsingborg, Sweden

Thanks Peter, I think the offending code has been identified...for some reason I was convinced it was on the sql side.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources