Announcement

Collapse
No announcement yet.

import_über_Stored_Procedure

Collapse
X
Collapse
  • Page of 1
  • Filter
  • Time
  • Show
Clear All
new posts
Previous template Next
  • #1

    import_über_Stored_Procedure

    hallo,
    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
    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
    Tags: import, stored_procedure
  • #2
    Es muss doch aber irgendwo eine Tabelle ProcessedFileNames geben, denn mit

    Code:
    select path + '\' + FileName from ProcessedFileNames
    versuchst Du doch zwei Felder daraus auszulesen...

    Comment

    • #3
      ProcessedFileNames is doch der aufbau der File von der er doch importieren soll,

      das steht doch auch hier in der code

      Code:
      begin
set nocount off
insert into ProcessedFileNames(Path,FileName) values (@path,@Filename)
insert into File_EOD
select 
[SITEMS_ID]
,[SITEMS_IDENTCODE] u.s.w...

      Comment

      Previous template Next
      Working...
      X