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

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]

<%
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

%>

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

• 
  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.
  

GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[myStoredProcedure]

	@FName AS VARCHAR(100), 
	@LName AS VARCHAR(100)	
AS
        SET NOCOUNT ON;
	DECLARE @CustId AS INT
	INSERT INTO myCustomers(FirstName,LastName) VALUES(@FName,@LName)
	SET @CustId = @@IDENTITY

	SELECT @CustId

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

CustID = RS("CustId")  & "<br>"

ALTER PROCEDURE [dbo].[myStoredProcedure]

	@FName AS VARCHAR(100), 
	@LName AS VARCHAR(100)	
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @CustId table ( CustID int )

	INSERT INTO myCustomers(FirstName,LastName)
	output inserted.CustID 
	into @CustId
	VALUES(@FName,@LName)

	SELECT CustID from @CustId 
END