topic Re: Query from powershell to SQL in Developer

Query from powershell to SQL

Canale — Thu, 21 Jun 2018 22:55:41 GMT

Hello

My problem is next:

I need make a query to read a database from powershell to sqlserver and after that up to powerbi online

any ideas about that.

this is my code

$SQLServer = "server"
$SQLDBName = "prueba"
$uid ="remoto"
$pwd = " "
$query = "SELECT * FROM table WHERE Column = 'id'"

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True; User ID = $uid; Password = $pwd;"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $Query
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet

$endpoint = "https://api.powerbi.com/beta/1b682faf-ba6c-46c6-a1e7-74ebc6a55b1f/datasets/8cb9acd2-13a8-486d-93bc-1..."

$payload = @{
"FC210-2" = "This put de value I call from sql"
}
Invoke-RestMethod -Method Post -Uri "$endpoint" -Body (ConvertTo-Json @($payload))

From powershell to sql

Canale — Thu, 21 Jun 2018 03:53:56 GMT

Hello

My problem is next:

I need make a query to read a database from powershell to sqlserver and after that up to powerbi online

any ideas about that.

this is my code

$SQLServer = "server"
$SQLDBName = "prueba"
$uid ="remoto"
$pwd = " "
$query = "SELECT * FROM datos WHERE Column = 'id'"

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True; User ID = $uid; Password = $pwd;"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $Query
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet

$endpoint = "https://api.powerbi.com/beta/1b682faf-ba6c-46c6-a1e7-74ebc6a55b1f/datasets/8cb9acd2-13a8-486d-93bc-1649d90967a2/rows?key=zgbRrKKtFnoEqxkhzbu32WsYo3hoaUbsaLjWhj7R%2B16nHCbPs9X6363Jq8P9W9I3%2BdipVvG7QgA6nV%2FuU59EoA%3D%3D"

$payload = @{
"FC210-2" = "This put de value from sql"
}
Invoke-RestMethod -Method Post -Uri "$endpoint" -Body (ConvertTo-Json @($payload))

Re: Query from powershell to SQL

v-chuncz-msft — Sat, 23 Jun 2018 08:30:57 GMT

@Canale,

You may refer to the following post.

https://community.powerbi.com/t5/Developer/Stream-Dataset-for-local-SQL-Server-Database-Query/td-p/107540