Execute SQL script in an Azure SQL Data Warehouse

Question

I'm basically trying to execute a SQL script that inserts around 50 views into an Azure Data Warehouse using a Powershell Script. But for some reason doesn't like the syntax that I'm using.

For example:

CREATE VIEW XX.FirstView 
AS 
     SELECT bookings.Activity 
     FROM XX.FirstTable bookings
GO

CREATE VIEW XX.SecondView 
AS 
     SELECT books.ID 
     FROM XX.SecondTable books

If I run it directly in the SQL Server Data warehouse seems to work fine but when running it from Powershell it complains about a syntax error.

There is any SQL syntax that I have to add/modify which I'm not considering?

Syntax Error

PowerShell Script:

function Invoke-SQLDestination {
param([string] $sqlCommand = "")
    $sqlCommand.ToString()
    $connectionStringDestination = "XXXXXXXX"

    $connection = new-object system.data.SqlClient.SQLConnection($connectionStringDestination)
    $command = new-object system.data.sqlclient.sqlcommand($sqlCommand,$connection)
    $connection.Open()

    $adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command
    $dataset = New-Object System.Data.DataSet

    $adapter.Fill($dataSet) 

    $connection.Close()
    $dataSet.Tables
}


    $sqlscript = Get-Content ./SqlViewCreate.sql | Out-String
    Invoke-SQLDestination $sqlscript

Thanks!

Possible duplicate of [How to execute .sql file using powershell?](https://stackoverflow.com/questions/10894688/how-to-execute-sql-file-using-powershell) — , Nov 20 '18 at 18:03
@GiuseppeAdamo the problem is `Go` is not a sqlcommand and it's a separator used for a batch helper. [Find the solution here](https://www.robinosborne.co.uk/2014/10/13/getting-past-powershell-sqls-incorrect-syntax-near-go-message/) — Jayendran, Nov 21 '18 at 01:12
You can use SMO to execute batch scripts (it's kind of headless management studio). https://learn-powershell.net/2013/09/27/exploring-sql-databases-using-sql-server-management-objects-smo-and-powershell/ — Mike Twc, Nov 21 '18 at 04:21

score 1 · Accepted Answer · answered Nov 21 '18 at 08:34

The error is related to "go" in the sql script, which cannot be recognized by the powershell scripts you used.

You can make a little changes to your powershell, when it reads "go", execute the above sql scripts.

Code like below:

function Invoke-SQLDestination {
param([string] $sqlCommand = "")
    #$sqlCommand.ToString()

    $commandTxt = @(Get-Content -Path $sqlCommand)
    foreach($txt in $commandTxt)
    {
    if($txt -ne "Go")
    {
    $SQLPacket += $txt +"`n"
    }
    else
    {
    $connectionStringDestination = "xxxx"
    Write-Host $SQLPacket
    $connection = new-object system.data.SqlClient.SQLConnection($connectionStringDestination)
    $command = new-object system.data.sqlclient.sqlcommand($SQLPacket,$connection)
    $connection.Open()

    $adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command
    $dataset = New-Object System.Data.DataSet

    $adapter.Fill($dataSet) 

    $connection.Close()
    $dataSet.Tables

    $SQLPacket =""

    }
    }
}

    # here pass the sql file path
    $sqlscript = "D:\azure sql\test.sql"
    Invoke-SQLDestination $sqlscript

The following is my sql file:

create view v1
as
select name from student
go

create view v2
as
select name from student
go

create view v3
as
select name from student
go

The test result:

Execute SQL script in an Azure SQL Data Warehouse

1 Answers1