Problem calling stored procedure from another stored procedure via classic ASP

Question

We have a classic ASP application that simply works and we have been loathe to modify the code lest we invoke the wrath of some long-dead Greek gods.

We recently had the requirement to add a feature to an application. The feature implementation is really just a database operation requires minimal change to the UI.

I changed the UI and made the minor modification to submit a new data value to the sproc call (sproc1).

In sproc1 that is called directly from ASP, we added a new call to another sproc that happens to be located on another server, sproc2.

Somehow, this does not work via our ASP app, but works in SQL Management Studio.

Here's the technical details:

1. SQL 2005 on both database servers.
2. Sql Login is authenticating from the ASP application to SQL 2005 Server 1.
3. Linked server from Server 1 to Server 2 is working.
4. When executing sproc1 from SQL Management Studio - works fine. Even when credentialed as the same user our code uses (the application sql login).
5. sproc2 works when called independently of sproc1 from SQL Management Studio.
6. VBScript (ASP) captures an error which is emitted in the XML back to the client. Error number is 0, error description is blank. Both from the ADODB.Connection object and from whatever Err.Number/Err.Description yields in VBScript from the ASP side.

So without any errors, nor any reproducibility (i.e. through SQL Mgmt Studio) - does anyone know the issue?

Our current plan is to break down and dig into the code on the ASP side and make a completely separate call to Server 2.sproc2 directly from ASP rather than trying to piggy-back through sproc1.

Answer 1

My first reaction is that this might not be an issue of calling cross-server, but one of calling a second proc from a first, and that this might be what's acting differently in the two different environments.

My first question is this: what happens if you remove the cross-server aspect from the equation? If you could set up a test system where your first proc calls your second proc, but the second proc is on the same server and/or in the same database, do you still get the same problem?

Along these same lines: In my experience, when the application and SSMS have gotten different results like that, it has often been an issue of the stored procedures' settings. It could be, as Luke says, NOCOUNT. I've had this sort of thing happen from extraneous PRINT statements in the code, although I seem to remember the PRINTed value becoming part of the error description (very counterintuitively).

If anything is returned in the Messages window when you run this in SSMS, find out where it is coming from and make it stop. I would have to look up the technical terms, but my recollection is that different querying environments have different sensitivities to "errors", and that a default connection via SSSM will not throw an error at certain times when an ADO connection from a scripting language will.

One final thought: in case it is an environment thing, try different settings on your ASP page's connection string. E.g., if you have an OLEDB connection, try ODBC. Try the native and non-native SQL Server drivers. Check out what connection string options your provider supports, and try any of them that seem like they might be worth trying.