Unable to insert record from stored procedure call

Question

I have the following table:

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

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

EXEC sp_omgwth 'Gareth', 2

and get

tbl_key,name,id
1,Gareth,2

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

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.

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.

Answer 1

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.

Answer 2

Perhaps you need to provide an example of the SQL Client call from the webservice code. You might try setting and checking the EXECUTE return status.