none
SQL Serve 2008 Does Not Return Identity Value

    Question

  • We just upgraded from Windows 2003/SQL2000 to Windows 2008R2/SQL200R2 and we are using a website in classic ASP.
    We are running into a problem where SQL server stored procedure does not return identity value after doing an insert.

     


    My Stored Procedure

    GO
    SET ANSI_NULLS OFF
    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    CREATE PROCEDURE [dbo].[myStoredProcedure]
     @FName AS VARCHAR(100), 
     @LName AS VARCHAR(100) 
    AS
     DECLARE @CustId AS INT
     INSERT INTO myCustomers(FirstName,LastName) VALUES(@FName,@LName)
     SET @CustId = @@IDENTITY
     SELECT @CustId
    

    My Table

    CREATE TABLE [dbo].[myCustomers](
     [CustomerID] [int] IDENTITY(1,1) NOT NULL,
     [FirstName] [varchar](50) NULL,
     [LastName] [varchar](50) NULL,
     CONSTRAINT [PK_myCustomers] PRIMARY KEY CLUSTERED 
    (
     [CustomerID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    


    My ASP Code

    <%
    strConn = "Provider=SQLOLEDB;SERVER=mySERVER;Uid=myUserName;Pwd=myPassWord;Database=myDatabase"
    set conn = server.createobject("ADODB.Connection")
    conn.open strConn 
    If Err.Number <> 0 then
     Response.Write Err.Number & "<br>"
     Response.Write Err.Description & "<br>"
    Else
     Response.Write "Conned established<br>" 
    End If 
    strSQL = "exec myStoredProcedure 'myFirstName', 'MyLastName'"
    SET RS  = conn.execute(strSQL)
    CustID = RS("CustID")  & "<br>"
    Response.Write "CustID = " & CustID 
    RS.Close
    SET RS = Nothing
    conn.close
    SET conn = nothing
    Response.End
    %>
    


    My Error Message

    Conned established
    ADODB.Recordset error '800a0cc1' 
    Item cannot be found in the collection corresponding to the requested name or ordinal.
    
    myTest.asp, line 12 
    

     


    Here is what I have tried I have so far:


    1. If I comment the INSERT statement in stored procedure and hard code some value to @CustID such as @CustID=1000, it works.

    2. If I DO NOT comment  INSERT statement in stored procedure and hard code some value to @CustID such as @CustID=1000, it gives the same error as above.

    3. Same code with same stored procedure works with Windows 2003/SQL2000.


    Please help.

     



    • Edited by SKB-007 Friday, July 26, 2013 12:04 AM
    Thursday, July 25, 2013 11:55 PM

All replies

  • Your stored procedure is returning a column without a name.  Sql Server will not automatically supply a column name if the value being SELECTed is a variable or function.   Try this and I believe your .asp code will pick it up the new IDENTITY value:
     SELECT @CustId AS 'CustID'


    Friday, July 26, 2013 12:54 AM
  • Last two rows of a stored procedure should be changed to:

     SET @CustId = SCOPE_IDENTITY()
     SELECT [CustId] = @CustId

    Explanation:

    1) do not ever use @@IDENTITY. Use SCOPE_IDENTITY() instead. @@IDENTITY will give you wrong result in case the table has a trigger that does some inserts (you will get identity from those trigger's inserts). SCOPE_IDENTITY is always correct, regardless of what triggers on the table do.

    2) If your ASP code reads a value by the column name, you must give that column a name in the TSQL procedure


    Friday, July 26, 2013 1:17 AM
  • We are running into a problem where SQL server stored procedure does not return identity value after doing an insert.

    Add SET NOCOUNT ON after the AS statement of your proc.  This will suppress the DONE_IN_PROC message (rowcount) returned after the insert statement, which is returned to ADO classic as an empty result set and will be returned before the susbsequent SELECT statement result set.  Alternatively, you could invoke the command NextResult method immediately after the execute method in the code.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Friday, July 26, 2013 3:39 AM