Streaming from SQL Server

Anonymous — Tue, 24 Sep 2019 15:53:09 GMT

When ai try run this script, it return error message.

$SqlServer = 'GRANITOAON';
$SqlDatabase = 'DWPago';

$SqlConnectionString = 'Data Source={0};Initial Catalog={1};Integrated Security=SSPI' -f $SqlServer, $SqlDatabase;
$SqlQuery = "select a.QuantidadeTransações as TransacoesMes, a.ValorTransacionado as ValorTransacoesMes, b.QuantidadeTransações as TransacoesDia, b.ValorTransacionado as ValorTransacoesDia, c.Data as Data, c.TransacoesUltimoMin as TransacoesMinuto, c.TempoProcessamentoBD as TempoProcessamentoBD, c.TempoProcessamentoSocket as TempoProcessamentoSocket, c.TempoProcessamentoHSM as TempoProcessamentoHSM from [vw_rt_transacoesmes] a, [vw_rt_transacoesdia] b, [vw_rt_processamentotransacao] c;";

$SqlCommand = New-Object -TypeName System.Data.SqlClient.SqlCommand;
$SqlCommand.CommandText = $SqlQuery;
$SqlConnection = New-Object -TypeName System.Data.SqlClient.SqlConnection -ArgumentList $SqlConnectionString;
$SqlCommand.Connection = $SqlConnection;

$SqlConnection.Open();
$SqlDataReader = $SqlCommand.ExecuteReader();

##you would find your own endpoint in the Power BI service
$endpoint = "https://api.powerbi.com/beta/0c4e7c53-5100-4c56-8aa0-9705f186a05c/datasets/75393a01-8326-41ab-a4aa-c5940578a6eb/rows?key=B7bc9GHRohDwzdPpb23E7YL89tNY%2BEu3Bg267F0InR37VA8lAolvQlT9G7CZVoWAfAtWqkIr9cFQKdYPzMGZCw%3D%3D"

#Fetch data and write out to files
while ($SqlDataReader.Read()) {
$payload = @{
"Transações Mês" =$SqlDataReader['TransacoesMes']
"Valor Transacionado Mês" =$SqlDataReader['ValorTransacoesMes']
"Transações Dia" =$SqlDataReader['TransacoesDia']
"Valor Transacionado Dia" =$SqlDataReader['ValorTransacoesDia']
"Data" =$SqlDataReader['Data']
"Transações por minuto" =$SqlDataReader['TransacoesMinuto']
"Tempo Médio Procesamento BD" =$SqlDataReader['TempoProcessamentoBD']
"Tempo Médio Processamento Socket" =$SqlDataReader['TempoProcessamentoSocket']
"Tempo Médio Processamento HSM" =$SqlDataReader['TempoProcessamentoHSM']
}
Invoke-RestMethod -Method Post -Uri $endpoint -Body (ConvertTo-Json @($payload))
}

$SqlConnection.Close();
$SqlConnection.Dispose();

Error

Invoke-RestMethod : The remote server returned an error: (404) Not Found.
At line:33 char:5
+ Invoke-RestMethod -Method Post -Uri $endpoint -Body (ConvertTo-Json @($paylo ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (System.Net.HttpWebRequest:HttpWebRequest) [Invoke-RestMethod], WebException
+ FullyQualifiedErrorId : WebCmdletWebResponseException,Microsoft.PowerShell.Commands.InvokeRestMethodCommand

topic Streaming from SQL Server in Developer

Streaming from SQL Server