crystal report 9 won't run stored procedure within stored procedure

I want to capture how many times a stored procedure is run so I can easily get rid of obsolete code.  

1.  Crystal report uses SP a_cstPOPriceVsStdCost.  
2.  a_cstPOPriceVsStdCost executes a_cstZRecordSPUsed to capture usage info.  

Problem is, I get zero records when I run my report.  If I run a_cstPOPriceVsStdCost outside of the report, it captures the info perfectly.  

================ Crystal runs this SP

ALTER PROCEDURE a_cstPOPriceVsStdCost AS

--****** gather usage info
declare @GetSPName varchar(255)
declare @GetUserName varchar(25)

SET @GetSPName=object_name(@@procid)
set @GetUserName=current_user

execute a_cstZRecordSPUsed @GetSPName, @GetUserName
--****** end gather usage info

SELECT pol.OrdResc, br.Description RescDesc
FROM poPurchaseOrder po
WHERE DelStat=8

===============================
alter procedure a_cstZRecordSPUsed
       @SP varchar(255), @UserName varchar(15) as

DECLARE @Count int

SET @Count = (SELECT COUNT(SP)
              FROM cstZ_SPUsage
              WHERE UserName=@UserName
                    and SP=@SP)

IF @Count<=0 or @Count is null
  INSERT cstZ_SPUsage (SP, UserName, DateUsed, UseCount)
  SELECT @SP, @UserName, getdate(),1
ELSE
  UPDATE cstZ_SPUsage
  SET DateUsed = getdate(), UseCount=UseCount+1
  WHERE SP=@SP and UserName = @UserName
gebiglerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mlmccCommented:
How are you running the report?
Is it through an application?

I know there are problems using SPs with Crystal in that Crystal uses the first SELECT statement it finds.

I think the problem is the SP is being run by a system user not the logged in user so it may not get the correct  CUrrentUser back to run the report.

mlmcc
0
vastoCommented:
add SET NOCOUNT ON as it is shown bellow:


ALTER PROCEDURE a_cstPOPriceVsStdCost AS

--****** gather usage info
declare @GetSPName varchar(255)
declare @GetUserName varchar(25)

SET NOCOUNT ON

SET @GetSPName=object_name(@@procid)
.....
0
gebiglerAuthor Commented:
It looks like the issue may have been a field size.  I changed the user field size and it works.  

I've had stored procedures run correctly via query analyzer where crystal reports was stricter.  Thanks!
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gebiglerAuthor Commented:
I was able to fix the issue by changing field size.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.