February 9, 2009 at 12:09 am
Hi All,
I have a scheduled job that executes a stored procedure through java, trying to update date from one table to another in two steps. Below is the snippet:
CREATE PROCEDURE fdds.populate_distance_info
AS
BEGIN
SET NOCOUNT ON
-- Populate the DistanceInfo table now if the distance is less than 30 miles
TRUNCATE TABLE fdds.DistanceInfo
DECLARE @brand-2 INT
DECLARE @query VARCHAR(1000)
DECLARE brand_cursor CURSOR
FORWARD_ONLY READ_ONLY FOR
SELECT DISTINCT brand_id FROM fdds.CountryBrand ORDER BY brand_id
OPEN brand_cursor
FETCH NEXT FROM brand_cursor INTO @brand-2
WHILE @@FETCH_STATUS = 0
BEGIN
-- step 1:
SET @query = 'INSERT INTO fdds.DistanceInfo(uuid,city_id,dealer_id,brand_id,distance) '
+ 'SELECT newid(), city_id, dealer_id,' + CONVERT(VARCHAR, @brand-2) +', distance '
+ 'FROM fdds.TempDistancesMatrix tdm, fdds.Dealer d '
+ 'WHERE distance <= 30 and tdm.dealer_id=d.id AND brands LIKE ''%'+ CONVERT(VARCHAR, @brand-2) +'%'''
EXEC(@query)
-- step 2: For all the cities, where there is no dealer, insert the dealers nearest to the city
SET @query = 'INSERT INTO fdds.DistanceInfo(uuid,city_id,dealer_id,brand_id,distance) '
+ 'SELECT newid(),dm.city_id, dm.dealer_id,' +CONVERT(VARCHAR, @brand-2) +', dm.distance '
+ 'FROM ('
+ ' SELECT city_id, MIN(distance) distance '
+ ' FROM fdds.TempDistancesMatrix tdm, fdds.Dealer d '
+ ' WHERE tdm.dealer_id=d.id AND brands LIKE ''%' + CONVERT(VARCHAR, @brand-2) + '%'' '
+ ' GROUP BY city_id'
+ ' ) d, fdds.TempDistancesMatrix dm, fdds.Dealer dlr '
+ 'WHERE d.city_id NOT IN (SELECT DISTINCT city_id FROM fdds.distanceinfo '
+ ' WHERE brand_id= '+CONVERT(VARCHAR,@brand)+') '
+ 'AND d.city_id=dm.city_id AND d.distance=dm.distance '
+ 'AND dlr.id=dm.dealer_id AND dlr.brands LIKE ''%' + CONVERT(VARCHAR, @brand-2) + '%'' '
EXEC(@query)
FETCH NEXT FROM brand_cursor INTO @brand-2
END
CLOSE brand_cursor
DEALLOCATE brand_cursor
END
GO
As it can be seen, the procedure tries to copy data from TempDistancesMatrix table into Distanceinfo table. When the job that executes this procedure is run, it often fails on with the following error :
[FCXDB69]INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK__DistanceI__deale__5FBE24CE'. The conflict occurred in database 'DEALERSITES', table 'Dealer', column 'id'.
It does succeed sometimes, with the success to failure ratio almost at 1:3. Whenever it succeeded, i was able to see that no rows were inserted into the DistanceInfo table, that would violate the foreign key constraint. Also, there are no rows in the TempDisatncesmatrix table that would violate the constraint.
I tried to insert the date into distanceinfo, one row at a time, instead of the bulk insert and it works absolutely fine. All the runs were successful. So, I was wondering if the bulk insertion is the cause of the problem. However, A "Foreign Key Constraint" is completely unfathomable.
The JDK we use is 1.5, it runs on a websphere environment. We are using Microsoft SQL Server 2000 - 8.00.2040 (Intel X86) , guess it has SP4.
Any help is appreciated.
February 9, 2009 at 8:53 am
If it reports a FK violation, that's what you have. It's not Java, SPs, or anything else. You must be missing something in what you're checking. If it fails, what you really need to think about doing here is adding a logging table with no FK and duplicate the insert there. That way you can easily see what is causing the error.
February 10, 2009 at 12:55 am
I understand it is, for sure, a FK issue because when I tried removing the constraint it does work. But, as I said, with the constraint in place and inserting the 80000-odd rows one by one is not throwing the error, while a bulk insert throws the error. Could it be because of the "length of time" that the bulk insert takes?
February 10, 2009 at 7:50 am
Cross check the data inserted into the table (i.e without FK), that this data is not violating the FK constraint...
--Ramesh
February 10, 2009 at 9:41 am
How are you inserting? All the child records at one shot or Alternative like parent record first then corresponding child records??
Thanks
Vijaya Kadiyala
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply