Ytukyg

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!

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 sqltest.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: