Select @@IDENTITY returning multiple rows?! 
Author Message
 Select @@IDENTITY returning multiple rows?!

This one is baffling me to no end... I've got a script here that will
pull information out of one table and spread it across three tables.
The server is telling me that when I run this script that the
following line(s) are returning multiple values which is impossible


When I run it I get this:

Server: Msg 512, Level 16, State 1, Line 163
Subquery returned more than 1 value. This is not permitted when the
subquery follows =, !=, <, <= , >, >= or when the subquery is used as
an expression.

I removed some header comments so the line doesn't match in this copy
of the script... but I know it is this line causing the error.  This
is SQL Server 2000 SP1 on Win2K SP2.  Anybody have an inkling?

Here's the entire script:
------------------------------------------------------
BEGIN TRANSACTION T1

DECLARE





























DECLARE old_records CURSOR FOR
SELECT  [csr_id],
        [first_name],
        [middle_name],
        [last_name],
        [suffix],
        [password],
        [institution],
        [department],
        [job_title],
        [street_addr_1],
        [street_addr_2],
        [city],
        [state],
        [postal_code],
        [country],
        [phone],
        [fax],
        [email],
        [timestamp],
        [ip_address],
        [anonymous]
FROM [installation]
ORDER BY [last_name] ASC, [first_name] ASC, [password] ASC,
[timestamp] DESC

OPEN old_records

FETCH NEXT FROM old_records INTO























BEGIN





                BEGIN
                        --
                        -- Create the additional client (connector) and hook it up
                        -- to the record that was generated previously.  We'll validate
                        -- this list with a temporary view once done.
                        --
                        -- This is how we identify the same person on different computers
                        -- now instead of matching on first/last/pw.
                        --

                        INSERT INTO client (csr_id, account, ip_address)

                        PRINT 'Attached additional client to previously created account.'
                END
        ELSE
                BEGIN



                        --
                        -- Insert a new person record
                        --

                        INSERT INTO person (
                                created,
                                first_name,  
                                middle_name,  
                                last_name,  
                                suffix,
                                institution,  
                                department,  
                                job_title,  
                                street_addr_1,
                                street_addr_2,  
                                city,  
                                state,  
                                postal_code,  
                                country,
                                phone,  
                                fax,  
                                email) VALUES (


















                        --
                        -- Create the account
                        --



                        --
                        -- Create the client (connector)
                        --

                        INSERT INTO client (csr_id, account, ip_address)

                END

        FETCH NEXT FROM old_records INTO





















END

CLOSE old_records
DEALLOCATE old_records

COMMIT TRANSACTION T1
------------------------------------------------------



Sun, 21 Dec 2003 00:38:55 GMT
 Select @@IDENTITY returning multiple rows?!


Instead use:


If you are using SQL 2K then it is safer to use the SCOPE_IDENTITY() function:



The SCOPE_IDENTITY () function always returns the last identity value for the current session in the current scope and is not affected by INSERT triggers.

Regards

Vaughan Powell
MCDBA, MCSD, MCSE
Internet Database Architect

Keybright Services Ltd
<http://www.keybright.co.uk/>

'Databases for the Internet'

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!



Sun, 21 Dec 2003 01:16:31 GMT
 Select @@IDENTITY returning multiple rows?!
Vaughan many thanks, that did the trick.


Mon, 22 Dec 2003 22:32:43 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. select returns identity row from dropped table

2. question regarding select (multiple rows select into one result row)

3. urgent: returning multiple rows in single SELECT statment

4. HELP! UPDATE single-row subquery returns multiple rows

5. Single-row refresh operation returned multiple row

6. stored procdure return a set of multiple rows or single row

7. select case return rows in one row (how to group by three column)

8. return 10 rows starting with row 100 from a select

9. Grouping multiple rows into 1 row during a select into

10. SELECT multiple rows back as one row with many columns

11. Inserting a record and returning identity row from VB5

12. column in multiple rows to multiple columns in one row


 
Powered by phpBB® Forum Software