Microsoft KB Archive/271827

= BUG: DOC Error: Cannot Use Variables as SEED Parameter to the IDENTITY Function =

Article ID: 271827

Article Last Modified on 10/16/2003

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q271827



BUG #: 58240, 58294 (SQLBUG_70)

BUG #: 235891 (SHILOH)



SYMPTOMS
The IDENTITY function is used only in a SELECT statement with an INTO table clause to insert an identity column into a new table. SQL Server Books Online ought to include information stating that variables are not allowed as parameters to this function.

SQL Server 7.0 accepts a variable for the SEED parameter but does not return the expected results. SQL Server 2000 returns the following error message:

Server: Msg 170, Level 15, State 1, Line 3

Line 3: Incorrect syntax near '@myident'.



WORKAROUND
To work around this problem, dynamically build the Transact-SQL command into a string, and then EXECute that string: DECLARE @SQL varchar(8000) DECLARE @myident int SET @myident = 100 SET @sql = 'SELECT IDENTITY(INT, ' + CAST(@myident as varchar) + ', 1)   AS ident INTO #T2' EXEC(@sql) SELECT @@identity



STATUS
Microsoft has confirmed this to be a problem in SQL Server 7.0 and SQL Server 2000.



MORE INFORMATION
To reproduce this behavior, run the following script: DECLARE @myident int SET @myident = 100 SELECT IDENTITY(int, @myident, 1) AS ident INTO #T1 DROP TABLE #T1 SELECT @@identity

Additional query words: Error Message Msg 170

Keywords: kbbug kbcodesnippet kbpending KB271827

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.