Stored_Procedure

[gbegger]

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

[Markus Lahr]

Hallo,

Formatierung von SQL in Beiträgen bitte beachten. Ausserdem deutet das eher auf einen MS SQL Server hin: Forum MS SQL Server und MSDE.

[hwoess]

Sieht aus, als würde er ProcessedFileNames nicht kennen (falsch geschrieben?). Probiere mal, gleich am Anfang vor dem 'create table #temp' gleich mal ein 'select * from ProcessedFileNames', dann kann man anhand der Fehler-Zeilennummer ja ganz einfach feststellen, ob mein Verdacht stimmt.

bye,
Helmut

[gbegger]

danke fur den hinweis,

leider hab ich es auch mit "'select * from ProcessedFileNames', " probiert und bekomme den selben fehler,


ich hab die frage an das Forum : MS SQL Server und MSDE. gestellt, vielen dank fur den hinweis,

hier der link Here