sql server - Problem calling stored procedure from another stored procedure via classic ASP -

we have classic asp application works , have been loathe modify code lest invoke wrath of long-dead greek gods.

we had requirement add feature application. feature implementation database operation requires minimal change ui.

i changed ui , made minor modification submit new data value sproc call (sproc1).

in sproc1 called directly asp, added new call sproc happens located on server, sproc2.

somehow, not work via our asp app, works in sql management studio.

here's technical details:

1. sql 2005 on both database servers.
2. sql login authenticating asp application sql 2005 server 1.
3. linked server server 1 server 2 working.
4. when executing sproc1 sql management studio - works fine. when credentialed same user our code uses (the application sql login).
5. sproc2 works when called independently of sproc1 sql management studio.
6. vbscript (asp) captures error emitted in xml client. error number 0, error description blank. both adodb.connection object , whatever err.number/err.description yields in vbscript asp side.

so without errors, nor reproducibility (i.e. through sql mgmt studio) - know issue?

our current plan break down , dig code on asp side , make separate call server 2.sproc2 directly asp rather trying piggy-back through sproc1.

my first reaction might not issue of calling cross-server, 1 of calling second proc first, , this might what's acting differently in 2 different environments.

my first question this: happens if remove cross-server aspect equation? if set test system first proc calls second proc, second proc on same server and/or in same database, still same problem?

along these same lines: in experience, when application , ssms have gotten different results that, has been issue of stored procedures' settings. be, luke says, nocount. i've had sort of thing happen extraneous print statements in code, although seem remember printed value becoming part of error description (very counterintuitively).

if anything returned in messages window when run in ssms, find out coming , make stop. have technical terms, recollection different querying environments have different sensitivities "errors", , default connection via sssm not throw error @ times when ado connection scripting language will.

one final thought: in case environment thing, try different settings on asp page's connection string. e.g., if have oledb connection, try odbc. try native , non-native sql server drivers. check out connection string options provider supports, , try of them seem might worth trying.