The @@ROWCOUNT is a sql function that is contained in the stored
procedure, it is not within Witango, the <@LITERAL> tag is not the
solution, but thanks for the effort!
The issue is that the @@ROWCOUNT returns a value of 0 when the stored
procedure is called from Witango and it returns the correct value when
called from SQL Query Analyzer.
I ran a trace to see what was happening and i see statements coming out of
Witango setting the value of ROWCOUNT to 0. I am assuming that there is
some additional "plumbing" happening behind the scenes to facilitate the
request although it doesn't really make any sense to me considering the
fact that the query result in SQL is the right value. Witango people can
you shed some light on this?
I came up with a workaround that seems to have almost no impact on the
query from a cpu, memory and speed standpoint, so i am ok with it. The
workaround is detailed below.
I would sure love it if i didn't need the workaround.
p.
**********************************************************
Within Witango i just have a direct dbms action with the following command:
usp_WS_GetProductsByGID <@VAR method$gid>
**********************************************************
This executes the following stored procedure:
**********************************************************
CREATE PROCEDURE usp_WS_GetProductsByGID
@gid int,
AS
SELECT DISTINCT Product.Product_Id, Product.Product_Name,
Product.Product_Image_sm, Product.Product_Price,
Product.Product_Sale_Price,
Product.Product_Sale_Price_Flag,Product.Product_Featured_Flag,
Product.Product_Promotion_Flag, Product.SortOrder,
Product.Product_SKU, @@ROWCOUNT as matchingRecords
FROM Product INNER JOIN
ProductGroup ON Product.Product_Id =
ProductGroup.ProductId
WHERE (Product.Product_Status_Flag = 1) AND (Product.Product_Dead_Flag
= 0) AND (ProductGroup.GroupId = @gid)
ORDER BY Product.Product_Featured_Flag DESC,
Product.Product_Promotion_Flag DESC, Product.SortOrder DESC
GO
**********************************************************
This is my workaround to solve the problem:
**********************************************************
CREATE PROCEDURE usp_WS_GetProductsByGID
@gid int,
AS
DECLARE @matches int
SET @matches =
(SELECT DISTINCT COUNT(Product.Product_Id) AS matches
FROM Product INNER JOIN
ProductGroup ON Product.Product_Id =
ProductGroup.ProductId
WHERE (Product.Product_Status_Flag = 1) AND (Product.Product_Dead_Flag
= 0) AND (ProductGroup.GroupId = @gid)
SELECT DISTINCT Product.Product_Id, Product.Product_Name,
Product.Product_Image_sm, Product.Product_Price,
Product.Product_Sale_Price,
Product.Product_Sale_Price_Flag,Product.Product_Featured_Flag,
Product.Product_Promotion_Flag, Product.SortOrder,
Product.Product_SKU, @matches as matchingRecords
FROM Product INNER JOIN
ProductGroup ON Product.Product_Id =
ProductGroup.ProductId
WHERE (Product.Product_Status_Flag = 1) AND (Product.Product_Dead_Flag
= 0) AND (ProductGroup.GroupId = @gid)
ORDER BY Product.Product_Featured_Flag DESC,
Product.Product_Promotion_Flag DESC, Product.SortOrder DESC
GO
**********************************************************
p.
> Ok.. Forget it :P
>
> Use <@literal> around the rowcount statement
>
> -----Original Message-----
> From: Jon Pevzner [mailto:[EMAIL PROTECTED]
> Sent: Friday, May 05, 2006 1:13 PM
> To: [email protected]
> Subject: RE: Witango-Talk: strange query issue
>
> That was weird... My reply got trashed somewhere along the path to
> Australia... It
> should read:
>
> It looks like Witango is interpreting @@ROWCOUNT as an empty variable (as
> in <@var
> rowcount>). I've never tryed this in a sproc, but try using <@literal
> rowcount>'@@rowcount'>
> instead so Witango doesn't process it and SQL Server will.
>
> -----Original Message-----
> From: Jon Pevzner [mailto:[EMAIL PROTECTED]
> Sent: Friday, May 05, 2006 1:09 PM
> To: [email protected]
> Subject: RE: Witango-Talk: strange query issue
>
> Peter,
>
> It looks like Witango is interpreting @@ROWCOUNT as an empty variable (as
> in <@var
> rowcount>). I've never tryed this in a sproc, but try using <@literal
> rowcount>'@@rowcount'>
> instead so Witango doesn't process it and SQL Server will.
>
> Jon
>
> -----Original Message-----
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> Sent: Friday, May 05, 2006 10:16 AM
> To: [email protected]
> Subject: Witango-Talk: strange query issue
>
> Hey,
>
> I am executing a query against a MSSQL2000 database. I am requesting the
> @@ROWCOUNT
> value in one of the columns.
>
> When i call the stored procedure from within sql query analyzer it gives
> me the
> correct value. When i call the stored procedure from Witango this value is
> coming
> back with a 0.
>
> Anyone seen anything like this?
>
> p.
>
> ________________________________________________________________________
> TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
>
> ________________________________________________________________________
> TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
>
> ________________________________________________________________________
> TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
>
> ________________________________________________________________________
> TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
>
________________________________________________________________________
TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
