Databases
StackOverflow2013
Postsdbo
POUnable to insert record from stored procedure called from web service
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POUnable to insert record from stored procedure called from web service
    primarykey
    data
    text
    <p>I have the following table:</p> <pre><code>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] </code></pre> <p>...and the following stored procedure:</p> <pre><code>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 </code></pre> <p>I added a single record by calling the procedure from SSMS:</p> <pre><code>EXEC sp_omgwth 'Gareth', 2 </code></pre> <p>and get </p> <pre><code>tbl_key,name,id 1,Gareth,2 </code></pre> <p>So far, so good. A colleague tries to call it from a web service and gets the return 'Max_tbl_key' 2</p> <p>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:</p> <pre><code>tbl_key,name,id 1,Gareth,2 3,Gawain,4 </code></pre> <p>It's like it added the record, which incremented the identity value, but then...I don't know, rolled it back or something.</p> <p>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.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USmdoyle
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COYou should **NOT EVER** use `SELECT MAX(tbl_key) ...` to get the value of an `IDENTITY` column - use `SELECT SCOPE_IDENTITY()...` instead! Much safer - won't break in concurrency situations!
      singulars
      1. POUnable to insert record from stored procedure called from web service
      1. USmarc_s
    2. COI should probably clarify that the developers traced the issue as far as they could. They found that the SQL statement that was being passed worked perfectly fine when copied and pasted into SSMS. Also, using the same code base to add a record via an INSERT statement also worked. There seems to be something weird specifically about the webservice calling any stored procedure with an INSERT.
      singulars
      1. POUnable to insert record from stored procedure called from web service
      1. USmdoyle
    3. COAgree with `marc's` comment above. You shouldn't use `MAX`. Please post your web service code. It being in a transaction that never gets committed sounds feasible.
      singulars
      1. POUnable to insert record from stored procedure called from web service
      1. USMartin Smith
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload