SAP_Queries/PROC CRIAR_ARQUIVO_TEXTO.sql at master · MRGhidini/SAP_Queries · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
/****** Object:  StoredProcedure [dbo].[OKS_CriaTexto]    Script Date: 09/01/2016 14:16:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[OKS_CriaTexto]
	@Table as varchar(100),
	@DataIni DateTime = null,
	@DataFim as DateTime = null,
	@OrderBy varchar(255)= ''
as

Declare @Colunas as varchar(max),
		@Colunas2 as nvarchar(max) = '',
		@Cmd as varchar(8000),
		@Linhas as nvarchar(max),
		@ColName as varchar(200),
		@Where as varchar(255),
		@id_file_sist	int,
		@id_arqv int,
		@status int,
		@src varchar(255),
		@desc varchar(255),
		@Arq as varchar(255),
		@msg as varchar(30) ='',
		@Code as int

--Exec OKS_CriaTexto GA_TP_MESTRE_NFEN_MERC, '20130120', '20130921'

set @Where = ' Where '
Set @Colunas = 'SELECT '
set @Code = 1--(ISNULL((Select MAX(cast(code as int))+1 From [@GATPLOG]),1))


if @Table not in (select T0.Name From sys.views T0)
Begin
	set @src = ''
	set @desc = 'View Não existe'
	set @msg = 'Erro Tabela'
	goto Fim
End


/*************************************************************************/
/**********TODO -- VERIFICAR ONDE VAI SER CRIADO. ABAIXO EXEMPLOS*********/
/*************************************************************************/
--Select @Arq = cast(AttachPath as varchar(255)) +'\'+@Table+'.TXT' From OADP
--set @Arq = 'C:\'+@Table+replace(replace(replace(convert(varchar,GETDATE(),120),'-',''),' ',''),':','')+'.txt'
SET @Arq = '\\srvoksftp\FTPOKSER\'+@Table + '.txt';

/*cria objeto*/
declare xCur1 Cursor for
	select T1.Name from sys.views T0 inner join sys.columns T1 on T0.object_id = T1.object_id
	where T0.name = @Table And T1.Name not in ('oksFiltro')
Open xCur1
Fetch Next From xCur1 into @ColName
While @@FETCH_STATUS = 0
Begin
	Set @Colunas = @Colunas + '+['+ @ColName+']'
	Fetch Next From xCur1 into @ColName
End
Close xCur1
Deallocate xCur1

/*************************************************************************/
/*******TODO -- MUDAR NOME DO BANCO NA IMPLEMENTAÇÃO NO CLIENTE***********/
/*************************************************************************/
Set @Colunas = @Colunas + ' From SBONaanDanJain.dbo.' + @Table

--Set @Colunas2 = LEFT(@colunas2, LEN(@Colunas2) -1)
if @DataIni is not null
Begin
	set @Where = @Where + ' oksFiltro >= ' + char(39)+replace(CONVERT(varchar,@DataIni,102),'.','')+CHAR(39)
End

if @DataFim is not null
Begin
	if LEN(@Where) > 10 begin set @Where = @Where+' And ' End
	set @Where = @Where + ' oksFiltro <= '+ char(39)+replace(CONVERT(varchar,@DataFim,102),'.','')+CHAR(39)
End

if @DataFim is not null or @DataIni is not null
Begin
	set @Colunas = @Colunas + @Where
End
--Colocar Ordenação
Set @Colunas = @Colunas + ' ' + @OrderBy


--Liberar esta parte do código para verificar a query que sera executada
--print @Colunas
--set @msg = 'Erro'
--goto Fim


/*************************************************************************/
/****************TODO -- MUDAR NOME DO SERVIDOR E INSTANCIA***************/
/*************************************************************************/
/*Grava o resultado no arquivo de saida*/
SET @Cmd = 'BCP "'+ @Colunas+' " queryout '+@Arq+' -c -T -S SRVOKS03\SQLDESENV'
print @CMD
exec xp_cmdshell @Cmd


--Set @msg = 'Arquivo Criado'
Fim:
--print @msg
IF(LEN(@msg)>0)Begin Select @msg End