October 31, 2008 at 12:53 pm
OK I have a database I am working on the I had to place a sql server agent on to execute the stored procedure on. I told it to do this every min. cause we never know when new data is entered into the system it retive data from. That all works fine but i am running into the problem of it just places the same data over and over again in the table until the information changes then it does the same with the new data. how do I prevent it from duplicating its information?
October 31, 2008 at 1:03 pm
You could add a job step that deletes the file after it is processed. There is a problem with that option as well, the job will most likly fail if there is no file to process.
October 31, 2008 at 1:09 pm
I am a bit confused as to what the problem is here. Can you explain a bit more on what your stored procedure is doing?
October 31, 2008 at 1:10 pm
but if i set up a step to delet after it process wouldn't always delet the files or is there a command I would use in the second step to have it check if its duplicate information
October 31, 2008 at 1:14 pm
my stored procedure pulls information from an as400 and puts it into the database for others to view.
October 31, 2008 at 1:18 pm
October 31, 2008 at 1:20 pm
this is the stored proc:
USE [BOMVerifier]
GO
/****** Object: StoredProcedure [dbo].[Get_BomInfo] Script Date: 10/31/2008 15:19:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[Get_BomInfo]
@PARNT CHAR(15),
@CUSNO NUMERIC(10,0),
@REVCD CHAR(3),
@CSEQN NUMERIC(3,0),
@child CHAR(15),
@DESCP CHAR(30),
@PSREQ NUMERIC(11,5),
@PSUNM CHAR(2),
@PSCCL CHAR(3),
@PSPCL CHAR(3),
@PSEDT varchar(50),
@PSDDT varchar(50),
@PSBFL char(1),
@SGVND numeric(6,0),
@PLNID char(6),
@ORDNO numeric(6,0),
@LineNumber numeric(3,0),
@CustPN varchar(50),
@PalletNum varchar(50),
@BulkContainer varchar(50),
@SingleBox varchar(50),
@Palletqty int,
@FullpalletBox varchar(50),
@PartialPalletBox varchar(50),
@Lid varchar(50),
@Divider varchar(50),
@Layer varchar(50),
@BandType varchar(50),
@BandPosition varchar(50),
@BandCount int,
@MaxSkidHeight varchar(50),
@MaxSkidWeight int,
@PalletLabelFormat varchar(50),
@BandMarkingRequired int,
@FoamPackRequired int,
@FoamCellPackRequired int,
@ShrinkWrapRequired int,
@ContainerSealMethod varchar(50),
@EmptyCellsOK int,
@SpecialInstructions varchar(2000),
@Active int,
@DateStamp smalldatetime,
@EntryBy varchar(200),
@PartNum Char(15)
as
insert into BOM_Packaging_Verification (PARNT,CUSNO,REVCD,CSEQN,CHILD,DESCP,PSREQ,PSUNM,PSCCL,PSPCL,PSEDT,PSDDT,PSBFL,SGVND,PLNID,
ORDNO,LineNumber,CustPN,PalletNum,BulkContainer,SingleBox,palletqty,FullPalletBox,
PartialPalletBox,Lid,Divider,Layer,BandType,BandPosition,BandCount,MaxSkidHeight,MaxSkidWeight,PalletLabelFormat,
BandMarkingRequired,FoamPackRequired,FoamCellPackRequired,ShrinkWrapRequired,
ContainerSealMethod,EmptyCellsOK,specialInstructions,Active,DateStamp,EntryBy,PartNum)
SELECT A.PARNT , A.CUSNO, A.REVCD, A.CSEQN, A.CHILD, A.DESCP, A.PSREQ, A.PSUNM, A.PSCCL, A.PSPCL, A.PSEDT, A.PSDDT, A.PSBFL, A.SGVND, A.PLNID,
A.ORDNO, A.LINE# AS LineNumber, p.CustPN, plt.PalletNum, pbc.BulkContainer, psb.SingleBox, p.palletqty, pfbx.fullpalletBox,
ppbx.PartialPalletBox, pl.Lid, pd.Divider, ply.Layer, pbt.BandType, pbp.BandPosition,p.BandCount, p.MaxSkidHeight, P.MaxSkidWeight, pplf.PalletLabelFormat,
CONVERT(INTEGER, P.BandMarkingRequired) as BandMarkingRequired, CONVERT(INTEGER, P.FoamPackRequired) as FoamPackRequired,
CONVERT(INTEGER, P.FoamCellPackRequired) as FoamCellPackRequired, CONVERT(INTEGER, P.ShrinkWrapRequired) as ShrinkWrapRequired,
pcsm.ContainerSealMethod , CONVERT(INTEGER, P.EmptyCellsOK) AS EmptyCellsOK, p.specialInstructions,
CONVERT(INTEGER, P.Active) as Active, p.DateStamp, p.EntryBy, P.PartNum
FROM OPENQUERY(AS400,
'select
PARNT, REVCD, CSEQN, CHILD,
DESCP, PSREQ, PSUNM, PSCCL,
PSPCL, CAST(PSEDT AS VARCHAR(10)) as PSEDT,CAST(PSDDT AS VARCHAR(10)) as PSDDT, PSBFL,
SGVND, PLNID, ORDNO, LINE#, CUSNO
FROM V92FILES.WKPS0426')
as a inner join shipinsp07.dbo.Packaging AS P on PARNT = P.PartNum
left outer join shipInsp07.dbo.PackagingContainerSealMethod as pcsm on p.containerSealMethodID = pcsm.ID
left outer join shipinsp07.dbo.PackagingPalletLabelFormat as pplf on P.PalletLabelFormatID = pplf.ID
left outer join shipinsp07.dbo.packagingBandPosition as pbp on p.BandPositionID = pbp.ID
left outer join shipinsp07.dbo.PackagingBandType as pbt on P.BandTypeID = pbt.ID
left outer join shipinsp07.dbo.packagingLayer as ply on P.LayerID = ply.ID
left outer join shipinsp07.dbo.PackagingDivider as pd on P.DividerID = pd.ID
left outer join shipinsp07.dbo.packagingLid as pl on P.LidID = pl.ID
left outer joinshipinsp07.dbo.PackagingPartialPalletBox as ppbx on p.PartialPalletBoxID = ppbx.ID
left outer join shipinsp07.dbo.PackagingFullPalletBox as pfbx on P.FullpalletBoxID = pfbx.ID
left outer join shipinsp07.dbo.PackagingSingleBox as psb on P.SingleBoxID = psb.ID
left outer join shipinsp07.dbo.PackagingBulkContainer as pbc on P.BulkContainerID = pbc.ID
left outer join shipinsp07.dbo.PackagingPalletNum as plt on P.PalletNumID = plt.ID
and this is what the agent job step is:
USE [BOMVerifier]
GO
DECLARE@return_value int
if exists EXEC @return_value = [dbo].[Get_BomInfo]
@PARNT = NULL,
@CUSNO = NULL,
@REVCD = NULL,
@CSEQN = NULL,
@child = NULL,
@DESCP = NULL,
@PSREQ = NULL,
@PSUNM = NULL,
@PSCCL = NULL,
@PSPCL = NULL,
@PSEDT = NULL,
@PSDDT = NULL,
@PSBFL = NULL,
@SGVND = NULL,
@PLNID = NULL,
@ORDNO = NULL,
@LineNumber = NULL,
@CustPN = NULL,
@PalletNum = NULL,
@BulkContainer = NULL,
@SingleBox = NULL,
@Palletqty = NULL,
@FullpalletBox = NULL,
@PartialPalletBox = NULL,
@Lid = NULL,
@Divider = NULL,
@Layer = NULL,
@BandType = NULL,
@BandPosition = NULL,
@BandCount = NULL,
@MaxSkidHeight = NULL,
@MaxSkidWeight = NULL,
@PalletLabelFormat = NULL,
@BandMarkingRequired = NULL,
@FoamPackRequired = NULL,
@FoamCellPackRequired = NULL,
@ShrinkWrapRequired = NULL,
@ContainerSealMethod = NULL,
@EmptyCellsOK = NULL,
@SpecialInstructions = NULL,
@Active = NULL,
@DateStamp = NULL,
@EntryBy = NULL,
@PartNum = NULL
SELECT'Return Value' = @return_value
GO
October 31, 2008 at 1:29 pm
Well, you need some way to make sure the data does not exist in the target table prior to inserting it. You could accomplish this by using an IF EXISTS or OUTER JOIN on your INSERT statement. This could prove to be a poor performer since you're joining to an external data source. You could create a temp or staging table to bring your AS400 data over into and then make your comparisons prior to the actual INSERT. Again, with this running every 1 min, this could be a poor performer as well.
I would suggest playing around with these 2 options (and the others that this post is sure to give you) and compare/contrast the impacts of each of them.
November 3, 2008 at 6:01 am
khaysmer (10/31/2008)
OK I have a database I am working on the I had to place a sql server agent on to execute the stored procedure on. I told it to do this every min. cause we never know when new data is entered into the system it retive data from. That all works fine but i am running into the problem of it just places the same data over and over again in the table until the information changes then it does the same with the new data. how do I prevent it from duplicating its information?
very simple. use flag column(s) in your table structure if the data is fetching by more users at a time and you can also use triggers on INSERT
kshitij kumar
kshitij@krayknot.com
www.krayknot.com
November 3, 2008 at 8:53 am
how do i set up flags in the table and how does this prevent replication of the data.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply