Stored procedure fails, but QA works
-
Hi guys,
I have a stored procedure which I am expecting to return one row of information....but when I run it at as a stored procedure it returns nothing.....
I have tested the code from QA and it returns my required data.....can someone please explain why this might be???
See code below....
CREATE PROCEDURE GetPersonAddress (@EntityID AS UNIQUEIDENTIFIER, @LanguageID AS smallint)
AS
declare @geoid UNIQUEIDENTIFIER
SELECT @geoid = GeoRollupID_FK FROM tblSiteRollups
LEFT OUTER JOIN tblPersonTrespasses ON tblPersonTrespasses.SiteRollupID_FK= tblSiteRollups.ID_PK
WHERE tblPersonTrespasses.PersonID_FK = @EntityID
if (@GeoID = null)
BEGIN
SELECT @geoid = GeoRollupID_tblSiteRFK from ollups
WHERE ID_PK IN (SELECT ParentSiteRollupID_FK from tblSiteRollups
LEFT OUTER JOIN tblPersonTrespasses ON tblPersonTrespasses.SiteRollupID_FK = tblSiteRollups.ID_PK
WHERE tblPersonTrespasses.PersonID_FK = @EntityID)
if (@GeoID = null)
BEGIN
SELECT @geoid = GeoRollupID_FK FROM tblSiteRollups
WHERE ID_PK IN (SELECT ParentSiteRollupID_FK from tblSiteRollups
LEFT OUTER JOIN tblPersonTrespasses ON tblPersonTrespasses.SiteRollupID_FK = tblSiteRollups.ID_PK
WHERE tblPersonTrespasses.PersonID_FK = @EntityID)
if (@GeoID = null)
BEGIN
SELECT @geoid = GeoRollupID_FK FROM tblSiteRollups
WHERE ID_PK IN (SELECT ParentSiteRollupID_FK from tblSiteRollups
LEFT OUTER JOIN tblPersonTrespasses ON tblPersonTrespasses.SiteRollupID_FK = tblSiteRollups.ID_PK
WHERE tblPersonTrespasses.PersonID_FK = @EntityID)
END
END
END
SELECT tblSiteRollups.Address1_NV,
tblSiteRollups.Address2_NV,
tblSiteRollups.PostalCode_NV,
SiteInfo.Level1,
SiteInfo.Level2,
SiteInfo.Level3
FROM tblSiteRollups
LEFT OUTER JOIN dbo.fcnGeoRollupFlatTable(@LanguageID) SiteInfo
ON SiteInfo.ID_PK = tblSiteRollups.GeoRollupID_FK
WHERE tblSiteRollups.GeoRollupID_FK = @geoid;
GO
-
The one thing I see is
a value does not = null
you have to check if it is null
So if (@GeoID is null)
as to why it works in one place not the other, don't know
-
Don't know if this will help but, I just used it on a "locks script" I just downloaded and put in a stored procedure that runs from the Master database and returns a recordset.
The procedure returns a recordset when run from the query analyzer but it returned nothing except column names in my front end. It made no sense to me.
I call the stored procedure from VB 6, using ADO and . As soon as I added [set nocount on] in my procedure, for each server, it returns a recordset on the front end.
-
Forgive my ignorance but could you explain the above again....and maybe show me hot it might apply?
-
You say it works in QA. Where else are you calling the the stored procedure from? A VB front end? MS Access? Let us know. The code is not intuitive in Access.
-
I am calling it from a VB front end is correct, i am using it in Reporting Services...
-
At risk off getting off-topic for this forum, what is the VB code you're using to get the return values?
-
What return value? There's no return value set in that code so it will always be 0!
Maybe you should start a new thread with a more defined question if you still want help with that!
-
Does Login used in VB to execute the procedure has the procedure execute permissions?
1. While executing the procedure through VB, run sql trace to with ERRORS and BATCH STARTING and BATCH COMPLETED events... All add host name column...
2. Open QA using the same login used VB and execute the procedure in QA.
MohammedU
Microsoft SQL Server MVP -
The original question is, in my words "why does running the stored procedure from the (VB) front end return no rows, while running it from QA returns rows?"
Some ideas:
1. Nocount, 2. "typo", 3. VB code/logic, 4. Permissions issue, 5. data issue
1. NOCOUNT
Matthew suggested to SET NOCOUNT ON (and at the end of the procedure, you might want to return to the default with SET NOCOUNT OFF). The original poster, Neil, asked what that was, so we don't know whether he has tried this yet. Neil, after running a query in QA, you usually get the message
(N row(s) affected)
If you're always running your queries with Results in Grid (see the Query menu), then you would have to click the Messages tab at the bottom to see this. When you SET NOCOUNT ON, this message is not returned.
2. "Typo"
Verify that the stored procedure you're calling is the same one you ran; same server, database and stored procedure name. Verify that the recordset object you're checking in VB is the same one you assigned the stored procedure results to.
3. VB Code/Logic
Are you sure you're sending the input parameters (@EntityID and @LanguageID) with the correct values? Are you creating those parameters in the correct order?
Do you have "On error resume next" in your VB code? If so, it may be glossing over the VB or SQL error message you need to see.
4. Permissions
When you run VB, are you logged in as the same user you are when you run QA? If not, are these users in the same user group?
5. Data issues
Some sites have multiple environments (e.g. Production, Development, Test). Are you using the same one from QA and VB?
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply