hallo alle,
ich hab ein problem eine Procedure zuerstellen,
aber jedes mal krieg ich eine fehler meldung wenn ich es ausfuhre,
fehler wie folgt
"Msg 208, Level 16, State 1, Procedure SP_Import_DHL_Report, Line 86
Invalid object name 'ProcessedFileNames'."
das ist mein Procedure:
USE [MYDB]
GO
/****** Object: StoredProcedure [dbo].[SP_Import_Report] Script Date: 09/15/2010 15:04:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[SP_Import_Report] as
begin
-----Garbage collection table -----
create table #temp(
[SITEMS_ID] [varchar](50) NULL,
[SITEMS_IDENTCODE] [varchar](50) NULL,
[SITEMS_STORNO] [varchar](50) NULL,
[SITEMS_LABELTYP] [varchar](50) NULL,
[ABS_CUSTOMERDECL] [varchar](50) NULL,
[AbsHausNr] [varchar](50) NULL,
[AbsName1] [varchar](50) NULL,
[AbsName2] [varchar](50) NULL,
[ORT] [varchar](50) NULL,
[AbsPlz] [varchar](50) NULL,
[AbsStrasse] [varchar](50) NULL,
[SITEMS_DATE] [varchar](50) NULL,
[SITEMS_ABS_ID] [varchar](50) NULL,
[EmpKundenNr] [varchar](50) NULL,
[VerfahrenTyp] [varchar](50) NULL,
[SITEMS_READY_TO_SEND] [varchar](50) NULL,
[CircleNumber] [varchar](50) NULL,
[EmpName1] [varchar](50) NULL,
[EmpName2] [varchar](50) NULL,
[SITEMS_EMPBEM] [varchar](50) NULL,
[SITEMS_LEITCODE] [varchar](50) NULL,
[SITEMS_ABSBEM] [varchar](50) NULL,
[SITEMS_ORDERNUMBER] [varchar](50) NULL,
[KSTS_BEZEICHNUNG] [varchar](50) NULL,
[SITEMS_GEWICHT] [varchar](50) NULL,
[SITEMS_ZLEISTUNG] [varchar](50) NULL,
[SOWNER_CLOSEDATE] [varchar](50) NULL,
[SITEMS_NACHNAHME] [varchar](50) NULL,
[SITEMS_WERT] [varchar](50) NULL,
[SITEMS_ENTGELD] [varchar](50) NULL,
[EmpLandName] [varchar](50) NULL,
[EmpPlz] [varchar](50) NULL,
[SITEMS_PRODUKTCODE] [varchar](50) NULL,
[EmpOrt] [varchar](50) NULL,
[EmpStrasse] [varchar](50) NULL,
[SITEMS_PRODUKTNAME] [varchar](50) NULL,
[LeistungenText] [varchar](50) NULL,
[NNWAEHRUNG] [varchar](50) NULL,
[SITEMS_KOLLICOUNTER] [varchar](50) NULL,
[SITEMS_ZUSATZBETRAGNACHNAHME] [varchar](50) NULL,
[SITEMS_GEWICHT_MC] [varchar](50) NULL,
[SITEMS_VORAUSVERF] [varchar](50) NULL,
[EmpHausNr] [varchar](50) NULL,
[WertWaehrung] [varchar](50) NULL,
[SITEMS_STORNO_REASON] [varchar](50) NULL,
[TVWERTWAEHRUNG] [varchar](50) NULL,
[SITEMS_ZOLLZUSATZ] [varchar](50) NULL,
[EmpLandCode] [varchar](50) NULL,
[NKREIS_TYP_CN] [varchar](50) NULL,
[SITEMS_AWB] [varchar](50) NULL,
[SITEMS_TVWERT] [varchar](50) NULL,
[SITEMS_E_EMAIL] [varchar](50) NULL,
[SITEMS_E_GEBDATUM] [varchar](50) NULL,
[SITEMS_E_AUSWEISNR] [varchar](50) NULL,
[SITEMS_E_AUSWEISART] [varchar](50) NULL,
[SITEMS_E_AUSWEISBEH] [varchar](50) NULL,
[SITEMS_E_MINDESTALTER] [varchar](50) NULL,
[SITEMS_TAUSCHBOX] [varchar](50) NULL,
[SITEMS_ZLEISTUNG_RETOUR] [varchar](50) NULL,
[SITEMS_IDENTCODE_RETOUR] [varchar](50) NULL,
[TEILNAHME] [varchar](50) NULL,
[SITEMS_CREATOR] [varchar](50) NULL)
-----end garbage collection table-----
set nocount on
declare @path varchar(150)
set @path = 'D:\Manifest_ImportSQL\DHLHOL'
declare @Error bit
declare @CommandScript varchar(500)
-----file collection-----
create table #Files(path varchar(150),FileName Varchar(250),Depth int,Filetype varchar(10))
insert into #Files (filename,Depth,Filetype) exec xp_dirtree @path,1,1
delete from #Files where RIGHT(filename,3)<> 'txt'
-----end file collection-----
Declare @Filename varchar(150)
Declare @SQL varchar(2000)
update
#Files
set
path = @Path
delete from
#Files
where
path + '\' + FileName in (select path + '\' + FileName from ProcessedFileNames)
While (select COUNT(filename) from #Files) > 0
begin
set @Filename = (select top 1 filename from #Files order by Filename)
set @sql = '
bulk insert #temp
from ''' + @path + '\' + @Filename + ''' with
(codepage = ''acp'', Firstrow = 2, FIELDTERMINATOR = '';'', keepnulls, ROWTERMINATOR = ''\n'')'
--print (@sql)
begin try
set @Error = 0
exec (@sql)
end try
begin catch
set @Error = 1
end catch
if @Error = 0
begin
set nocount off
insert into ProcessedFileNames(Path,FileName) values (@path,@Filename)
insert into File_EOD
select
[SITEMS_ID]
,[SITEMS_IDENTCODE]
,[SITEMS_STORNO]
,[SITEMS_LABELTYP]
,[ABS_CUSTOMERDECL]
,[AbsHausNr]
,[AbsName1]
,[AbsName2]
,[ORT]
,[AbsPlz]
,[AbsStrasse]
,[SITEMS_DATE]
,[SITEMS_ABS_ID]
,[EmpKundenNr]
,[VerfahrenTyp]
,[SITEMS_READY_TO_SEND]
,[CircleNumber]
,[EmpName1]
,[EmpName2]
,[SITEMS_EMPBEM]
,[SITEMS_LEITCODE]
,[SITEMS_ABSBEM]
,[SITEMS_ORDERNUMBER]
,[KSTS_BEZEICHNUNG]
,[SITEMS_GEWICHT]
,[SITEMS_ZLEISTUNG]
,[SOWNER_CLOSEDATE]
,[SITEMS_NACHNAHME]
,[SITEMS_WERT]
,[SITEMS_ENTGELD]
,[EmpLandName]
,[EmpPlz]
,[SITEMS_PRODUKTCODE]
,[EmpOrt]
,[EmpStrasse]
,[SITEMS_PRODUKTNAME]
,[LeistungenText]
,[NNWAEHRUNG]
,[SITEMS_KOLLICOUNTER]
,[SITEMS_ZUSATZBETRAGNACHNAHME]
,[SITEMS_GEWICHT_MC]
,[SITEMS_VORAUSVERF]
,[EmpHausNr]
,[WertWaehrung]
,[SITEMS_STORNO_REASON]
,[TVWERTWAEHRUNG]
,[SITEMS_ZOLLZUSATZ]
,[EmpLandCode]
,[NKREIS_TYP_CN]
,[SITEMS_AWB]
,[SITEMS_TVWERT]
,[SITEMS_E_EMAIL]
,[SITEMS_E_GEBDATUM]
,[SITEMS_E_AUSWEISNR]
,[SITEMS_E_AUSWEISART]
,[SITEMS_E_AUSWEISBEH]
,[SITEMS_E_MINDESTALTER]
,[SITEMS_TAUSCHBOX]
,[SITEMS_ZLEISTUNG_RETOUR]
,[SITEMS_IDENTCODE_RETOUR]
,[TEILNAHME]
,[SITEMS_CREATOR]
from #temp
set nocount on
print 'File imported: "'+ @filename + '"'
set @CommandScript = 'Move "' + @path + '\' + @Filename + '" "' + @path + '\processed\' + @Filename + '"'
exec xp_cmdshell @commandscript
end
else
begin
print 'Error in: "'+ @filename + '"'
set @CommandScript = 'Move "' + @path + '\*' + @Filename + '" "' + @path + '\Error\"'
print @commandscript
exec xp_cmdshell @commandscript
end
delete from #temp
Delete from #Files where FileName = (select top 1 FileName from #Files order by FileName)
end
end
ich hab ein problem eine Procedure zuerstellen,
aber jedes mal krieg ich eine fehler meldung wenn ich es ausfuhre,
fehler wie folgt
"Msg 208, Level 16, State 1, Procedure SP_Import_DHL_Report, Line 86
Invalid object name 'ProcessedFileNames'."
das ist mein Procedure:
USE [MYDB]
GO
/****** Object: StoredProcedure [dbo].[SP_Import_Report] Script Date: 09/15/2010 15:04:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[SP_Import_Report] as
begin
-----Garbage collection table -----
create table #temp(
[SITEMS_ID] [varchar](50) NULL,
[SITEMS_IDENTCODE] [varchar](50) NULL,
[SITEMS_STORNO] [varchar](50) NULL,
[SITEMS_LABELTYP] [varchar](50) NULL,
[ABS_CUSTOMERDECL] [varchar](50) NULL,
[AbsHausNr] [varchar](50) NULL,
[AbsName1] [varchar](50) NULL,
[AbsName2] [varchar](50) NULL,
[ORT] [varchar](50) NULL,
[AbsPlz] [varchar](50) NULL,
[AbsStrasse] [varchar](50) NULL,
[SITEMS_DATE] [varchar](50) NULL,
[SITEMS_ABS_ID] [varchar](50) NULL,
[EmpKundenNr] [varchar](50) NULL,
[VerfahrenTyp] [varchar](50) NULL,
[SITEMS_READY_TO_SEND] [varchar](50) NULL,
[CircleNumber] [varchar](50) NULL,
[EmpName1] [varchar](50) NULL,
[EmpName2] [varchar](50) NULL,
[SITEMS_EMPBEM] [varchar](50) NULL,
[SITEMS_LEITCODE] [varchar](50) NULL,
[SITEMS_ABSBEM] [varchar](50) NULL,
[SITEMS_ORDERNUMBER] [varchar](50) NULL,
[KSTS_BEZEICHNUNG] [varchar](50) NULL,
[SITEMS_GEWICHT] [varchar](50) NULL,
[SITEMS_ZLEISTUNG] [varchar](50) NULL,
[SOWNER_CLOSEDATE] [varchar](50) NULL,
[SITEMS_NACHNAHME] [varchar](50) NULL,
[SITEMS_WERT] [varchar](50) NULL,
[SITEMS_ENTGELD] [varchar](50) NULL,
[EmpLandName] [varchar](50) NULL,
[EmpPlz] [varchar](50) NULL,
[SITEMS_PRODUKTCODE] [varchar](50) NULL,
[EmpOrt] [varchar](50) NULL,
[EmpStrasse] [varchar](50) NULL,
[SITEMS_PRODUKTNAME] [varchar](50) NULL,
[LeistungenText] [varchar](50) NULL,
[NNWAEHRUNG] [varchar](50) NULL,
[SITEMS_KOLLICOUNTER] [varchar](50) NULL,
[SITEMS_ZUSATZBETRAGNACHNAHME] [varchar](50) NULL,
[SITEMS_GEWICHT_MC] [varchar](50) NULL,
[SITEMS_VORAUSVERF] [varchar](50) NULL,
[EmpHausNr] [varchar](50) NULL,
[WertWaehrung] [varchar](50) NULL,
[SITEMS_STORNO_REASON] [varchar](50) NULL,
[TVWERTWAEHRUNG] [varchar](50) NULL,
[SITEMS_ZOLLZUSATZ] [varchar](50) NULL,
[EmpLandCode] [varchar](50) NULL,
[NKREIS_TYP_CN] [varchar](50) NULL,
[SITEMS_AWB] [varchar](50) NULL,
[SITEMS_TVWERT] [varchar](50) NULL,
[SITEMS_E_EMAIL] [varchar](50) NULL,
[SITEMS_E_GEBDATUM] [varchar](50) NULL,
[SITEMS_E_AUSWEISNR] [varchar](50) NULL,
[SITEMS_E_AUSWEISART] [varchar](50) NULL,
[SITEMS_E_AUSWEISBEH] [varchar](50) NULL,
[SITEMS_E_MINDESTALTER] [varchar](50) NULL,
[SITEMS_TAUSCHBOX] [varchar](50) NULL,
[SITEMS_ZLEISTUNG_RETOUR] [varchar](50) NULL,
[SITEMS_IDENTCODE_RETOUR] [varchar](50) NULL,
[TEILNAHME] [varchar](50) NULL,
[SITEMS_CREATOR] [varchar](50) NULL)
-----end garbage collection table-----
set nocount on
declare @path varchar(150)
set @path = 'D:\Manifest_ImportSQL\DHLHOL'
declare @Error bit
declare @CommandScript varchar(500)
-----file collection-----
create table #Files(path varchar(150),FileName Varchar(250),Depth int,Filetype varchar(10))
insert into #Files (filename,Depth,Filetype) exec xp_dirtree @path,1,1
delete from #Files where RIGHT(filename,3)<> 'txt'
-----end file collection-----
Declare @Filename varchar(150)
Declare @SQL varchar(2000)
update
#Files
set
path = @Path
delete from
#Files
where
path + '\' + FileName in (select path + '\' + FileName from ProcessedFileNames)
While (select COUNT(filename) from #Files) > 0
begin
set @Filename = (select top 1 filename from #Files order by Filename)
set @sql = '
bulk insert #temp
from ''' + @path + '\' + @Filename + ''' with
(codepage = ''acp'', Firstrow = 2, FIELDTERMINATOR = '';'', keepnulls, ROWTERMINATOR = ''\n'')'
--print (@sql)
begin try
set @Error = 0
exec (@sql)
end try
begin catch
set @Error = 1
end catch
if @Error = 0
begin
set nocount off
insert into ProcessedFileNames(Path,FileName) values (@path,@Filename)
insert into File_EOD
select
[SITEMS_ID]
,[SITEMS_IDENTCODE]
,[SITEMS_STORNO]
,[SITEMS_LABELTYP]
,[ABS_CUSTOMERDECL]
,[AbsHausNr]
,[AbsName1]
,[AbsName2]
,[ORT]
,[AbsPlz]
,[AbsStrasse]
,[SITEMS_DATE]
,[SITEMS_ABS_ID]
,[EmpKundenNr]
,[VerfahrenTyp]
,[SITEMS_READY_TO_SEND]
,[CircleNumber]
,[EmpName1]
,[EmpName2]
,[SITEMS_EMPBEM]
,[SITEMS_LEITCODE]
,[SITEMS_ABSBEM]
,[SITEMS_ORDERNUMBER]
,[KSTS_BEZEICHNUNG]
,[SITEMS_GEWICHT]
,[SITEMS_ZLEISTUNG]
,[SOWNER_CLOSEDATE]
,[SITEMS_NACHNAHME]
,[SITEMS_WERT]
,[SITEMS_ENTGELD]
,[EmpLandName]
,[EmpPlz]
,[SITEMS_PRODUKTCODE]
,[EmpOrt]
,[EmpStrasse]
,[SITEMS_PRODUKTNAME]
,[LeistungenText]
,[NNWAEHRUNG]
,[SITEMS_KOLLICOUNTER]
,[SITEMS_ZUSATZBETRAGNACHNAHME]
,[SITEMS_GEWICHT_MC]
,[SITEMS_VORAUSVERF]
,[EmpHausNr]
,[WertWaehrung]
,[SITEMS_STORNO_REASON]
,[TVWERTWAEHRUNG]
,[SITEMS_ZOLLZUSATZ]
,[EmpLandCode]
,[NKREIS_TYP_CN]
,[SITEMS_AWB]
,[SITEMS_TVWERT]
,[SITEMS_E_EMAIL]
,[SITEMS_E_GEBDATUM]
,[SITEMS_E_AUSWEISNR]
,[SITEMS_E_AUSWEISART]
,[SITEMS_E_AUSWEISBEH]
,[SITEMS_E_MINDESTALTER]
,[SITEMS_TAUSCHBOX]
,[SITEMS_ZLEISTUNG_RETOUR]
,[SITEMS_IDENTCODE_RETOUR]
,[TEILNAHME]
,[SITEMS_CREATOR]
from #temp
set nocount on
print 'File imported: "'+ @filename + '"'
set @CommandScript = 'Move "' + @path + '\' + @Filename + '" "' + @path + '\processed\' + @Filename + '"'
exec xp_cmdshell @commandscript
end
else
begin
print 'Error in: "'+ @filename + '"'
set @CommandScript = 'Move "' + @path + '\*' + @Filename + '" "' + @path + '\Error\"'
print @commandscript
exec xp_cmdshell @commandscript
end
delete from #temp
Delete from #Files where FileName = (select top 1 FileName from #Files order by FileName)
end
end
Comment