Stored Procedures Stopped Working

[nicfoster]

I had tested new versions of the stored procedures we use for searching for products on my development machine. Once tested, I used the Query Analyzer to install the new stored procedures in the production database on our web server. When I went to run a search, the stored procedure returned 0 rows when I knew that a result set would have over 40 rows. I tried all of the others and the same thing. I went into the Query Anaylyzer and exec the stored procedure and got the same results - no rows returned and no errors. All of these stored procs use joins but are not tricky. What is the problem? Thanks for any advice.

Nic Foster

 

[foxdev]

First guess: SET ANSI_NULLS. Try turning them off with SET ANSI_NULLS OFF at the top of one of the stored procedures and see if that affects the results. Robert Bradley

http://www.lowcostprints.com

http://www.foxdev.com/

http://www.agrainofhope.org

 

[nicfoster]

No Luck. I had cut and pasted the stored procedure select into the Query Analyzer and ran it. Same effect - no rows returned and no errors. Here is the query:

IF EXISTS (SELECT * FROM sysobjects
WHERE id = object_id(N'dbo.procSearchKeywordOnly')
AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.procSearchKeywordOnly

GO

SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON

GO

CREATE PROCEDURE procSearchKeywordOnly(

@KeyWord varchar(100) = NULL
)
AS
SET NOCOUNT ON

SELECT p.product_Id,p.name,p.SKU,su.Company,p.ImageName,
d.descr, max(pr.price) as RetailPrice,p.supplier
FROM SelectionList s
INNER JOIN WordList w ON s.GroupWordValue = w.GroupWordValue
INNER JOIN ProductKeywords k ON k.KeywordNbr = w.WordValue
INNER JOIN Products p ON p.Product_Id = k.prod_id
INNER JOIN ProductDescr d ON d.Prod_Id = p.Product_Id
INNER JOIN SupplierDB su ON su.suppid = p.supplier
LEFT JOIN ProductPrices pr ON pr.Prod_Id = p.Product_Id
WHERE (k.keyword like '%' + @Keyword + '%'
OR d.descr like '%' + @Keyword + '%'
OR p.name like '%' + @Keyword + '%')
AND d.SequenceNbr = 1
AND p.Display = 1
GROUP BY p.product_Id,p.[name],p.SKU,su.Company,p.ImageName,d.descr,p.supplier
ORDER BY su.Company, p.Product_Id

GO

This should return 49 rows of product.

 

[foxbox]

has user IUSER_<server> execution rights for the sp's? br
Gerard
(-:

Better a known bug then a new release.


| Do it!
|
V

 

[foxbox]

has user IUSR_<server> execution rights for the sp's? br
Gerard
(-:

Better a known bug then a new release.


| Do it!
|
V

 

[nicfoster]

I have found the problem and it is me. I have not set the display flag to 1 in the production database.

Sorry everyone - I just noticed this when I was making the previous posting. So, I guess I need to thank you for at least getting me to see this.

Thanks.
Nic

 

[Vancouverite]

(1) Get rid of the 'GO's in the procedure

(2) This is the actual code stored in the Stored Procedure? Or is this the script you used to create the SP.

Give us the actual code that shows up if you View the Properties of the SP.