hallo,
ich hab ein problem eine Procedure aufzurufen,
das ist mein Procedure:
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'."
es scheint das es der 'ProcessedFileNames' ist aber der name der file ist
"ShippingData_20100916_125908" so steht es auch in der Procedure,
kan mir jemand von euch hier bei helfen,
mfg
gregor
ich hab ein problem eine Procedure aufzurufen,
das ist mein Procedure:
Code:
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
fehler wie folgt:
"Msg 208, Level 16, State 1, Procedure SP_Import_DHL_Report, Line 86
Invalid object name 'ProcessedFileNames'."
es scheint das es der 'ProcessedFileNames' ist aber der name der file ist
"ShippingData_20100916_125908" so steht es auch in der Procedure,
kan mir jemand von euch hier bei helfen,
mfg
gregor
Comment