Execute a .SQL file in Powershell without having SQL Server installed?

Question

I need to provide a powershell script that runs a bunch of steps to install a custom solution. One of these steps is create a SQL Server database and execute a .sql file to generate the schema.

The thing is that I need a way of doing it in any machine, whether is has SQL server locally installed or not (it usually is on another network machine). Also I need to avoid using 3rd party modules since I cannot force clients to install them.

Is there a way of doing this?

UPDATE: The server running the script will have .NET 4.5 installed since SharePoint 2013 will be installed.

Answer 1

Something like this:

#Variables
$sqlServer = "."
$sqlDBName = "master"
$sqlQuery = "CREATE DATABASE test"

# Create the connection string
$sqlConnectionString ="Server = $sqlServer; Database = $sqlDBName; Integrated Security = True"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = $sqlConnectionString

#Create the SQL Command object
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection

#Open SQL connection
$SqlCmd.Connection.Open()

#Execute the Query
$ReturnValue = $SqlCmd.ExecuteNonQuery()

--Edit

Technically GO is not a T-SQL command. It's the end-of-batch marker recognised by Microsoft SQL tools (Management Studio, isql, osql). So that is why when you execute the statement directly, it isn't recognized. The 'real' solution is to either eliminate the GO statements, or split up the statements into separate batch processes (either physically or string.split("GO"))

Or the alternate using SQL Management Object which theoretically can handle "Go" statements (SqlCommand() ExecuteNonQuery() truncates command text):

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
$SMOServer = New-Object ('Microsoft.SqlServer.Management.Smo.Server')
$SMOServer.ConnectionContext.ConnectionString = $sqlConnectionString
$SMOServer.ConnectionContext.ExecuteNonQuery($sqlQuery)

--Edit 2

Or, if you can't use SQL Management Object, and you have "GO" Statements, something quick and dirty is that you can split the string and use code like this:

#Variables
$sqlServer = "."
$sqlDBName = "master"
$sqlQuery = "CREATE DATABASE test; GO; CREATE DATABASE test2; GO;"

# Create the connection string
$sqlConnectionString ="Server = $sqlServer; Database = $sqlDBName; Integrated Security = True"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = $sqlConnectionString

$sqlQuery -split "GO;" | ForEach-Object{
    if($_ -ne "")
    {
        #Create the SQL Command object
        $SqlCmd = New-Object System.Data.SqlClient.SqlCommand

        $SqlCmd.CommandText = $_
        $SqlCmd.Connection = $SqlConnection

        #Open SQL connection
        $SqlCmd.Connection.Open()

        #Execute the Query
        $ReturnValue = $SqlCmd.ExecuteNonQuery()

        #Close the connection
        $SqlCmd.Connection.Close()
    }
}