我们有一个可以简单运行的经典 ASP 应用程序，但我们一直不愿意修改代码，以免激怒某些早已死去的希腊诸神。
我们最近需要向应用程序添加一项功能。 该功能的实现实际上只是一个数据库操作，只需对 UI 进行最小程度的更改。
我更改了 UI 并进行了较小的修改，以向 sproc 调用 (sproc1) 提交新的数据值。
在直接从 ASP 调用的 sproc1 中，我们添加了对恰好位于另一台服务器 sproc2 上的另一个存储过程的新调用。
不知何故，这不能通过我们的 ASP 应用程序工作，但可以在 SQL Management Studio 中工作。
以下是技术细节：
- 两台数据库服务器上的 SQL 2005。
- Sql Login 正在从 ASP 应用程序向 SQL 2005 Server 1 进行身份验证。
- 从服务器 1 到服务器 2 的链接服务器正在工作。
- 从 SQL Management Studio 执行 sproc1 时 - 工作正常。 即使我们的代码使用的用户身份相同（应用程序 sql 登录）。
- 当从 SQL Management Studio 独立于 sproc1 调用时，sproc2 可以工作。
- VBScript (ASP) 捕获错误并以 XML 形式发送回客户端。 错误编号为 0，错误描述为空。 既来自 ADODB.Connection 对象，也来自 ASP 端 VBScript 中生成的 Err.Number/Err.Description。
因此，没有任何错误，也没有任何可重复性（即通过 SQL Mgmt Studio） - 有人知道这个问题吗？
我们当前的计划是分解并深入研究 ASP 端的代码，并直接从 ASP 对服务器 2.sproc2 进行完全独立的调用，而不是尝试通过 sproc1 进行搭载。
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:
- SQL 2005 on both database servers.
- Sql Login is authenticating from the ASP application to SQL 2005 Server 1.
- Linked server from Server 1 to Server 2 is working.
- When executing sproc1 from SQL Management Studio - works fine. Even when credentialed as the same user our code uses (the application sql login).
- sproc2 works when called independently of sproc1 from SQL Management Studio.
- 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.
发布评论
评论（9）
您是否在两个存储过程中都设置了 set nocount on ？ 我曾经遇到过类似的问题，虽然我现在不记得我是如何解决它的，但我知道这与它有关！
Have you got set nocount on set in both stored procedures? I had a similar issue once and whilst I can't remember exactly how I solved it at the moment, I know that had something to do with it!
您可能会遭受 double-跃点问题
双跃点问题是指当 ASP/X 页面尝试使用位于与 IIS 服务器不同的服务器上的资源时。
Windows NT 质询/响应不支持双跳模拟（一旦传递到 IIS服务器，相同的凭据无法传递到后端服务器进行身份验证）。
您应该使用 SQL Profiler 验证尝试的第二个连接。
请注意，通过手动测试，您不会通过 IIS 进行身份验证。 只有当您通过 ASP/X 页面启动 sql 时，才会出现此问题。
更多资源：
You could be suffering from the double-hop problem
The double-hop issue is when the ASP/X page tries to use resources that are located on a server that is different from the IIS server.
Windows NT Challenge/Response does not support double-hop impersonations (in that once passed to the IIS server, the same credentials cannot be passed to a back-end server for authentication).
You should verify the attempted second connection using SQL Profiler.
Note that with your manual testing you are not authenticating via IIS. It's only when you initiate the sql via the ASP/X page that this problem manifests.
More resources:
我有一个类似的问题，我通过设置 nocount 并删除打印命令解决了它。
I had a similar problem and I solved it by setting nocount on and removing print commands.
我的第一反应是，这可能不是调用跨服务器的问题，而是从第一个过程调用第二个过程的问题，并且这可能是在两个不同环境中表现不同的原因。
我的第一个问题是：如果从等式中删除跨服务器方面会发生什么？ 如果您可以设置一个测试系统，其中第一个过程调用第二个过程，但第二个过程位于同一服务器和/或同一数据库中，您还会遇到同样的问题吗？
沿着同样的思路：根据我的经验，当应用程序和 SSMS 得到这样的不同结果时，通常是存储过程设置的问题。 正如卢克所说，可能没有。 我在代码中的无关 PRINT 语句中发生过这种情况，尽管我似乎记得 PRINTed 值成为错误描述的一部分（非常违反直觉）。
当您在 SSMS 中运行此命令时，如果“消息”窗口中返回任何内容，请找出它来自何处并使其停止。 我必须查阅技术术语，但我记得不同的查询环境对“错误”的敏感度不同，并且当来自脚本语言的 ADO 连接会在某些时候通过 SSSM 默认连接不会抛出错误。 。
最后一个想法：如果是环境问题，请在 ASP 页面的连接字符串上尝试不同的设置。 例如，如果您有 OLEDB 连接，请尝试 ODBC。 尝试本机和非本机 SQL Server 驱动程序。 查看您的提供商支持哪些连接字符串选项，并尝试其中任何看起来值得尝试的选项。
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.
示例代码可能会有所帮助:) 您是否尝试从存储过程返回两个表？ 我不认为 ADO 2.6 可以处理返回的多个表。
Example code might help :) Are you trying to return two tables from the stored procedure; I don't think ADO 2.6 can handle multiple tables being returned.
我确实考虑过这一点（双跳），但是像我所指的 sproc-in-a-sproc 调用与通过 INNER JOIN 的典型跨服务器连接之间有什么区别？ 两者都将在 Server1 上执行，使用链接服务器凭据并向服务器 2 进行身份验证。
任何人都可以确认调用 sproc 跨服务器与在数据表上执行联接不同吗？ 为什么？
如果链接服务器配置是一个 sql 帐户 - 是否被视为双跳（因为您指的是 NTLM 双跳？）
就是否返回多个结果集而言 - 否。 在 .net 世界中，Server1.Sproc1 和 Server2.Sproc2 都将是“ExecuteNonQuery()”，并且不返回任何内容（没有结果集，也没有返回值）。
I did consider that (double-hop), but what is the difference between a sproc-in-a-sproc call like I am referring to vs. a typical cross-server join via INNER JOIN? Both would be executed on Server1, using the Linked Server credentials, and authenticating to Server 2.
Can anyone confirm that calling a sproc cross-server is different than doing a join on data tables? And why?
If the Linked Server config is a sql account - is that considered a double-hop (since what you refer to is NTLM double-hops?)
In terms of whether multiple resultsets are coming back - no. Both Server1.Sproc1 and Server2.Sproc2 would be "ExecuteNonQuery()" in the .net world and return nothing (no resultsets and no return values).
尝试检查连接字符串中指定的用户对数据库的权限。
使用 sql mgmt studio 时，在连接字符串中使用相同的用户名登录数据库。
创建一些临时表来写入中间值和异常，因为它可以是调试应用程序的有效方法。
Try to check the permissions to the database for the user specified in the connection string.
Use the same user name in the connection string to log in to the database while using sql mgmt studio.
create some temporary table to write the intermediate values and exceptions since it can be a effective way of debugging your application.
我可以检查一下：您添加了 sproc2 吗？ 在此之前，它已经工作了很长时间。
你不能改变你调用sproc2的地方吗？ 您可以从 ASP 中调用它，而不是从 sproc1 内部调用它吗？ 这样您就可以在代码中控制对 SQL 的身份验证，而不必依赖在服务器上设置任何信任或共享远程身份验证。
Can I just check: You made the addition of sproc2? Prior to that it was working fine for ages.
Could you not change where you call sproc2 from? Rather than calling it from inside sproc1, can you call it from the ASP? That way you control the authentication to SQL in the code, and don't have to rely on setting up any trusts or shared remote authentication on the servers.
您的链接服务器是如何设置的？ 通常，您可以选择如何向远程服务器进行身份验证，其中包括以当前登录用户身份登录或指定始终使用的 SQL 登录名。 您是否尝试过将其设置为始终使用特定帐户？ 这应该消除调用远程过程时任何可能的权限问题......
How is your linked server set up? You generally have some options as to how it authenticates to the remote server, which include logging in as the currently logged in user or specifying a SQL login to always use. Have you tried setting it to always use a specific account? That should eliminate any possible permissions issues in calling the remote procedure...