Azure SQL Powershell , how to read a data view

Question

I have a Azure SQL Database with a SQL View - that I want to view in PowerShell.

I have been looking and working with :

Read-SqlViewData -ServerInstance "xxx.database.windows.net" -DatabaseName "dbname" -ViewName "viewNAme" -TopN 2 -Credential $cred -SchemaName "dbo"

I have got the $cred by get-credential.

I am wondering if this is now for Azure SQL and it is for a SQL installation on a server, as I am getting an error:

ItemNotFoundException on the Server Instance.

Any help would be great as this is my first time getting sql azure data with PowerShell.

zinco · Accepted Answer · 2020-10-02 16:18:38Z

1

Honestly never used this command but I was able to use other Powershell command from the same module like "Invoke-Sqlcmd" against Azure SQL DB, therefore the command should be compatible with Azure SQL DB.

I used Invoke-Sqlcmd several time using dbname, servername, username, password and the query, maybe you can give a try, e.g:

$params = @{
    'Database' = ${DbName}
    'ServerInstance' = $ServerInstance.FullyQualifiedDomainName
    'Username' = ${UserDB}
    'Password' = ${dbPassword}
    'Query' = ${SqlQuery}
} 
$val = Invoke-Sqlcmd @params

Other option is to use SqlConnection like (auth with token in this case):

    $conn = New-Object System.Data.SqlClient.SQLConnection
    $conn.ConnectionString = "Data Source=$SQLSERVERNAME.database.windows.net;Initial Catalog=$DBNAMEe;Connect Timeout=30"
    $conn.AccessToken = $tokenAuth
    $conn.Open() 
    $conn
    $query = "YOUR QUERY"
    $command = New-Object -TypeName System.Data.SqlClient.SqlCommand($query, $conn)
    $Result = $command.ExecuteScalar()

answered Oct 2, 2020 at 16:18

zinco

393 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jason_Hough Over a year ago

thats amazing, although i have been given a view on the SQL to use and not really an SQL TSQL query...

Jason_Hough Over a year ago

if you look at the command i was using it is asking for a serverinstance but I cannot seem to figure out what the instance is on Azure SQL .

Jason_Hough Over a year ago

I have got it working with your help... cheers for that man !

Collectives™ on Stack Overflow

Azure SQL Powershell , how to read a data view

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related