November 18, 2008 at 6:02 am
Ok thought of something else.
If any of the following values are inserted as null then you won't find a duplicate.
@Name AS VARCHAR(4000),
@Phone AS VARCHAR(4000),
@Email AS VARCHAR(4000),
@Obs AS VARCHAR(4000),
@EstateID AS INT
Might be work using ISNULL on the params and column values in the proc [CentralCheckDuplicate]
does that make sense.
Other than that I can't see anything else unless I see data
for example what the actually duplicate records look like and what is passed to the main procedure that does the insert.
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
November 18, 2008 at 6:12 am
he he he
sorry for all the replies.
Another possible problem could this:
Because you checking for the duplicates in another proc and not on the insert statement.
There is a chance that a row could be added between you checking for the duplicate and adding your row.
It might be worth making your insert statment look like this:
[font="Courier New"]INSERT INTO Contacts
(
Form,
Name,
Phone,
Email,
Obs,
Business,
Nature,
PropType,
Towns,
Neighborhood,
RealEstate_ID,
BPI,
MaxPrice,
fromWhere,
UserIP,
Lang,
FoundInPage,
TotalPages,
Estate,
Portal,
Origin,
ClientID,
Blocked
)
SELECT TOP 1
@Form,
@Name,
@Phone,
@Email,
@Obs,
@BusinessCLID,
@NatureCLID,
@ProptypeCLID,
@Town,
@Neighborhood,
@EstateID,
@BPI,
@MaxPrice,
@fromWhere,
@UserIP,
@Lang,
@FoundInPage,
@TotalPages,
@EstateCLID,
@Portal,
@Origin,
@ClientID,
@BadText
FROM dbo.CONTACTS
WHERE
ISNULL([Realestate_id],0) !=ISNULL(@EstateID ,0)
AND ISNULL(LEN(@OBS),0) != ISNULL(LEN(OBS),0)
AND ISNULL([NAME],'') NOT LIKE ISNULL(@Name,'') AND ISNULL([Phone],'') NOT LIKE ISNULL(@Phone ,'')
AND ISNULL([Email],'') NOT LIKE ISNULL(@Email,'') AND ISNULL([OBS],'') NOT LIKE ISNULL(@Obs ,'')[/font]
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
November 18, 2008 at 7:16 am
Hello again,
no worries for the replies... ehehe, i was looking, and no parameter can be null, the maximum is '' 🙂
I understand what u said on the last reply, but if the check is in the middle of a transaction, shouldnt be a problem right?
November 18, 2008 at 7:55 am
i put the profiler running and i catch another duplicated insert...
declare @p21 int
set @p21=1
declare @p22 int
set @p22=4135805
exec CentralSaveContactPoint @form=4,@Name=N'José XXXX',@Phone=N'xxxxxxx',@Email=N'jxxxxxxx9@gmail.com',@Obs=N'comments',@Portal=1,@Origin=0,@EstateID=741636,@Business=0,@Nature=0,@PropType=0,@Town=0,@MaxPrice=0,@ClientID=0,@BPI=0,@Lang=0,@FoundInPage=0,@TotalPages=0,@FromWhere=1,@UserIP=N'00.000.000.00',@RowCount=@p21 output,@ScopeIdentity=@p22 output
select @p21, @p22
go
declare @p21 int
set @p21=1
declare @p22 int
set @p22=4135806
exec CentralSaveContactPoint @form=4,@Name=N'José XXXX',@Phone=N'xxxxxxx',@Email=N'jxxxxxxx9@gmail.com',@Obs=N'comments',@Portal=1,@Origin=0,@EstateID=741636,@Business=0,@Nature=0,@PropType=0,@Town=0,@MaxPrice=0,@ClientID=0,@BPI=0,@Lang=0,@FoundInPage=0,@TotalPages=0,@FromWhere=1,@UserIP=N'00.000.000.00',@RowCount=@p21 output,@ScopeIdentity=@p22 output
select @p21, @p22
go
November 18, 2008 at 8:05 am
ooops.
Could you edit your post and put line returns in as the screen is now left-right scrollable.
Also have tried running that code after changing the insert statement to see what happens?
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
November 18, 2008 at 8:09 am
yes i try with the altered insert... but same thing occours...
declare @p21 int
set @p21=1
declare @p22 int
set @p22=4135805
exec CentralSaveContactPoint @form=4,@Name=N'José XXXX',@Phone=N'xxxxxxx',
@Email=N'jxxxxxxx9@gmail.com',@Obs=N'comments',@Portal=1,@Origin=0,@EstateID=741636,
@Business=0,@Nature=0,@PropType=0,@Town=0,@MaxPrice=0,@ClientID=0,@BPI=0,@Lang=0,
@FoundInPage=0,@TotalPages=0,@FromWhere=1,@UserIP=N'00.000.000.00',
@RowCount=@p21 output,@ScopeIdentity=@p22 output
select @p21, @p22
go
declare @p21 int
set @p21=1
declare @p22 int
set @p22=4135806
exec CentralSaveContactPoint @form=4,@Name=N'José XXXX',@Phone=N'xxxxxxx',
@Email=N'jxxxxxxx9@gmail.com',@Obs=N'comments',@Portal=1,@Origin=0,@EstateID=741636,
@Business=0,@Nature=0,@PropType=0,@Town=0,@MaxPrice=0,@ClientID=0,@BPI=0,
@Lang=0,@FoundInPage=0,@TotalPages=0,@FromWhere=1,@UserIP=N'00.000.000.00',
@RowCount=@p21 output,@ScopeIdentity=@p22 output
select @p21, @p22
go
November 18, 2008 at 8:23 am
Sorry my change to your insert won't work
You need to make the where clause on the insert a NOT EXISTS(SELECT id from mytable WHERE field1 = @field1)
etc
let me know if that works
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
November 18, 2008 at 8:36 am
Before i test ur new tip ( and i have to thanks again, for dont give up 🙂 ), we put the begin trans in the beginiing of the procedure... 25 min ago and no duplicate rows until now 🙂
November 18, 2008 at 8:49 am
Does your test created duplicates with the transactions in place?
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
November 19, 2008 at 4:33 am
Hi again,
How are u since yesterday 🙂
With the trans in the places the duplicate rows still appear...
November 19, 2008 at 4:33 am
Hi again,
How are u since yesterday 🙂
With the trans in the places the duplicate rows still appear...
November 19, 2008 at 4:54 am
I forgot which thread had a similar problem but here'S something else to think about...
Even with a transaction if you run your code like this :
begin tran
if not exists (Check if exists)
begin
insert into...
end
commit tran
There's a tiny delay, just long enough where a second call to the sp could be started and run right in between the moment where the if is evaluated and the insert into is executed.
Jeff Moden or Sergiy had created a test case for this which includes opening a few query windows in QA. It goes something like this :
goto
q1 :
Begin tran
if not exists ()
begin
-- do nothing
end
goto
q2 insert duplicate row and COMMIT
goto
q1 run the same insert as q2 and try COMMIT >> that'll throw an error (or insert a duplicate depending on the DRI).
The solution to this problem was quite simple, change the statement to this :
begin tran
insert into table (columns) Select columns, parameters where not exists (check exists on base table here)
commit tran
In that case, the check and the insert are run at the exact same time, while there's a lock on the row, so nothing else can get by.
Now the final question could be this, why isn't there any DRI on the table to block duplicates (wheter in unique constraints, check, or even a trigger depending on the complexity)?
I think with those 2 solutions, you should avoid that problem once and for all!
Hope this helps.
November 19, 2008 at 4:57 am
Just on top of my head... a unique constraint of the e-mail could be a great place to start.
Of course since basically all the columns can be nullable in the insert, it's kind of hard to pick a single perfect spot to block duplicates!!
Let me know if you have any more questions after you solve this!
November 19, 2008 at 6:19 am
IS this working with the change to the insert statement yet?
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
November 20, 2008 at 11:06 am
Hello again,
First to Ninja's_RGR'us:
We try this
begin tran
insert into table (columns) Select columns, parameters where not exists (check exists on base table here)
commit tran
and the same thing happens, few less times, but happens...
About the check constraints, is impossible do that, cause of the flow of the business.. in this table we save the contacts from potencial clients or clients, and this table can have hundread times the same contact, but always with different GETDATE(), so is not good political like u example, to check constraint to email field or phone field...
Regards,
Viewing 15 posts - 16 through 30 (of 35 total)
You must be logged in to reply to this topic. Login to reply