Windows Server

Guillaume · Guest

Hi,

I am having a serious problem with the CatalogSearch method in CS2002,
SP1a, FP1.

What happens is that sometimes this will work flawlessly for hours,
then will suddenly start failing with that error: Source:Microsoft OLE
DB Provider for SQL ServerDescription:Invalid column name 'rank'. At
some moments, it will keep failing non-stop for a few dozen calls, at
other moments it will run at 50/50 success rate. It is absolutely
unpredictable.

The code I am using is:

CatalogSearchOptions searchOptions = new CatalogSearchOptions();
searchOptions.ClassTypes = CatalogClassTypes.ProductClass |
CatalogClassTypes.ProductFamilyClass |
CatalogClassTypes.ProductFamilyForVariantsClass |
CatalogClassTypes.ProductVariantClass |
CatalogClassTypes.ProductVariantsForFamilyClass;
searchOptions.SortProperty = "cy_list_price";
searchOptions.SortAscending = true;
searchOptions.RecordsToRetrieve = 100;

(...)

CatalogSearch firstSearch = new
CatalogSearch(_catalog.CatalogContext);
firstSearch.Language = _catalog.ActiveLanguage;
firstSearch.CatalogNames = _catalog.Name;
firstSearch.CategoryName = categories[0].ToString();
firstSearch.SearchOptions = searchOptions;
firstSearch.Recursive = true;

I have tried:
- specifying exactly every ReturnProperties I want
- using "*, rank" as ReturnProperties
- commenting a line in ctlg_GetChildCategories as documented in some
posts (problems with ranking being mishandled by faulty STP)
- using the SqlWhereClause property instead of ClassTypes
- using different sort fields and sort directions
- leaving the RecordsToRetrieve to default or set it at some number of
records
- editing ctlg_GetCategoryResults in order to bypass the code piece
that seems to trigger the error

And of course nothing worked.

So far, the farthest I have traced this problem is in a call to
ctlg_GetCategoryResults I traced using SQL Profiler. When a query
fails, it inevitably passes "0" as value of parameter @P1 as shown
here:

declare @P1 int
set @P1=0
exec dbo.[ctlg_GetCategoryResults] N'Client_Catalog', N'en-CA', 1,
N'Client_Catalog', 29, N'*', N' ', N' ', N'cy_list_price', 1,
2147483646, 1, 62, -1, N' ', N' ', N' ', @P1 output
select @P1

And when the query succeeds, notice the difference in the log:

declare @P1 int
set @P1=2
exec dbo.[ctlg_GetCategoryResults] N'Client_Catalog', N'en-CA', 1,
N'Client_Catalog', 29, N'*', N' ', N' ', N'cy_list_price', 1,
2147483646, 1, 62, -1, N' ', N' ', N' ', @P1 output
select @P1

If any of you folks has any pointers, ideas, suggestions, any help
would be welcomed.

As I said, this is a totally random problem. Same code, same
parameters... have it loop for 20 000 repetitions, no errors. Then run
it 5 times and blam, 3 out of 5 fail... and of course 30 mins later
everything works without ever failing. I'm going crazy. No
identifiable pattern at all.

Thanks in advance, have a good day

Guillaume

David Messner [MSFT] · Guest

We've tried to reproduce this issue and are unable to.

Also, I've been reviewing the code that calls this procedure. It is called
from only one place in the FP1 codebase and the out parameter is always
initialized to zero in that code base. So I cannot explain what would
cause it to be initialized to '2' in any cases as your trace shows. Even
the COM model sets it always to zero or -1, but never 2.

Are you running a Web farm where this could be an issue potentially
specific to one of the servers?

I hate to suggest it, but your best avenue for getting traction on this
issue may be either to come up with a repro case (which may involve your
custom catalog) or engage with Microsoft Product Support.

regards
-djm
--
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use. © 2004 Microsoft Corporation. All rights
reserved.

