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]

you dont need a cursor to do this:

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

[blue]DBomrrsm[/blue]

 

[SQLSister]

Never, but absolutely never, update a table with millions of records using a cursor!!!! Cursors are terribly inefficient.

Try

UPDATE Acct_Table
SET [CCOUNT-NUMBER]= Tmp_Update_Acct.New_Num 
FROM Acct_Table join Tmp_Update_Acct
ON Acct_Table.[ACCOUNT-NUMBER] = Tmp_Update_Acct.Original_Acct_Num[/cpode]

Of course try this on a development database first and make sure it gives the results you want. 


Questions about posting. See faq183-874
Click here to learn [url=http://www.google.com/tsunami_relief.html]Ways to help with Tsunami Relief[/url]

 

[litsz02]

Wow Thanks for the very speedy feedback. I actually tried both of the above code and both came back with an error:

For the first one it said that null values cannot be placed in the account field:
Server: Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'ACCOUNT-NUMBER', table 'TD03.dbo.Acct_Table'; column does not allow nulls. UPDATE fails.

I checked the Tmp_Update_acct table and there are no nulls in it....wierd.

and in the second, it said something about a duplicate primary key issue. Wierd... either of those error messages dont make sense to me seeing that there should not be any duplicates from the computer generated Tmp_Update_Acct table...

I have 2.2 million records in the Tmp_Update_Acct table and 14 million in the Acct_Table.

 

[dbomrrsm]

do

select New_Num
from Tmp_Update_Acct a join acct_table b
on a.Original_Acct_Num = b.acct_table
where new_num is null

and see if any null values are trying to be inserted into a field that cant be null

[blue]DBomrrsm[/blue]

 

[litsz02]

Yeah its very wierd. I just ran that code and comes back empty. The update code looks perfect to me. Not sure why it would give me that error.....

 

[SQLSister]

If you have 14.2 million records each with a unique ID, then I would think your temp table would also have that many records. Otherwise some of your records will not get a new value. And how are you handling changing the information in related tables?

Also a thought, is there a trigger on the table? S0metimes the error message is from the trigger not the query. Especially if the trigger is not set up to handle mutliple record updates.

While a set-based solution is best, for this many records, tyou might want to do some batch processing to avoid having the processor tied up for so long. see the following FAQ for advice on that.
SQL Server Performance Issues
How to Use Batch Size to Speed Mass Updates, Inserts and Deletes
faq183-3141

Check for duplicate values:

Select Acct_Table.[ACCOUNT-NUMBER] from Acct_Table
Having Count(Acct_Table.[ACCOUNT-NUMBER]) >1

Select New_Num from Tmp_Update_Acct 
Having Count(Tmp_Update_Acct.NewNum ) >1

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

 

[dbomrrsm]

we will get this one sorted sister but I am sorry I may have to leave it to you until tomorrow - its late here and I'm tired

[blue]DBomrrsm[/blue]

 

[litsz02]

Yeah I actually have about 14.2 million records in the Main Account table. I have about 2.2 million records in that Tmp_Update_Acct table that I am trying to update the main table with. I am basically just trying to do whereever the Old Account number is found in the Main Account table, update it with the new number found in the Tmp_Account Table.
I'm not changing any other information in related tables because there arent any relationships. I ran that code with the cursor before and it worked fine, so i know the logistics of just changing it is fine.

I didnt set up the table so really, I'm not too sure about whether there is a trigger or not. That does seem like a possibility though...good thought.

SQLSister,
The way the primary key is set up in the Acct_table is a 2 Primary Key field, ACCOUNT-NUMBER and Date_Time. So Duplicate ACCOUNT_NUMBERS is ok as long as they have different DATE_TIME fields. I ran the above query on the Tmp_Acct_Table and it showed me 3 duplicate records. I checked all 3 of them in the Main Acct_Table, and all 3 had more than one record, but different Date_Time fields, so the duplicate primary key error is not in fact true.

Thanks so much for sticking with me i cant tell you how much i appreciate all the insight you've given me...I'll keep playing with it I guess....If you happen to think of anything else, I'd appreciate it!

 

[dbomrrsm]

As far as I can gather using my code tried to update a non nullable column with a null value so there must be a null value in Tmp_Update_Acct.

do a

select * from Tmp_Update_Acct where new_num is null

and let us know the results.

[blue]DBomrrsm[/blue]

 

[litsz02]

I agree with you 100%. Your code makes sense. I ran the select statement to find nulls and it came back: (0 row(s) affected). So i dont understand why it would tell me that I'm trying to insert a null value?!

I just ran your original code again and it gave me the same error:
"Cannot insert the value NULL into column 'ACCOUNT-NUMBER', table 'TD03.dbo.Acct_Table'; column does not allow nulls. UPDATE fails. The statement has been terminated."

I'm using the Microsoft Query Analyzer and it runs for about 3 minutes and then yields the above error.

 

[dbomrrsm]

Try this

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)

