With interruptions, this stored procedure will return an identifier that is in millions, when in reality it will be only about 400.
set ANSI_NULLS OFF
set QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[TestStoredProcedure]
(
@Title VARCHAR(50),
@ID INT OUTPUT
)
AS
DECLARE @ResultsTable Table(InsertedID INT);
INSERT INTO Table
(
Title
)
OUTPUT INSERTED.ID INTO @ResultsTable
VALUES
(
@Title
);
SELECT @ID = (SELECT TOP 1 InsertedID FROM @ResultsTable);
This stored procedure used to return an identifier using SCOPE_IDENTITY (), but with the same problem.
There are no triggers in the database. The primary keys that SQL Server creates automatically have only indexes. There are no tables that have an identifier somewhere next to what is being returned, so I have no idea where these big numbers come from.
Any help or suggestions would be appreciated.
Edit:
As I said above, this stored procedure originally used SCOPE_IDENTITY () as follows:
set ANSI_NULLS OFF
set QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[TestStoredProcedure]
(
@Title VARCHAR(50),
@ID INT OUTPUT
)
AS
INSERT INTO Table
(
Title
)
VALUES
(
@Title
);
SELECT @ID = SCOPE_IDENTITY();
2:
SQL:
Microsoft SQL Server 2005 - 9.00.4230.00 (X64) 30.07.2009 13:42:21 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64- ) Windows NT 5.2 ( 3790: 2)
3:
( ASP, )
set obj_CMD = Server.CreateObject("ADODB.Command")
with obj_CMD
.ActiveConnection = Application("DSN")
.CommandText = "TestStoredProcedure"
.CommandType = adCmdStoredProc
dim txt_title
txt_title = "Some text"
set oParam = .CreateParameter("@Title",adVarChar,adParamInput,50,txt_title)
.Parameters.Append oParam
set oParam = .CreateParameter("@ID",adInteger,adParamOutput)
.Parameters.Append oParam
.Execute
dim ID
ID = .Parameters("@ID").Value
end with
set obj_CMD = nothing
4:
DBCC CHECKIDENT ( "" ) :
: "422", "422".
DBCC . DBCC , .
.