I've noticed that Witango issues "SET ROWCOUNT 0" before it does it's "hearbeat" query that checks that the database is there. We've seen that it then does "SET ROWCOUNT 1" before actually issuing the query. I think we've also noticed that the value changes based on the "number of rows to retrieve" setting. Maybe if you're doing a DBMS to execute a stored procedure, it's not setting ROWCOUNT back to 1 when it should?
----- Original Message ----- From: <[EMAIL PROTECTED]> To: <[email protected]> Sent: Sunday, May 07, 2006 9:57 AM Subject: RE: Witango-Talk: strange query issue > 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 > ________________________________________________________________________ TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
