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

Appreciate your response. I tried your suggestion but still same error. Here is the final version of my stored procedure:

USE [myDatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[myStoredProcedure]
@FName AS VARCHAR(100),
@LName AS VARCHAR(100)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @CustId AS INT
INSERT INTO myCustomers(FirstName,LastName) VALUES(@FName,@LName)
SET @CustId = @@IDENTITY
SELECT @CustId
END