November 18, 2008 at 2:57 am
Hello to all,
I have a big problem and i cant resolve, i have one sp that insert on a table, but sometimes inserts two rows with the same results and the same getdate (), does anyone had this problem before?
Thanks
November 18, 2008 at 3:07 am
Please could you post the code your sp?
----------------------------------------------
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 3:14 am
CREATE PROCEDURE [dbo].[xxxx]
@FormAS INT,
@NameAS VARCHAR(4000),
@PhoneAS VARCHAR(4000),
@EmailAS VARCHAR(4000),
@ObsAS VARCHAR(4000),
@PortalAS INT,
@OriginAS INT,
@EstateIDAS INT,
@BusinessAS INT,
@NatureAS INT,
@PropTypeAS INT,
@TownAS INT,
@MaxPriceAS MONEY,
@ClientIDAS INT,
@BPIAS INT,
@LangAS INT,
@FoundInPageAS INT,
@TotalPagesAS INT,
@FromWhereAS INT,
@UserIPAS VARCHAR(4000),
@NeighborhoodAS VARCHAR(4000) = NULL,
@RowCount as int = 0 OUTPUT,
@ScopeIdentity as int = 0 OUTPUT
AS
SET CONCAT_NULL_YIELDS_NULL OFF;
SET NOCOUNT ON
DECLARE @ERR AS INT
/*-------------------------------ASSERTS--------------------------------*/
DECLARE @Errors TABLE(ERR INT,MESSAGE VARCHAR(4000))
DECLARE @ErrorString AS VARCHAR(4000)
IF (@Form NOT IN (1,2,3,4))
BEGIN
SET @ErrorString = 'messages0'
IF (@Portal=0)
BEGIN
INSERT INTO @Errors(ERR,MESSAGE)VALUES(-1,@ErrorString)
END
ELSE
BEGIN
RAISERROR(@ErrorString,15,1,@Form);
return;
END
END
IF ((@FormIN (3,4)) AND
(@EstateIDIS NULL OR @EstateID=0))
BEGIN
SET @ErrorString = 'messages1'
IF (@Portal=0)
BEGIN
INSERT INTO @Errors(ERR,MESSAGE)VALUES(-1,@ErrorString)
END
ELSE
BEGIN
RAISERROR(@ErrorString,15,1);
return;
END
END
IF ((@EstateIDIS NULL OR @EstateID=0) AND
(@BusinessIS NULL OR @Business=0) AND
(@NatureIS NULL OR @Nature=0) AND
(@PropTypeIS NULL OR @PropType=0) AND
(@TownIS NULL OR @Town=0))
BEGIN
SET @ErrorString = 'messages2'
IF (@Portal=0)
BEGIN
INSERT INTO @Errors(ERR,MESSAGE)VALUES(-1,@ErrorString)
END
ELSE
BEGIN
RAISERROR(@ErrorString,15,1);
return;
END
END
IF ((@Portal=0) AND
(@FormIN (1,2)) AND
(@ClientIDIS NULL OR @ClientID=0))
BEGIN
SET @ErrorString = 'message3'
IF (@Portal=0)
BEGIN
INSERT INTO @Errors(ERR,MESSAGE)VALUES(-1,@ErrorString)
END
ELSE
BEGIN
RAISERROR(@ErrorString,15,1);
return;
END
END
IF ((@Portal=1) AND
(@FormIN (1,2)) AND
(@TownIS NULL OR @Town=0))
BEGIN
SET @ErrorString = 'message4'
IF (@Portal=0)
BEGIN
INSERT INTO @Errors(ERR,MESSAGE)VALUES(-1,@ErrorString)
END
ELSE
BEGIN
RAISERROR(@ErrorString,15,1);
return;
END
END
/*-------------------------------/ASSERTS-------------------------------*/
DECLARE @BusinessCLIDAS INT;
DECLARE @NatureCLIDAS INT;
DECLARE @PropTypeCLIDAS INT;
DECLARE @EstateCLIDAS INT;
DECLARE @Neighborhood_IDAS INT;
DECLARE @Zone_IDAS INT;
DECLARE @ImovelRefAS VARCHAR(128);
DECLARE @NatureNameAS VARCHAR(32);
DECLARE @PropTypeNameAS VARCHAR(32);
DECLARE @TownNameAS VARCHAR(64);
DECLARE @NeighborhoodNameAS VARCHAR(64);
DECLARE @ZoneNameAS VARCHAR(256);
IF (@EstateID IS NOT NULL AND @EstateID!=0)
BEGIN
SELECT@ClientID=re.client_ID,
@Business=re.business_ID,
@Nature=re.Category_ID,
@PropType=re.Proptype_ID,
@EstateCLID=re.Clid,
@Town=re.Town_ID,
@Neighborhood_ID = re.Neighborhood_ID,
@Zone_ID = re.Zone_ID,
@ImovelRef = re.ref,
@MaxPrice=re.Price
FROM AdRealEstates..RealEstates re WITH (NOLOCK)
WHERE re.id = @EstateID
IF @@ROWCOUNT = 0
BEGIN
INSERT INTO log_imoveis_dalia (imovel_id, type,date)
SELECT @EstateID,1,GETDATE()
SELECT
@ClientID=i.cliente,
@Business=i.negocio,
@Nature=i.natureza,
@PropType=i.tipologia,
@EstateCLID=i.clid,
@Town=i.concelho,
@Neighborhood_ID = i.Freguesia,
@Zone_ID = i.ZonaId,
@ImovelRef = i.ref,
@MaxPrice=i.preco
FROM dalia.portal.dbo.imoveis i WITH(NOLOCK)
WHERE i.ID=@EstateID
END
IF (@EstateCLID IS NULL)
BEGIN
SELECT@ClientID=re.client_ID,
@Business=re.business_ID,
@Nature=re.Category_ID,
@PropType=re.Proptype_ID,
@EstateCLID=re.Clid,
@Town=re.Town_ID,
@Neighborhood_ID = re.Neighborhood_ID,
@Zone_ID = re.Zone_ID,
@ImovelRef = re.ref,
@MaxPrice=re.Price
FROM AdRealEstates..RealEstates re WITH (NOLOCK)
WHERE re.id = @EstateID
IF @@ROWCOUNT = 0
BEGIN
INSERT INTO log_imoveis_dalia (imovel_id, type, date)
SELECT @EstateID,0,GETDATE()
SELECT
@ClientID=i.cliente,
@Business=i.negocio,
@Nature=i.natureza,
@PropType=i.tipologia,
@EstateCLID=i.clid,
@Town=i.concelho,
@Neighborhood_ID = i.Freguesia,
@Zone_ID = i.ZonaId,
@ImovelRef = i.ref,
@MaxPrice=i.preco
FROM dalia.portal.dbo.imoveisretirados i WITH (NOLOCK)
WHERE i.ID=@EstateID
END
END
END
SELECT @BusinessCLID=ClidFROM AdRealEstates.dbo.Business WITH (NOLOCK)WHERE Business_ID=@Business;
SELECT @NatureCLID=Clid , @NatureName = name FROM AdRealEstates.dbo.Category WITH (NOLOCK) WHERE Category_ID=@Nature;
SELECT @PropTypeCLID=Clid,@PropTypeName=name FROM AdRealEstates.dbo.Proptype WITH (NOLOCK) WHERE Proptype_ID=@PropType;
SELECT @TownName = NAME FROM AdRealEstates.dbo.Town WITH (NOLOCK) WHERE Town_ID = @Town
SELECT @NeighborhoodName = NAME,@Neighborhood = name FROM AdRealEstates.dbo.Neighborhood WITH (NOLOCK) WHERE Neighborhood_ID = @Neighborhood_ID
SELECT @ZoneName = NAME FROM AdRealEstates.dbo.Zone WITH (NOLOCK) WHERE Zone_ID = @Zone_ID
/*------------------Fix Some Values-----------------------*/
IF (@BusinessCLID IS NULL)SET @BusinessCLID=-1
IF (@NatureCLID IS NULL)SET @NatureCLID=-1
IF (@PropTypeCLID IS NULL)SET @PropTypeCLID=-1
IF (@Town IS NULL)SET @Town=-1
IF (@EstateCLID IS NULL)SET @EstateCLID=0
IF (@EstateID IS NULL)SET @EstateID=0
/*-------------------CHECK TEXT--------------------------*/
DECLARE @BadText AS INT
EXEC @BadText = CentralSaveContactCheckText @Obs
IF (@BadText=0)
EXEC @BadText = CentralSaveContactCheckText @Name
IF (@BadText=0)
EXEC @BadText = CentralSaveContactCheckText @Phone
IF (@BadText=0)
EXEC @BadText = CentralSaveContactCheckText @Email
BEGIN TRAN;
IF(@Form IN (3,4))
BEGIN
DECLARE @IsDuplicate AS INT
EXEC @IsDuplicate = CentralCheckDuplicate @Name, @Phone, @Email, @Obs, @EstateID
IF(@IsDuplicate = 1)
BEGIN
IF (@Portal=0)
BEGIN
--INSERT INTO @Errors(ERR,MESSAGE)VALUES(-1,'message 10')
SELECT * FROM @Errors WHERE ERR != 0
return;
END
ELSE
BEGIN
--RAISERROR('message11',15,1);
return;
END
END
END
--PRINT 'o'
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
)
VALUES
(
@Form,
@Name,
@Phone,
@Email,
@Obs,
@BusinessCLID,
@NatureCLID,
@ProptypeCLID,
@Town,
@Neighborhood,
@EstateID,
@BPI,
@MaxPrice,
@fromWhere,
@UserIP,
@Lang,
@FoundInPage,
@TotalPages,
@EstateCLID,
@Portal,
@Origin,
@ClientID,
@BadText
)
select @RowCount = @@RowCount, @ScopeIdentity = SCOPE_IDENTITY()
DECLARE @ContactIDAS INT;
SET @ContactID = @ScopeIdentity;
IF (@ContactID IS NULL)
BEGIN
SET @ErrorString = 'message error'
IF (@Portal=0)
BEGIN
INSERT INTO @Errors(ERR,MESSAGE)VALUES(-1,@ErrorString)
END
ELSE
BEGIN
RAISERROR(@ErrorString,15,1);
ROLLBACK;
return;
END
END
DECLARE @SMSERRS AS INT
SET @SMSERRS=0
IF (@BadText=0)
BEGIN
EXEC CentralSaveContactDestination @ContactID
/*
IF(@Form IN (3,4)
AND NOT EXISTS(SELECT * FROM @Errors WHERE ERR != 0))
AND EXISTS (SELECT c.ID
FROM celia.sms.dbo.Client c WITH (NOLOCK)
INNER JOIN celia.sms.dbo.ClientService cs WITH (NOLOCK) ON cs.Client_UID = c.UID AND cs.service_ID = 2 AND cs.status_id = 1
WHERE ((c.PaymentType_ID = 1 AND c.Balance > 0) OR c.PaymentType_ID = 2)
AND c.Active = 1 AND ID = @ClientID)*/
IF(@Form IN (3,4)
AND NOT EXISTS(SELECT * FROM @Errors WHERE ERR != 0))
AND EXISTS (SELECT client_id
FROM client_sms WITH (NOLOCK)
WHERE client_id = @ClientID)
BEGIN
set @ZoneName =coalesce(@ZoneName,'')
set @Portal = coalesce(@Portal,0)
set @Form = coalesce(@Form ,0)
set @ClientID =coalesce(@ClientID,0)
set @Phone =coalesce(@Phone,'')
set @Name =coalesce(@Name,'')
set @Email =coalesce(@Email,'')
set @NatureName=coalesce(@NatureName,'')
set @PropTypeName =coalesce(@PropTypeName,'')
set @TownName =coalesce(@TownName,'')
set @NeighborhoodName =coalesce(@NeighborhoodName,'')
set @ImovelRef =coalesce(@ImovelRef,'')
set @MaxPrice =coalesce(@MaxPrice,'')
EXEC @SMSERRS = SENDSMS@ORG = @Portal,
@CTYP = @Form ,
@CLI =@ClientID,
@PHO =@Phone,
@NAM =@Name,
@eml =@Email,
@NAT =@NatureName,
@TYP =@PropTypeName,
@TWN =@TownName,
@NGH =@NeighborhoodName,
@ZON =@ZoneName,
@REF =@ImovelRef,
@PRI =@MaxPrice
END
END
--IF(@SMSERRS > -6)
SELECT * FROM @Errors WHERE ERR != 0
IF(@@ROWCOUNT>0)
ROLLBACK;
ELSE
COMMIT;
SET NOCOUNT OFF
November 18, 2008 at 3:29 am
Is there a trigger on the table you are inserting into ?
Also how is this sp called?
Is it not being called twice at the same time?
----------------------------------------------
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 3:35 am
hi,
There is no trigger here, this sp is called from application, but even if the sp is called 2 times, i have a validation in sp that checks if there is any row equal insert...
Tell me something, even the sp is call 2 times at the same time, the getdate on the table can be the same? is very strange, because the developers said that never call this sp 2 times... im getting bored 🙂
thanks
November 18, 2008 at 3:39 am
I've heard that getdate() normally has a 3 ms difference, so if the proc ran in less than 3 ms then there is a chance the dates could be the same I think?!?!
does this happen to you proc when you run it in SQL console or only when you run it from the app?
----------------------------------------------
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 3:43 am
Just when the sp is run from app...
November 18, 2008 at 3:48 am
carlos.tapadinhas (11/18/2008)
Hello to all,I have a big problem and i cant resolve, i have one sp that insert on a table, but sometimes inserts two rows with the same results and the same getdate (), does anyone had this problem before?
Thanks
Carlos - which table?
Cheers
chrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 18, 2008 at 5:25 am
Hi again,
I see now that the problem is that in sometimes the app call the sp two times, but even in that way, the database shouldnt let the row be insert 2 times, i had With (nolock) on the table that is in the middle of a transaction, i retire the with (nolock) and the problem didnt appear again, until 5 minutes ago, another duplicate row was inserted, i dont have anymore ideias...
November 18, 2008 at 5:27 am
how does the proc stop a dup record getting inserted?
If it's this proc? please could you supply the code:CentralCheckDuplicate
----------------------------------------------
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 5:37 am
Hello Chris, first of all, i want to thank you for keeping help me...
CREATE PROCEDURE [dbo].[CentralCheckDuplicate]
@NameAS VARCHAR(4000),
@PhoneAS VARCHAR(4000),
@EmailAS VARCHAR(4000),
@ObsAS VARCHAR(4000),
@EstateIDAS INT
AS
SET NOCOUNT ON
--IF(EXISTS(SELECT ID FROM CONTACTS WHERE [NAME]=@Name AND [Phone]=@Phone AND [Email]=@Email AND [OBS]=@Obs AND [Realestate_id]=@EstateID AND [Date] >= DateAdd(mi,-60,GetDate()) ))
IF(EXISTS(SELECT ID
FROM (SELECT TOP 250 ID,[Realestate_id],Name,Phone,Email,Obs
FROM dbo.CONTACTS ORDER BY ID DESC) t
WHERE [Realestate_id]=@EstateID
AND LEN(@OBS) = LEN(OBS)
AND [NAME] LIKE @Name AND [Phone] LIKE @Phone
AND [Email] LIKE @Email AND [OBS] LIKE @Obs
))
BEGIN
RETURN 1
END
ELSE
BEGIN
RETURN 0
END
SET NOCOUNT OFF
November 18, 2008 at 5:44 am
oh it's no problem.
OK I think I found the problem.
When you create the record you INSERT @EstateCLID INTO RealEstate_ID
However when you call dbo.[CentralCheckDuplicate]
You pass in @EstateID which looks at [Realestate_id] in the where clause.
Basically you not comparing the same fields.
If you passed @EstateCLID into the CentralCheckDuplicate proc I would say that you would find duplicates.
Let me know if this 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 5:56 am
Hi again,
i can't see where that appears, can u tell me here in sp?
Regards
November 18, 2008 at 5:58 am
look at the fields in the insert statement.
Then look at the values you pass into the check dup proc which is just above the insert statement.
----------------------------------------------
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 5:59 am
I'm sorry thats not a soution...
They are different fields I got confused sorry I'll keep looking
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
Viewing 15 posts - 1 through 15 (of 35 total)
You must be logged in to reply to this topic. Login to reply