从RPC运行时，SQL Server存储过程没有语句运行

I have a SQL Server 2000 database with a stored procedure that deletes a row from a specific table, given its id. When I call the stored procedure from VB.NET, it does not delete the row, but running the same script directly on the database via SSMS, it works.

我有一个带有存储过程的SQL Server 2000数据库,该存储过程在给定其id的情况下从特定表中删除一行。当我从VB.NET调用存储过程时,它不会删除该行,而是通过SSMS直接在数据库上运行相同的脚本,它可以工作。

Here's my chain of events:

这是我的一系列事件:

Start SQL Server Profiler to watch all calls to the database. I have it setup to track when stored procedure starts, completes, and even on SQL statements start/complete within that stored procedure.

启动SQL Server Profiler以查看对数据库的所有调用。我有它设置跟踪存储过程何时启动,完成,甚至在该存储过程中的SQL语句启动/完成。

Call stored procedure via VB.NET dll.

通过VB.NET dll调用存储过程。

Stop the profiler trace to avoid excessive data to dig through.

停止探查器跟踪以避免过多的数据挖掘。

Select from table, and see that the row still exists.

从表中选择,看到该行仍然存在。

View the profiler trace, which only shows RPC:Starting, SP:Starting, RPC:Completed. No inner statements are traced, which verifies why the row wasn't deleted since the delete statement never fired.

查看探查器跟踪,该跟踪仅显示RPC:Starting,SP:Starting,RPC:Completed。没有跟踪内部语句,这会验证为什么行没有被删除,因为delete语句从未被触发。

Copy/paste the EXEC call directly from the RPC:Starting trace entry from when it was called via VB.NET, into SQL Server Management Studio query window pointed to the same database with same credentials.

直接从RPC复制/粘贴EXEC调用:从通过VB.NET调用时开始跟踪条目,进入指向具有相同凭据的同一数据库的SQL Server Management Studio查询窗口。

Start profiler again.

再次启动探查器。

Execute EXEC statement from bullet 6 in SSMS.

在SSMS中执行子弹6中的EXEC语句。

Stop profiler.
Select from table, and see that the row GOT DELETED like it should.

从表中选择,并看到GOT DELETED行应该如此。

View the profiler trace, which shows SP:Starting, all statements starting/completed including the DELETE statement, and SP:Completed.

查看分析器跟踪,其中显示SP:Starting,所有语句的开始/完成(包括DELETE语句)和SP:已完成。

Why would running it via RPC make it not execute any of the statements in the proc, but running directly acts as it should?

为什么通过RPC运行它会使它不执行proc中的任何语句,但是直接运行应该是应该的?

EDIT: Below is my VB.NET code. This is the same code we use in over 100 other places:

编辑:下面是我的VB.NET代码。这与我们在其他100多个地方使用的代码相同:

Dim paramRowID As New SqlParameter("@RowID", sRowID) Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteNonQuery(oConn, "spDeleteRow", paramRowID)

Dim paramRowID As New SqlParameter(“@ RowID”,sRowID)Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteNonQuery(oConn,“spDeleteRow”,paramRowID)

See SqlHelper source here.

请在此处查看SqlHelper源代码。

EDIT: I hate myself right now. :) SQL threw an exception "nvarchar is incompatible with image" about another parameter that I was passing NULL to. SSMS didn't worry about the type, but VB.NET did since I didn't explicitly tell it that it was of type image. Once I defined that param, it worked. I wish profiler would have told me there was an error though.

编辑:我现在讨厌自己。 :) SQL抛出异常“nvarchar与图像不兼容”有关我传递NULL的另一个参数。 SSMS并不担心这种类型,但VB.NET确实没有明确告诉它它是类型图像。一旦我定义了那个参数,就可以了。我希望探查者会告诉我有一个错误。

Any help would be appreciated,

任何帮助,将不胜感激,

Greg

2 个解决方案

#1

That would be because SSMS does not call an RPC but a batch. There is no way in fact to call a RPC from SSMS since you cannot declare a parameter, which is what differentiate an RPC call from a batch call in TDS:

那是因为SSMS不会调用RPC而是调用批处理。实际上没有办法从SSMS调用RPC,因为您无法声明参数,这是区分RPC调用与TDS中的批调用的区别:

2.2.1.3 SQL Batch To send a SQL statement or a batch of SQL statements, the SQL batch, represented by a Unicode string, is copied into the data section of a TDS packet and then sent to the database server that supports SQL. A SQL batch may span more than one TDS packet. See section 2.2.6.6 for additional detail

2.2.1.3 SQL批处理要发送SQL语句或一批SQL语句,将由Unicode字符串表示的SQL批处理复制到TDS数据包的数据部分,然后发送到支持SQL的数据库服务器。 SQL批处理可能跨越多个TDS数据包。有关其他详细信息,请参见第2.2.6.6节

2.2.1.5 Remote Procedure Call To execute a remote procedure call (RPC) on the server, the client sends an RPC message data stream to the server. This is a binary stream that contains the RPC name or numeric identifier, options, and parameters. RPCs MUST be in a separate TDS message and not intermixed with SQL statements. There can be several RPCs in one message. See section 2.2.6.5 for additional details.

2.2.1.5远程过程调用要在服务器上执行远程过程调用(RPC),客户端会将RPC消息数据流发送到服务器。这是一个二进制流,包含RPC名称或数字标识符,选项和参数。 RPC必须位于单独的TDS消息中,而不是与SQL语句混合。一条消息中可以有多个RPC。有关其他详细信息,请参见第2.2.6.5节。

So monitor instead for the SQL:BatchCompleted event and you'll see your SSMS statement(s).

因此,请监视SQL:BatchCompleted事件,您将看到您的SSMS语句。

#2

Does the user the application is using to connect to sql have permission to execute stored procedures? That is the first thing I would verify.

应用程序用于连接到sql的用户是否有权执行存储过程?这是我要验证的第一件事。

#1