This is then only trying to update acct_table where it has an existence in Tmp_Update_Acct.

[blue]DBomrrsm[/blue]

 

[litsz02]

I swear its gonna be one of those weeks I feel like every time I post I'm just the bearer of more bad news. It came back now with the error:
Violation of PRIMARY KEY constraint 'PK_Detail'. Cannot insert duplicate key in object 'Detail'.

This shouldnt be the case, seeing that its a 2 field Primary Key, a DATE-TIME stamp and the ACCOUNT-NUMBER are labeled as PK's in the ACCT_TABLE. I ran a check on the ACCT_TABLE and each duplicate ACCOUNT that I am trying to update (total of 3 of them) has its own unique Time Stamp.

Would I be getting this error because I am only updating only part of the primary key perhaps? In my original cursor code, I did it that way and it seemed to be fine though.

 

[dbomrrsm]

is your PK on acct_table called 'PK_Detail' and what is the object called 'Detail' is this some other table affected via a trigger on update of acct_table.

[blue]DBomrrsm[/blue]

 

[dbomrrsm]

just recreated your scenario and forced an insert that would violate the PK and itr gave a message:

Violation of PRIMARY KEY constraint 'PK_acct_table'. Cannot insert duplicate key in object 'acct_table'.


in short it says the pk on acct_table - the table I was inserting into - has caused the violation - your error suggests a violatin on an object called Detail - look through your system tables for an object called this if you cant relate directly to what this is.

[blue]DBomrrsm[/blue]

 

[litsz02]

Yeah, ya know I typed that out..i typed the wrong text in there Sorry. The actual error is:
Violation of PRIMARY KEY constraint 'PK_ACCT_TABLE'. Cannot insert duplicate key in object 'ACCT_TABLE'.

I'm multi-tasking over here and working on another database. I got my head in a spin for a second. Sorry about that.

Thanks so much for spending so much time with this.

 

[dbomrrsm]

in em right click on the table select all tasks - generate sql script - under options table scripting options tick all 4 so the indexes etc ate scripted then on general tab click preview and post the generated sql here.

[blue]DBomrrsm[/blue]

 

[litsz02]

Here is the sql script:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ACCT_TABLE]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ACCT_TABLE]
GO

CREATE TABLE [dbo].[ACCT_TABLE] (
[ACCOUNT-NUMBER] [varchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DATE-TIME] [datetime] NOT NULL ,
[UPDATE-DATE] [datetime] NULL ,
[PHR-ACTION-CODE] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PHR-WORKING-DATE] [datetime] NULL ,
[PHR-DATE-OF-PAY] [datetime] NULL ,
[PHR-PAY-VIA] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PHR-AGENCY] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PHR-WORKSHEET-Y-N] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PHR-ORG-DEPARTMENT] [bigint] NULL ,
[PHR-DIGIT] [bigint] NULL ,
[PHR-SCORE] [bigint] NULL ,
[PHR-TOTEXT] [bigint] NULL ,
[PHR-HDREXT] [bigint] NULL ,
[PHR-OTHEXT] [bigint] NULL ,
[PHR-BALANCE] [float] NULL ,
[PROMISE-AMT-1] [float] NULL ,
[LETTER-CODE] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LOCATION-CODE] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PARTY-CONTACTED-CODE] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PLACE-CALLED] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PROMISE-AMT-2] [float] NULL ,
[PROMISE-DATE-2] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NONPAY-EXCUSE-CODE] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ROUTE-TO STATE-CODE] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TEXT-IND] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[USER-ID] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[USER-DEFINED-7] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[USER-DEF7-TYPE] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SELECTED-DEMOGRAPHICS] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PAYMENT-CURRENCY] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[STATE-POSITION] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ENTITY-ID] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SYSTEM] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[ACCT_TABLE] WITH NOCHECK ADD
CONSTRAINT [PK_ACCT_TABLE] PRIMARY KEY NONCLUSTERED
(
[ACCOUNT-NUMBER],
[DATE-TIME]
) ON [PRIMARY]
GO

 

[dbomrrsm]

will have a think about it and get back to you tomorrow

[blue]DBomrrsm[/blue]

 

[litsz02]

That's fine... Thanks dbomrrsm for all your help!! I cant tell you how much I appreciate it.