cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ashaikh Helper III
Helper III

Real Stream Dataset for AZURE SQL Server Database Query Using Powershell

Hello,

I am trying Real Stream Dataset reports using powershell. But my SQL Server Database is hosted in Azure. How would I connect it to azure SQL Database using powershell.

 

I have query to do it using local database but I am unable to connect it to AZURE SQL Database using same script.

$SqlServer = 'MySQLserverName';
$SqlDatabase = 'MyDatabaseName';
$user = "MyUsername";
$pwd = "MyPassword $connectionString = “Server=$SqlServer;uid=$user; pwd=$pwd;Database=$SqlDatabase;Integrated Security=False;” $SqlQuery = "SELECT * FROM table;"; $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 = "myEndPoint" #Fetch data and write out to files while ($SqlDataReader.Read()) { $payload = @{ "product" =$SqlDataReader['product'] "sales" =$SqlDataReader['sales'] "datetime" =$SqlDataReader['datetime'] } Invoke-RestMethod -Method Post -Uri "$endpoint" -Body (ConvertTo-Json @($payload)) } $SqlConnection.Close(); $SqlConnection.Dispose();

I also have a script to connect to Azure Database using Powershell. This is how I connect -

 

$params = @{

  'Database' = 'MyDatabase'

  'ServerInstance' =  'MyServerName'

  'Username' = 'MyUsername'

  'Password' = 'MyPassword'

  'OutputSqlErrors' = $true

  'Query' = 'SELECT * FROM table'

  }

$result = Invoke-Sqlcmd @params
1 REPLY 1
Highlighted
Community Support
Community Support

Re: Real Stream Dataset for AZURE SQL Server Database Query Using Powershell

@ashaikh,

 

This issue is more related to azure sql server, pleae refer to: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-powershell-samples.

 

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.