Debugging SQL And The Fun Attached To That

Having come across this problem in Commerce Server 2002 recently:

Server Error in '/' Application.

Source:Microsoft OLE DB Provider for SQL ServerDescription:Incorrect syntax near '('.Source:Microsoft OLE DB Provider for SQL ServerDescription:Invalid object name 'MSCS_CatalogScratch.dbo.Catalog__Query__Results__for_spid__304'.Source:Microsoft OLE DB Provider for SQL ServerDescription:Invalid object name 'MSCS_CatalogScratch.dbo.Catalog__Query__Results__for_spid__304'.

I discovered that the stored procedure failing was called "ctlg_GetResults_for_SingleCatalog" but how to work out where?

This page came in useful: http://support.microsoft.com/kb/316549

Basically, it's stepping through SQL Stored Procedures in Visual Studio. The account you're accessing the DB requires execute permissions, etc, of course, but from there you should be able to step through the process just like code. Well, almost.