Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.