Microsoft KB Archive/814072

= Installing SQL Server 2000 SP3 with Commerce Server 2002 =

PSS ID Number: 814072

Article Last Modified on 2/3/2003

-

The information in this article applies to:


 * Microsoft Commerce Server 2002 SP1

-



SYMPTOMS
After installing SQL Server 2000 Service Pack 3 (SP3), you may receive results from only one catalog although you have queried on multiple Microsoft Commerce Server catalogs.

In addition, you may also receive an error like:

Commerce.CatalogManager (0x80040E14) Source:Microsoft OLE DB Provider for SQL ServerDescription:Invalid column name '#CatalogRowNum#'.Source:Microsoft OLE DB Provider for SQL ServerDescription:Invalid column name '#CatalogRowNum#'. /retail/include/catalog.asp, line 287



CAUSE
This is due to cross-database ownership chaining in SQL Server 2000 SP3 being disabled.

When a solution site is unpackaged, both the Catalog database and the MSCS_CatalogScratch database are created. Some catalog stored procedures create, insert, and manipulate &quot;temp/scratch&quot; tables in the MSCS_CatalgoScratch database. Typically, the database user in the CatalogDB is mapped to a similar database user in the MSCS_CatalgoScratch DB. This is called cross-database ownership chaining.

The SQL Server 2000 SP3 (default installation option) disables cross-database ownership chaining and so some of the catalog stored procedures that manipulate scratch tables in the MSCS_CatalogScratch database do not work.



To resolve this problem, do one of the following:
 * Enable cross-database ownership chaining when prompted during the install process of SQL Server 2000 SP3.
 * If you already installed SP3 with the default selection for cross-database ownership chaining (disabled), you will need to enable this setting in SQL Server Enterprise Manager on the following databases:
 * Catalog database
 * MSCS_CatalogScratch database

