Unknown token received from SQL Server

[litsz02]

I am recieving an error: Unknown Token Recieved from SQL server after running this simple update below. What the update does is take 1 table holding old and new account numbers I am trying to convert, and searches another table and updates it with a new number if the account is found. There are about 2.2 million records to be converted so maybe using a cursor is ineffective? I'm not the best programmer, but this code has worked 3 other times so i'm not sure where to go from here.

Does any one have a link to information on troubleshooting this error or might know a better way of doing this?

Below is the code if you are interested:
-------------------------------------------
Declare @oldaccountnumber varchar(24),
@newaccountnumber varchar(18)

Declare mycursor2 CURSOR FOR
SELECT Original_Acct_Num, New_Num from Tmp_Update_Acct

OPEN mycursor
FETCH NEXT FROM mycursor into @oldaccountnumber, @newaccountnumber
while @@FETCH_STATUS = 0
BEGIN
Update Acct_Table
Set "ACCOUNT-NUMBER" = @newaccountnumber
Where "ACCOUNT-NUMBER" = @oldaccountnumber

FETCH NEXT FROM mycursor2 into @oldaccountnumber, @newaccountnumber
END

close mycursor
deallocate mycursor

 

[dbomrrsm]

What I would do now to try and get to the bottom of this is run my last code but on each of the three records that you know are duplicated -

update acct_table
set [account-number] = (select New_Num
from Tmp_Update_Acct
where acct_table.[account-number] = Tmp_Update_Acct.Original_Acct_Num)
where  [account-number] IN (Select Original_Acct_Num from Tmp_Update_Acct)
and [account-number] = one_of_the_three_you_know

Then if you dont get an error try the second of the three and then the third. This will prove if it is one of those records that are causing the problem because I have a feeling that it might not be.

Post back when you have tried this.

Good luck - we will get there in the end

[blue]DBomrrsm[/blue]

 

[SQLSister]

Is it possible that one of your newID matches an existing id that you are not updating and it just happens that the datetime of that matches as well causing the PK violation?

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[litsz02]

I am rerunning the query...There has to be some duplicate issue in there somehow. I checked and it doesnt seem there should bt (the date-time field goes to the millisecond, but anything is possible) It looks like everything updated except for 1 record which i'm investigating why. I'll check back....

 

[litsz02]

EUREEKA! OK there was 1 trouble record. It says there is a duplicate Primary Key issue that is blowing all this up. But I looked and it should update fine:

update ACCT_TABLE
set [ACCOUNT-NUMBER] = '22453CTR23'
where [ACCOUNT-NUMBER] = '22453000133'

yields the violation of the primary key contraint (duplicate rows) in the ACCT_TABLE.

So now i do a select on the ACCT_TABLE on BOTH accounts (new and old) and it gives me 8 records:
22453000133 2003-08-18 10:23:34.000
22453000133 2003-08-18 10:49:37.000
22453CTR23 2003-08-18 08:53:19.000
22453CTR23 2003-08-18 10:23:34.000
22453CTR23 2003-08-18 10:49:37.000
22453CTR23 2003-09-04 09:17:53.000
22453CTR23 2004-11-28 00:09:17.000
22453CTR23 2004-12-20 00:04:23.000

Look at lines 1 and 2, 4 and 5. I am trying to update this account and it would infact give me a PK issue. It would create a duplicate record. how strange this has been. I told the client and he isnt sure why this has happend, maybe it wasnt on the original conversion list from months ago. But all is settled now and after 2 days of racking my brain, I'm takin a vacation from computers.

Sql Sister and dbomrrsm, THANK YOU SO MUCH FOR ALL YOUR ASSISTANCE!!! i cant tell you how great it is to know there are Database guru's out there that really care to help. Thanks so much!!!!

 

[dbomrrsm]

your welcome and Im really pleased you got it sorted - told you we would in the end ;-)

[blue]DBomrrsm[/blue]

 

[SQLSister]

Great, glad we got it to work out.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief