December 14, 2005 at 11:03 am
I am having a weird error with this stored procedure. I got this code from an associate who claims that this executes on his system with no errors. When executed on my system I get the following errors:
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'WeekNumber'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'Movement'.
The weird thing is that all of the correct data gets inserted into the tables. I am pretty new to the sql world and this one is over my head. Anyone have a suggestion? I am using SQL Server 8.00.818 - SP3 (Standard Edition). Here is the code:
if object_id('dbo.IKBFinFromFile') is not null
drop proc dbo.IKBFinFromFile
go
create procedure dbo.IKBFinFromFile
@FinFileName varchar(500) = null -- The name of the 'financials' input file
as
declare @InputFile varchar(500)
declare @sql nvarchar(4000), @parm nvarchar(1000), @ret int
declare @UPCLen int
set nocount on
------------------------------------------------------------------------------------------------------------------------
-- set @InputFile value
------------------------------------------------------------------------------------------------------------------------
if (@FinFileName is not null) begin
set @InputFile = @FinFileName
end else begin
set @InputFile = 'C:\DATA\IKBFin.CSV'
end
------------------------------------------------------------------------------------------------------------------------
-- set the actual length for UPCs
------------------------------------------------------------------------------------------------------------------------
set @UPCLen = 15
------------------------------------------------------------------------------------------------------------------------
-- create IKBFinTmp table
------------------------------------------------------------------------------------------------------------------------
set @sql = N'if object_id(''.dbo.IKBFinTmp'') is not null drop table .dbo.IKBFinTmp'
exec sp_executesql @sql
if (@@error <> 0) begin
return -1
end
set @sql = N'create table dbo.IKBFinTmp ( '
set @sql = @sql + 'StoreNumberStr varchar(250) null, '
set @sql = @sql + 'UPCStr varchar(250) null, '
set @sql = @sql + 'WeekNumberStr varchar(250) null, '
set @sql = @sql + 'MovementStr varchar(250) null, '
set @sql = @sql + 'CostStr varchar(250) null, '
set @sql = @sql + 'RetailStr varchar(250) null, '
set @sql = @sql + 'ReportCodeStr varchar(250) null, '
set @sql = @sql + 'PriceTypeStr varchar(250) null, '
set @sql = @sql + 'WeekEndDateStr varchar(250) null)'
--print @sql
--raiserror('',0,1) with nowait -- causes print statement to execute immediately
exec sp_executesql @sql
if (@@error <> 0) begin
return -1
end
------------------------------------------------------------------------------------------------------------------------
-- bulk insert data from the input file to the IKBFinTmp table
------------------------------------------------------------------------------------------------------------------------
set @sql = N'bulk insert dbo.IKBFinTmp '
set @sql = @sql + 'FROM ''' + @InputFile + ''''
exec sp_executesql @sql
if (@@error <> 0) begin
return -1
end
------------------------------------------------------------------------------------------------------------------------
-- create IKBFinStg table
------------------------------------------------------------------------------------------------------------------------
set @sql = N'if object_id(''.dbo.IKBFinStg'') is not null drop table .dbo.IKBFinStg'
exec sp_executesql @sql
set @sql = N'create table dbo.IKBFINSTG ( '
set @sql = @sql + 'StoreNumber int not null, '
set @sql = @sql + 'UPC varchar(16) not null, '
set @sql = @sql + 'WeekNumber int not null, '
set @sql = @sql + 'Movement float null, '
set @sql = @sql + 'Cost float null, '
set @sql = @sql + 'Retail float null, '
set @sql = @sql + 'ReportCode int null, '
set @sql = @sql + 'PriceType int null, '
set @sql = @sql + 'WeekEndDate float null)'
--print @sql
--raiserror('',0,1) with nowait -- causes print statement to execute immediately
exec sp_executesql @sql
if (@@error <> 0) begin
return -1
end
------------------------------------------------------------------------------------------------------------------------
-- load / transform data from the IKBFinTmp table to the IKBFinStg table
------------------------------------------------------------------------------------------------------------------------
set @sql = N'insert dbo.IKBFinStg ('
set @sql = @sql + 'StoreNumber, '
set @sql = @sql + 'WeekNumber, '
set @sql = @sql + 'Movement, '
set @sql = @sql + 'ReportCode, '
set @sql = @sql + 'PriceType, '
set @sql = @sql + 'WeekEndDate) '
set @sql = @sql + 'cast(rtrim(StoreNumberStr) as int), '
set @sql = @sql + 'substring(UPCStr,2,@UPCLenParm), '
set @sql = @sql + 'cast(rtrim(WeekNumberStr) as int), '
set @sql = @sql + 'cast(isnull(rtrim(MovementStr),null) as float), '
set @sql = @sql + 'cast(isnull(rtrim(CostStr),null) as float), '
set @sql = @sql + 'cast(isnull(rtrim(RetailStr),null) as float), '
set @sql = @sql + 'cast(isnull(rtrim(ReportCodeStr),null) as int), '
set @sql = @sql + 'cast(isnull(rtrim(PriceTypeStr),null) as int), '
set @sql = @sql + 'cast(isnull(rtrim(WeekEndDateStr),null) as float) '
set @sql = @sql + 'from dbo.IKBFinTmp '
set @sql = @sql + 'where isnumeric(rtrim(StoreNumberStr)) = 1 '
set @sql = @sql + 'and isnumeric(substring(UPCStr,2,@UPCLenParm)) = 1 '
set @sql = @sql + 'and isnumeric(rtrim(WeekNumberStr)) = 1 '
set @sql = @sql + 'and isnumeric(rtrim(WeekEndDateStr)) = 1'
set @parm = N'@UPCLenParm int'
print @sql
raiserror('',0,1) with nowait -- causes print statement to execute immediately
exec sp_executesql @sql, @parm, @UPCLen
if (@@error <> 0) begin
return -1
end
------------------------------------------------------------------------------------------------------------------------
-- add a clustered index to the staging staging table
------------------------------------------------------------------------------------------------------------------------
set @sql = N'CREATE CLUSTERED INDEX idxc_IKBFinStg ON dbo.IKBFinStg (StoreNumber, UPC, WeekNumber) WITH PAD_INDEX, FILLFACTOR = 98'
exec sp_executesql @sql
------------------------------------------------------------------------------------------------------------------------
-- update existing records in the FinancialCSV table from the staging table
------------------------------------------------------------------------------------------------------------------------
set @sql = N'update FIN '
set @sql = @sql + 'set Movement = STG.Movement, '
set @sql = @sql + 'Cost = STG.Cost, '
set @sql = @sql + 'Retail = STG.Retail, '
set @sql = @sql + 'ReportCode = STG.ReportCode, '
set @sql = @sql + 'PriceType = STG.PriceType, '
set @sql = @sql + 'WeekEndDate = STG.WeekEndDate '
set @sql = @sql + 'from dbo.FinancialCSV FIN inner join dbo.IKBFinStg STG '
set @sql = @sql + 'on FIN.StoreNumber = STG.StoreNumber and FIN.UPC = STG.UPC and FIN.WeekNumber = STG.WeekNumber'
--print @sql
--raiserror('',0,1) with nowait -- causes print statement to execute immediately
exec sp_executesql @sql
------------------------------------------------------------------------------------------------------------------------
-- import new records from the staging table to the FinancialCSV table
------------------------------------------------------------------------------------------------------------------------
set @sql = N'insert dbo.FinancialCSV (StoreNumber, UPC, Period, Wk1Movement, Wk1UnitCost, Wk1UnitPrice,'
set @sql = @sql + 'Wk1ReportCode, Wk1PriceType, Wk1WeekEndDate) '
set @sql = @sql + 'select STG.StoreNumber, STG.UPC, STG.WeekNumber, STG.Movement, STG.Cost, STG.Retail, STG.ReportCode, '
set @sql = @sql + 'STG.PriceType, STG.WeekEndDate from dbo.IKBFinStg STG left outer join dbo.FinancialCSV FIN '
set @sql = @sql + 'on STG.StoreNumber = FIN.StoreNumber and STG.UPC = FIN.UPC and STG.WeekNumber = FIN.Period '
set @sql = @sql + 'where FIN.StoreNumber is null'
--print @sql
--raiserror('',0,1) with nowait -- causes print statement to execute immediately
exec sp_executesql @sql
return(0)
go
December 14, 2005 at 12:28 pm
If you execute the queries step by step, when does it error out?
You can split the stored procedure later in smaller parts.
--step 1
DECLARE @FinFileName varchar(500)
SET FinFileName ='myfile' -- The name of the 'financials' input file
declare @InputFile varchar(500)
declare @sql nvarchar(4000), @parm nvarchar(1000), @ret int
declare @UPCLen int
set nocount on
------------------------------------------------------------------------------------------------------------------------
-- set @InputFile value
------------------------------------------------------------------------------------------------------------------------
if (@FinFileName is not null) begin
set @InputFile = @FinFileName
end else begin
set @InputFile = 'C:\DATA\IKBFin.CSV'
end
------------------------------------------------------------------------------------------------------------------------
-- set the actual length for UPCs
------------------------------------------------------------------------------------------------------------------------
set @UPCLen = 15
------------------------------------------------------------------------------------------------------------------------
-- create IKBFinTmp table
------------------------------------------------------------------------------------------------------------------------
set @sql = N'if object_id(''.dbo.IKBFinTmp'') is not null drop table .dbo.IKBFinTmp'
exec sp_executesql @sql
if (@@error <> 0) begin
return -1
end
set @sql = N'create table dbo.IKBFinTmp ( '
set @sql = @sql + 'StoreNumberStr varchar(250) null, '
set @sql = @sql + 'UPCStr varchar(250) null, '
set @sql = @sql + 'WeekNumberStr varchar(250) null, '
set @sql = @sql + 'MovementStr varchar(250) null, '
set @sql = @sql + 'CostStr varchar(250) null, '
set @sql = @sql + 'RetailStr varchar(250) null, '
set @sql = @sql + 'ReportCodeStr varchar(250) null, '
set @sql = @sql + 'PriceTypeStr varchar(250) null, '
set @sql = @sql + 'WeekEndDateStr varchar(250) null)'
--print @sql
--raiserror('',0,1) with nowait -- causes print statement to execute immediately
exec sp_executesql @sql --creates table
if (@@error <> 0) begin
return -1
end
....
December 14, 2005 at 12:43 pm
Why all the dynamic SQL ?
Are you sure of database context. I see a bunch of 2-part naming on objects, without a database context, so is it possible you're executing this in the wrong database ?
December 14, 2005 at 1:24 pm
My associate has a copy of my database and wrote it specifically for my environment. I am certain of the database and I have checked the context several times. After some research I thought it might be something to do with a quouted identifier. I added this to the sql (turning the identifier off) but got the same results. My associate is rather dumbfounded by the whole thing.
December 14, 2005 at 1:55 pm
It comes up with the error when trying to do an insert on the dbo.IKBFinStg table. The procedure however completes inserting the final data into the dbo.WinCoFinancialCSV table. After the proc is executed The csv and all 3 tables contain the same amount of records and appears as if everything had processed correctly. It is a complete mystery. The correct data was inserted into the columns that were deemed "invalid". I will tear it down into smaller parts as you have suggested.
Thank You.
December 14, 2005 at 2:05 pm
Have you verified that tables FIN and FinancialCSV contain those columns ?
Also, take a close look at this:
set @sql = N'if object_id(''.dbo.IKBFinStg'') is not null drop table .dbo.IKBFinStg'
exec sp_executesql @sql
What is the leading period on the "dbo" supposed to be doing ? is it possible you're not recreating the table as epected because of this bug in the Drop Table code ?
And again, the final UPDATE and INSERT statements don't need to be dynamic SQL - if they were normal SQL in the sproc, you'd be able to catch things like this at compile time versus at runtime
December 14, 2005 at 4:44 pm
I am a complete idiot. The tables do contain those columns but part of the code is referencing different columns in the --update existing records in the financialcsv table from the staging table and the --import new records from the staging table to the financial table. I have corrected the code but I am still unsure of how the data was still being inserted into the table correctly. I will look into re-writing this code so that it is not dynamic. I appreciate your guidance. It is all starting to make sense now. I have also corrected the leading period issue. Not sure how that got there in the first place. Thanks again.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply