Results 1 to 10 of 10

Thread: error message

  1. #1
    Join Date
    Apr 2003
    Location
    Washington DC area
    Posts
    1,770

    Angry Unanswered: error message

    Server: Msg 544, Level 16, State 1, Line 1
    Cannot insert explicit value for identity column in table 'INCOMING' when IDENTITY_INSERT is set to OFF.


    It appears that some user/admin has allow data to come in while were in the cleanup process and now I get this error message. I've deleted the 66001 records that came in today before someone started the service today. This aint oracle so I can ask to give back row by rowid. Should I just settle for a lunch or should I shoot him.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Little more background....

    What's the INSERT look like?

    What do you mean by clean up
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Apr 2003
    Location
    Washington DC area
    Posts
    1,770

    Angry error message

    INSERT INTO dbo.INCOMING
    (RECORD_NUMBER, SOURCE_SERVER_IP, DATE_TIME, DESTINATION_PORT, PROTOCOL,
    DISPOSITION_CODE, BYTES_SENT, BYTES_RECEIVED, DURATION, CATEGORY, URL,
    FULL_URL, USER_ID, HITS, SOURCE_IP_TEXT, DESTINATION_IP_TEXT,
    KEYWORD)
    select distinct RECORD_NUMBER, SOURCE_SERVER_IP, DATE_TIME, DESTINATION_PORT, PROTOCOL,
    DISPOSITION_CODE, BYTES_SENT, BYTES_RECEIVED, DURATION, CATEGORY, URL,
    FULL_URL, USER_ID, HITS, SOURCE_IP_TEXT, DESTINATION_IP_TEXT,
    KEYWORD from
    incoming_temp where year(date_time) = 2004 and month(date_time) = 2
    SET IDENTITY_INSERT INCOMING On

    I think I have a solution. What do you think about running the insert without the without the record_number field. It's only historical data

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You need to do this first

    SET IDENTITY_INSERT INCOMING On

    Then the INSERT

    Just watch out for dup key next.....

    What are you doing?

    Batch cleanup?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Apr 2003
    Location
    Washington DC area
    Posts
    1,770

    Question error message

    Yes, cleaning up historical data. Why do I set on before the insert and can I make the select distinct?????????????

  6. #6
    Join Date
    Apr 2003
    Location
    Washington DC area
    Posts
    1,770

    Unhappy error message

    I attempted the modification to set indentity_insert before the insert statement and I get a different error.

    Server: Msg 2627, Level 14, State 1, Line 2
    Violation of PRIMARY KEY constraint 'PK_INCOMING'. Cannot insert duplicate key in object 'INCOMING'.
    The statement has been terminated.

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by Brett Kaiser

    Just watch out for dup key next.....

    I must be psychic!
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Apr 2003
    Location
    Washington DC area
    Posts
    1,770

    you not gonna beleive this

    One record!!!!! I said ONE Record was blocking the insert from running.

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You sure?

    Run this and let us know the count

    Code:
    SELECT COUNT(*) 
      FROM INCOMING o
     WHERE EXISTS (SELECT * 
    		 FROM incoming_temp i 
    		WHERE year(date_time) = 2004 
    		  AND month(date_time) = 2
    		  AND o.RECORD_NUMBER = i.RECORD_NUMBER)
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Apr 2003
    Location
    Washington DC area
    Posts
    1,770

    Red face error message

    cant run it now. I'm on to the next batch and there another after that. Remind me of the old tape mount days.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •