again proble??

just kidding dude . actually am going out of desk now.going home now. i will see once i get into appt.

Maybe 'Accountcode' is an Index ........check index and make twist as needed..

tips: disable the index and see if it works...just to make sure its an index problem and take it from there.

Good Luck

Yes, AccountCode is a unique non-clustered index:

When I insert the data into Live Table A, all other fields but this(Represented by Account) will be displayed o because it is derived from this unique non-clustered index.

I tried to do an insertion on Table B (where the Account # is), but I got this error:

Msg 2601, Level 14, State 3, Line 1

Cannot insert duplicate key row in object ''B'with unique index 'AccountCode'.

The statement has been terminated.

(0 row(s) affected)

First, Do you really want duplicate values on Table B. If yes why do  you have unique index. Simply drop the index and you should be fine.

Secondly, Wat kinda value do you want in Table B. Are you trying to insert from table A.

Can you be more specific on the value you want on table B and why are you inserting duplicate values and how do you want that to be handled.

I had to create a table C with all CustomerAccountInfo (from live table A) where subscription ends in 2 months from now. After that I update their renewal status to ‘1^st Notice’ .

Then on the main Live table A , I append all these records I just generated from table C, also doing case when ….statements.

Now, all the fields in the intranet gets uploaded automatically, besides one field Subscriber.

This subscriber field is apparently generated from unique non-clustered index in live Table B. I cannot change the structure of that table.

Table B holds CustomerPersonalInformation including a column cmp_code is the company code which needs to be put in the field Subscriber.

So after I insert into Table A, I tried updating Table B, using :

update cicmpy –this is the table B in my description

set cmp_code = (

select

enduser.cmp_code as Subscriber

from

RenewalLetter r –this is the table C in my description

left outer join cicmpy Subscriber on freeguidfield_01 = Subscriber.cmp_wwn

left outer join cicmpy Enduser on CustomerID = Enduser.cmp_wwn

where

type = 75

and ltrim(freetextfield_10) = 'Active'

and freedatefield_01 between

dateadd(mm,2,getdate())

AND dateadd(mm,2,getdate())+7

and freeboolfield_05 not like '1'

and subscriber.classificationid in ( 'HMO', 'EUR'))

And I get this error I explained in the previous post.

Certainly Its a very very complicated select statment,..

Wondering if it is possible to avoid the outer join with cicmpy enduser after the outerjoin between renewlletter and the cicmpy subscriber...it gets to complicated.

Also, i bet you have check the result of your select stattement.. can you check it again and make sure that the select statement is giving you more than one null in result. As more than one Null would violate the unique constraint.

Try doing this in your test server and see if it works

CREATE UNIQUE INDEX accountcode ON cicmpy (subscriber)
    WITH (IGNORE_DUP_KEY = ON);

PS: Remember its Monday, stressful day for most of us.

duplicate post

Its my pleasure, anyways why dnt  you get the highest id number in this case or pass the highest id number as parameter...

alter function NextCustomerNumber

returns char(7)
as
begin

set @id = select max(ID) from table_name ;--to get the highest id number from the table
return 'R' + right('000000' + convert(varchar(10), @id), 6)
end;

----------------------------------------------------------------------------------------------

OR

begin

set @id = select max(ID) from table_name ;--

select NextCustomerNumber(@id) ....

END;