Podemos através do BCP ou mesmo do Microsoft SQL Agent exportar o resultado da execução de uma proc através de uma task mas essas ferramentas tem um formato próprio que nem sempre é o que desejamos.
Um dos detalhes que mais pegam nessas ferramentas automaticas é que caso a conexão caia ou algum problema derrube a comunicação entre a máquina fonte do arquivo e a receptora o arquivo permancerá na máquina receptora truncado. Não é informado nenhum erro porque o servidor ftp é um serviço de rede que roda abaixo da camada de aplicação, por isso a queda não é notada pelo sistema chamador.
Desejamos exportar os dados da proc com as seguintes carcterísticas:
• 1 - Uma aplicação tipo console. O motivo é que não é necessário qualquer intervenção do usuário para executar a tarefa e podemos agendar o agendamento de tarefas do windows para executar o programa sempre que queremos atualizar esse arquivo de dados.
• 2 - Sobrescrever o arquivo antigo. O arquivo antigo será sobrescrito pelo resultado da nova execução do programa. Isto evita um monte de arquivos que devem ser apagados de tempo em tempo. Este item não é interessante para quem precisa de um histórico de arquivos enviados e para corrigir este problema basta colocar o horario de geração do arquivo no seu nome...mas precisa de um outro processo para apagar os aruivos antigos.
• 3 - Deve exportar os dados de qualquer proc não importando o resultado dela. Ou seja, se o campo for numérico ou texto, se tiver uma coluna ou 100 colunas... deve funcionar perfeitamente com qualquer formato de resposta da proc.
• 4 - Utilizar como separador o caractere |. Como vírgula, ponto, ponto e vírgula, dois pontos podem vir em alguns campos para evitar erro de separação dos mesmos futuramente usar como divisor um caractere que não é usado nunca, o |.
• 5 - A primeira linha deve conter o nome das colunas dos campos retornados pela pesquisa.
• 6 - As linhas de dados dos arquivos deve ter o mesmo número de colunas do cabeçalho acima (item 3), nem mais nem menos uma coluna sequer.
• 7 -Na última linha deverá conter um texto que será a contagem de quantas linhas de dados foram retornadas e gravadas no arquivo.
O item 7 é um item de segurança que tem como finalidade descobrir se o arquivo foi truncado na transmissão do mesmo. Se o número na última linha não vier ou vier diferente do número de linhas do arquivo isto significará que o arquivo está corrompido e o ftp do aruquivo deve ser feito novamente.
Imports System
Imports System.Data
Imports Microsoft.Data.SqlClient
Imports System.IO
''' <summary>
''' Inserir o Microsof.SQL.DataClient no menu Ferramentas, Gerenciador de pacotes do Nuget, Procurar
''' </summary>
Module Program
Public cnstr As String = "" 'string de conexão com o banco de dados
Public cncn As New SqlConnection 'conexão com o banco de dados
Public Erro As String = "" 'troca de mensagens de erro
''' <summary>
''' Define o string de conexão com o banco de dados que dependente da url de acesso do site
''' </summary>
Public Sub AbreConexao()
Erro = ""
Try
If cncn.ConnectionString = "" Then
cnstr = "Server=srvx;Database=bdx;User Id=userx;Password=senhax;TrustServerCertificate=True"
cncn.ConnectionString = cnstr
cncn.Open()
Return
End If
Catch ex As SqlException
Erro = ex.Message
End Try
End Sub
''' <summary>
''' Faz a pesquisa e retorna um DataSet
''' </summary>
Public Function MS_SQL_SRV_DS(Query As String) As DataSet
Erro = ""
Try
AbreConexao()
Dim cmd = New SqlCommand(Query, cncn)
Dim MS_SQL_SqlDtAdapter = New SqlDataAdapter(cmd)
Dim DS As New DataSet()
MS_SQL_SqlDtAdapter.Fill(DS)
Return DS
Catch ex As Exception
Erro = ex.Message
Return Nothing
End Try
End Function
''' <summary>
''' Objetivo deste programa...exportar dados de uma proc com o mínimo código possível
''' </summary>
''' <param name="args"></param>
Sub Main(args As String())
Dim sql As String
Dim ds As DataSet
'Dim nomarq = "D:\Pastax\Arquivox.txt"
Dim file As New System.IO.StreamWriter(nomarq)
Dim a As String 'texto
Dim b As Integer 'colunas
Dim c As Integer 'linhas
Dim d As Integer 'contador de linhas
d = 0
sql = "EXEC PROCX"
ds = MS_SQL_SRV_DS(sql)
If Erro <> "" Then
Console.WriteLine("Falha na conexão servidor SQL")
file.WriteLine(Erro)
file.Close()
Exit Sub
End If
Console.WriteLine("Conexão com o servidor SQL feita com SUCESSO")
'salvando os nomes das colunas
a = ""
For b = 0 To ds.Tables(0).Columns.Count - 1
a += ds.Tables(0).Columns(b).ColumnName + "|"
Next
file.WriteLine(a)
d += 1
Console.WriteLine("Nome das Colunas Salvo")
For c = 0 To ds.Tables(0).Rows.Count - 1
a = ""
For b = 0 To ds.Tables(0).Columns.Count - 1
If IsNumeric(ds.Tables(0).Rows(c).Item(b)) Then
a += CStr(ds.Tables(0).Rows(c).Item(b)) + "|"
Else
a += ds.Tables(0).Rows(c).Item(b) + "|"
End If
Next
file.WriteLine(a)
d += 1
Next
Console.WriteLine("Dados das Colunas Salvo")
'gravando o número de regs
d += 1
file.WriteLine(CStr(d))
file.Close()
Console.WriteLine("Conexão fechada..fim do processo")
End
End Sub
End Module
As linhas em negrito devem ser modificadas para apontarem para seu servidor, banco de dados, usuário, senha, pasta a salvar o arquivo e o nome da proc que deseja executar.