Azure SQL Powershell , how to read a data view

user_3437628 0
Viewed 146

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.

azure powershell sql-server sql
1 Answers

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()
user_56082650
Related