September 4, 2003 at 5:36 pm
Hi,
I have been using a stored procedure which ran nicely on two different sql setups but now that I've installed it on a third machine it is no longer working. The results returned via the sp are:
NULLNULLNULLNULL00
If I run the code in the query analyser window it works as it should do.
The stored procedure in question is:
CREATE PROCEDURE [dbo].[usp_GetProjectSummary]
@ProjectID int
AS
DECLARE @LastTaskDate datetime
DECLARE @LastTaskUserName varchar(100)
DECLARE @LastBugDate datetime
DECLARE @LastBugUserName varchar(100)
DECLARE @OpenTaskCount int
DECLARE @OpenBugCount int
SET @OpenTaskCount = (SELECT Count(EnhancementID) FROM tbl_Enhancement WHERE ProjectID = @ProjectID AND StatusID < 4)
SET @OpenBugCount =( SELECT Count(BugID) FROM tbl_Bug WHERE ProjectID = @ProjectID AND StatusID < 4)
SET @LastTaskDate = ( SELECT Max(LastAmmendedDate)
FROM tbl_Enhancement e
WHERE ProjectID = @ProjectID)
SET @LastBugDate = ( SELECT Max(LastAmmendedDate)
FROM tbl_Bug e
WHERE ProjectID = @ProjectID)
SET @LastBugUserName = (SELECT FirstName + ' ' + LastName FROM tbl_BugmanUser WHERE userID IN (SELECT LastAmmendedBy
FROM tbl_Bug
WHERE ProjectID = @ProjectID AND LastAmmendedDate = @LastBugDate))
SET @LastTaskUserName = (SELECT FirstName + ' ' + LastName FROM tbl_BugmanUser WHERE userID IN (SELECT LastAmmendedBy
FROM tbl_Enhancement
WHERE ProjectID = @ProjectID AND LastAmmendedDate = @LastTaskDate))
SELECT LastTaskDate = @LastTaskDate,
LastTaskUserName = @LastTaskUserName,
LastBugDate=@LastBugDate,
LastBugUserName = @LastBugUserName,
TotalTasks = @OpenTaskCount,
TotalBugs = @OpenBugCount
Does anyone have an idea as to what the problem might be?
Thanks in advance!
September 4, 2003 at 6:03 pm
Try the following tests to see what happens.
I run the code with all the variables and set them to static values to see if they return.
Also, try removing all the variables and run all the SELECTS to see if each returns a value.
Also are did you mean you tested the SP in QA and the code itself in QA and only the code works right? Or do you mean the code works right in QA and the SP fails in an app? If the later could be on the third machine may have the permissions setup incorrectly, I have seen folks write VB apps and test fine everywhere except one location, then it turns out the permissions error was being missed because of error trapping code.
September 4, 2003 at 6:18 pm
I agree with antares. I think it could be permissions too. because you say that it works fine in QA but not in the SP (which sounds like it is run from an app or something)
The only thing that bugs me (no pun intended) is that you got results of NULL and 0 , I would have thought that maybe you would have got an actual "user denied on database "blah" " type error if you didnt have permissions set properly... Let us know how you go !
------------------------------
Life is far too important to be taken seriously
September 4, 2003 at 6:40 pm
Thank you for your help. After working through the suggestions you gave I concluded that the stored procedure wasn't seeing any data in the table! When I looked at the table listing there were two versions of the table, a dbo and one belonging to the user, which would explain why QA (which was running as user) looked into one version of the table and the stored procedure which was dbo got the data from the other, emtpy one.
The reason for the duplication in the first place is probably to do with making two different attempts at copying the objects via import. Thanks again.
September 5, 2003 at 3:50 am
Classic case for using at least the two part name and why all objects should be owned by DBO.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply