Real Stream Dataset for AZURE SQL Server Database Query Using Powershell

[ashaikh]

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

[v-yuta-msft]

@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.