--------------------
From: guillaume.roy@nurun.com (Guillaume)
Newsgroups: microsoft.public.commerceserver.general
Subject: CatalogSearch failing sporadicaly; STP bug?
Date: 15 Dec 2004 15:00:28 -0800
Organization: http://groups.google.com
Lines: 82
Message-ID: <f3de902c.0412151500.12841d62@posting.google.com>
NNTP-Posting-Host: 216.113.12.98
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
X-Trace: posting.google.com 1103151628 15884 127.0.0.1 (15 Dec 2004
23:00:28 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Wed, 15 Dec 2004 23:00:28 +0000 (UTC)
Path:
cpmsftngxa10.phx.gbl!TK2MSFTNGXA06.phx.gbl!cpmsftngxa06.phx.gbl!TK2MSFTNGP08
.phx.gbl!news-out.cwix.com!newsfeed.cwix.com!newsswing.news.prodigy.com!prod
igy.net!news.glorb.com!postnews.google.com!not-for-mail
Xref: cpmsftngxa10.phx.gbl microsoft.public.commerceserver.general:15249
X-Tomcat-NG: microsoft.public.commerceserver.general

Hi,

I am having a serious problem with the CatalogSearch method in CS2002,
SP1a, FP1.

What happens is that sometimes this will work flawlessly for hours,
then will suddenly start failing with that error: Source:Microsoft OLE
DB Provider for SQL ServerDescription:Invalid column name 'rank'. At
some moments, it will keep failing non-stop for a few dozen calls, at
other moments it will run at 50/50 success rate. It is absolutely
unpredictable.

The code I am using is:

CatalogSearchOptions searchOptions = new CatalogSearchOptions();
searchOptions.ClassTypes = CatalogClassTypes.ProductClass |
CatalogClassTypes.ProductFamilyClass |
CatalogClassTypes.ProductFamilyForVariantsClass |
CatalogClassTypes.ProductVariantClass |
CatalogClassTypes.ProductVariantsForFamilyClass;
searchOptions.SortProperty = "cy_list_price";
searchOptions.SortAscending = true;
searchOptions.RecordsToRetrieve = 100;

(...)

CatalogSearch firstSearch = new
CatalogSearch(_catalog.CatalogContext);
firstSearch.Language = _catalog.ActiveLanguage;
firstSearch.CatalogNames = _catalog.Name;
firstSearch.CategoryName = categories[0].ToString();
firstSearch.SearchOptions = searchOptions;
firstSearch.Recursive = true;

I have tried:
- specifying exactly every ReturnProperties I want
- using "*, rank" as ReturnProperties
- commenting a line in ctlg_GetChildCategories as documented in some
posts (problems with ranking being mishandled by faulty STP)
- using the SqlWhereClause property instead of ClassTypes
- using different sort fields and sort directions
- leaving the RecordsToRetrieve to default or set it at some number of
records
- editing ctlg_GetCategoryResults in order to bypass the code piece
that seems to trigger the error

And of course nothing worked.

So far, the farthest I have traced this problem is in a call to
ctlg_GetCategoryResults I traced using SQL Profiler. When a query
fails, it inevitably passes "0" as value of parameter @P1 as shown
here:

declare @P1 int
set @P1=0
exec dbo.[ctlg_GetCategoryResults] N'Client_Catalog', N'en-CA', 1,
N'Client_Catalog', 29, N'*', N' ', N' ', N'cy_list_price', 1,
2147483646, 1, 62, -1, N' ', N' ', N' ', @P1 output
select @P1

And when the query succeeds, notice the difference in the log:

declare @P1 int
set @P1=2
exec dbo.[ctlg_GetCategoryResults] N'Client_Catalog', N'en-CA', 1,
N'Client_Catalog', 29, N'*', N' ', N' ', N'cy_list_price', 1,
2147483646, 1, 62, -1, N' ', N' ', N' ', @P1 output
select @P1

If any of you folks has any pointers, ideas, suggestions, any help
would be welcomed.

As I said, this is a totally random problem. Same code, same
parameters... have it loop for 20 000 repetitions, no errors. Then run
it 5 times and blam, 3 out of 5 fail... and of course 30 mins later
everything works without ever failing. I'm going crazy. No
identifiable pattern at all.

Thanks in advance, have a good day

Guillaume

Guillaume · Guest

Hi David,

Thanks for taking the time to study this problem.

We recently tried to isolate the problem as much as possible. The
following code also triggers (in an apparently random manner) the
problem:

Dim context As Microsoft.CommerceServer.Runtime.CommerceContext
context = Microsoft.CommerceServer.Runtime.CommerceContext.Current

Dim catalog As ProductCatalog
catalog = context.CatalogSystem.GetCatalog("Client_Catalog")
catalog.ActiveLanguage = "en-CA"

Dim search As CatalogSearch
search = New CatalogSearch

search.CatalogNames = "Client_Catalog"
search.CategoryName = "Client_Category"

Dim dsResults As DataSet
dsResults = search.Search()

This code sample apparently eliminating the possibility of a code error
on our part, we focused on environnement and platform specific factors.
Our main suspicion right now lies in possible conflicts between FP1 and
non-FP1 catalogs sharing the same MSCS_CatalogScratch database on a SQL
Server as our catalog itself is pretty simple and is based on a
previously fully functionnal pre-FP1 catalog.

We should have shortly a deployment of our catalog on a controlled
environnement (stand-alone dedicated PC) on which we hope to be able to
reproduce the bug.

Thanks again for your help, if you come up with any idea or intuition
about this, please let me know, every input is greatly appreciated.

Best regards,

Guillaume

David Messner [MSFT] wrote:

David Messner [MSFT] · Guest

Indeed, the fact that you are sharing MSCS_CatalogScratch DB across an FP1
and non-FP1 install would seem the likely culprit. This setup certainly
has not been tested and I'm pretty sure it would be problematic.

If you must use the same SQL server and you must support both FP1 and
non-FP1 sites, and since we don't allow you to change the name of the
scratch DB, you might consider using SQL named instances.

-djm
--
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use. © 2004 Microsoft Corporation. All rights
reserved.

--------------------
From: "Guillaume" <guillaume.roy@nurun.com>
Newsgroups: microsoft.public.commerceserver.general
Subject: Re: CatalogSearch failing sporadicaly; STP bug?
Date: 20 Dec 2004 13:12:32 -0800
Organization: http://groups.google.com
Lines: 185
Message-ID: <1103577152.558866.271920@z14g2000cwz.googlegroups.com>
References: <f3de902c.0412151500.12841d62@posting.google.com>
<oP88Qss5EHA.2680@cpmsftngxa10.phx.gbl>
NNTP-Posting-Host: 216.113.12.98
Mime-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
X-Trace: posting.google.com 1103577158 19692 127.0.0.1 (20 Dec 2004
21:12:38 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Mon, 20 Dec 2004 21:12:38 +0000 (UTC)
User-Agent: G2/0.2
Complaints-To: groups-abuse@google.com
Injection-Info: z14g2000cwz.googlegroups.com; posting-host=216.113.12.98;
posting-account=gqpY4A0AAAAjZ62ppWuLyvAgZ6rtpo8O
Path:
cpmsftngxa10.phx.gbl!TK2MSFTFEED02.phx.gbl!tornado.fastwebnet.it!tiscali!new
sfeed1.ip.tiscali.net!news.maxwell.syr.edu!postnews.google.com!z14g2000cwz.g
ooglegroups.com!not-for-mail
Xref: cpmsftngxa10.phx.gbl microsoft.public.commerceserver.general:15275
X-Tomcat-NG: microsoft.public.commerceserver.general

Hi David,
Thanks for taking the time to study this problem.
We recently tried to isolate the problem as much as possible. The
following code also triggers (in an apparently random manner) the
problem:
Dim context As Microsoft.CommerceServer.Runtime.CommerceContext
context = Microsoft.CommerceServer.Runtime.CommerceContext.Current
Dim catalog As ProductCatalog
catalog = context.CatalogSystem.GetCatalog("Client_Catalog")
catalog.ActiveLanguage = "en-CA"
Dim search As CatalogSearch
search = New CatalogSearch
search.CatalogNames = "Client_Catalog"
search.CategoryName = "Client_Category"
Dim dsResults As DataSet
dsResults = search.Search()
This code sample apparently eliminating the possibility of a code error
on our part, we focused on environnement and platform specific factors.
Our main suspicion right now lies in possible conflicts between FP1 and
non-FP1 catalogs sharing the same MSCS_CatalogScratch database on a SQL
Server as our catalog itself is pretty simple and is based on a
previously fully functionnal pre-FP1 catalog.
We should have shortly a deployment of our catalog on a controlled
environnement (stand-alone dedicated PC) on which we hope to be able to
reproduce the bug.
Thanks again for your help, if you come up with any idea or intuition
about this, please let me know, every input is greatly appreciated.
Best regards,
Guillaume
David Messner [MSFT] wrote:

	Windows Server Forum Index -> Commerce Server	All times are GMT
Page 1 of 1