August 24, 2005 at 5:05 am
Hi All
I am trying to Alter a table in a Dts package then update or populate the column with a default text annotation, However when i try and update it it keeps sending back the column does not exist.
Could this be a problem because the table is empty or is there something im missing
When i parse the query or run each line separately in query analyzer it goes through fine only when i execute the sql task it will fail with an Error.
Any Help will be appreciated
Here is a Snippet!
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[VesselDupCheckBunk]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[VesselDupCheckBunk]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[VesselDupCheckBunk]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[VesselDupCheckBunk]
GO
SELECT ponumber,PORLREV, COUNT(*) AS 'Occurrences' INTO VesselDupCheckBunk
FROM finalPolinesBunk
group by ponumber, PORLREV
having count(*) >=1
ORDER BY Ponumber
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[vesselduplicateBunk]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[vesselduplicateBunk]
GO
SELECT dbo.VesselDupCheckBunk.ponumber, dbo.VesselDupCheckBunk.Occurrences, dbo.FinalpoLinesBunk.PORHSEQ, dbo.FinalpoLinesBunk.PORLREV,
dbo.FinalpoLinesBunk.ITEMNO, dbo.FinalpoLinesBunk.LOCATION, dbo.FinalpoLinesBunk.ORDERUNIT, dbo.FinalpoLinesBunk.ITEMDESC, dbo.FinalpoLinesBunk.OQORDERED,
dbo.FinalpoLinesBunk.OQOUTSTAND, dbo.FinalpoLinesBunk.UNITCOST, dbo.FinalpoLinesBunk.TAXCLASS1, dbo.FinalpoLinesBunk.TAXRATE1,
dbo.FinalpoLinesBunk.TAXINCLUD1, dbo.FinalpoLinesBunk.TAXAMOUNT1, dbo.FinalpoLinesBunk.GLNONSTKCR, dbo.FinalpoLinesBunk.[DATE],
dbo.FinalpoLinesBunk.vDCode INTO vesselduplicateBunk
FROM dbo.VesselDupCheckBunk INNER JOIN
dbo.FinalpoLinesBunk ON dbo.VesselDupCheckBunk.PORLREV = dbo.FinalpoLinesBunk.PORLREV AND
dbo.VesselDupCheckBunk.ponumber = dbo.FinalpoLinesBunk.PONUMBER
WHERE (dbo.VesselDupCheckBunk.Occurrences >= 2) AND (dbo.VesselDupCheckBunk.PORLREV = 1)
ORDER BY dbo.FinalpoLinesBunk.PORHSEQ
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ErrorDuplicatesBunk]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ErrorDuplicatesBunk]
GO
SELECT * INTO ErrorDuplicatesBunk from FinalPoLinesBunk
Where ponumber In (SELECT ponumber from vesselduplicateBunk)
--Start Error File
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ErrorLinesBunk]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ErrorLinesBunk]
GO
SELECT PORHSEQ, PORLREV, ITEMNO, LOCATION, ITEMDESC, ORDERUNIT, OQORDERED, OQOUTSTAND, UNITCOST, TAXCLASS1, TAXRATE1, TAXINCLUD1,
TAXAMOUNT1, GLNONSTKCR, [DATE], PONUMBER, vDCode INTO ErrorLinesBunk
FROM dbo.ErrorDuplicatesBunk
-- Insert Error Placements Into Error File
ALTER TABLE ErrorLinesBunk
ADD Code varchar(50),Error1 varchar(50), Error2 varchar(50), Error3 varchar(50)
UPDATE ErrorLinesBunk
SET Error1 = 'Duplicate'
August 24, 2005 at 6:09 am
The top three lines appear to be duplicated for some reason - not that it matters.
To make this more manageable, I suggest that you implement this as a stored procedure (lose the GOs - see below) and then merely execute the SP from within DTS.
Debugging will be easier too - as you can step through the stored proc line by line.
Rather than DROPping and then doing SELECT INTO to recreate the tables, you might find that the whole thing works better and faster if you just TRUNCATE the tables and then do INSERT INTO - more typing, but preferable, in my opinion, as you know exactly what's going on and you won't need GO all over the place.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply