无法从 Web 服务调用的存储过程插入记录 [英] Unable to insert record from stored procedure called from web service

问题描述

我有下表:

CREATE TABLE [dbo].[omgbbq](
    [tbl_key] [int] IDENTITY(1,1) NOT NULL,
    [name] [varchar](10) NOT NULL,
    [id] [int] NULL,
 CONSTRAINT [PK_omgbbq] PRIMARY KEY CLUSTERED 
(
    [tbl_key] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,
    ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

...以及以下存储过程:

...and the following stored procedure:

CREATE PROCEDURE sp_omgwth 
    @name VARCHAR(10), 
    @id INT
AS
BEGIN
    INSERT INTO omgbbq (name, id) VALUES (@name, @id);
    SELECT MAX(tbl_key) AS Max_tbl_key FROM omgbbq;
END

我通过从 SSMS 调用过程添加了一条记录:

I added a single record by calling the procedure from SSMS:

EXEC sp_omgwth 'Gareth', 2

并得到

tbl_key,name,id
1,Gareth,2

到目前为止，一切都很好.一位同事尝试从 Web 服务调用它并获得返回'Max_tbl_key' 2

So far, so good. A colleague tries to call it from a web service and gets the return 'Max_tbl_key' 2

也符合预期.但是，该记录不会出现在表中.当我通过从 SSMS 调用 SP 添加新记录时，我们在表中看到以下内容:

Also as expected. However, the record does not appear in the table. When I add a new record by calling the SP from SSMS, we see the following in the table:

tbl_key,name,id
1,Gareth,2
3,Gawain,4

就好像它添加了记录，增加了标识值，但是……我不知道，回滚它什么的.

It's like it added the record, which incremented the identity value, but then...I don't know, rolled it back or something.

有什么想法吗?我们俩都以同一用户身份连接到服务器，因此这似乎不是凭据权限问题.我们从另一个表和另一个 SP 得到了同样的行为，这就是我们创建这两个作为测试的原因.

Any ideas? Both of us are connecting to the server as the same user, so it doesn't seem like it could be a credential permissions issue. We are getting this same behavior from another table and another SP, which is why we created these two as tests.

推荐答案

找出数据库中发生的事情的最佳方法是运行跟踪.运行 SQL Server Profiler 并调用存储过程的 Web 服务.然后查看事件以了解发生了什么.

The best way to find out what is happening in the database is to run a trace. Run SQL Server Profiler and have a call to the web service to the stored procedure. Then look at the events to find out what is happening.

这是一个神秘的问题，但查看痕迹会告诉您是什么以及为什么.

That's a cryptic problem, but looking at a trace will tell you what and why.

这篇关于无法从 Web 服务调用的存储过程插入记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！