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()
    }
}

Answer 2

You can use the ordinary .NET classes SqlConnection and SqlCommand from PowerShell. To do that you don't need SQL Server, nor any specific PowerShell modules, installed. Just create the objects using the New-Object cmdlet.

Update:

Here's a sample of how you could do (no error handling added) to call execute SQL code without using SQL specific cmdlets:

$connectionString = "" #Set your connection string here
$connection = New-Object -TypeName System.Data.SqlClient.SqlConnection($connectionString)
$query = "" #Set your sql query here
$command = New-Object -TypeName System.Data.SqlClient.SqlCommand($query, $connection)
$connection.Open()
$command.ExecuteNonQuery() #Other methods are available if you want to get the return value of your query.
$connection.Close()

Answer 3

You are trying to do something that perhaps cannot be done, depends on your definition of "sql not installed". If the client does not have the sql client tools installed, you will not be able to talk directly to sql server at all.

If you need to install sql server on a remote computer, you will not in general be able to do so without admin permissions, and doing so remotely with admin permissions is still complicated as SQL server 2012 is not designed for a remote install -- though you could perform a remote install using the CMD processor via remote connection (if you have some way to do that as this is not built in to the standard windows installation.

If the sql client tools are installed on your local machine (where you are running powershell) and sql server is running on the remote machine -- and you have a database login with suitable permissions, you can instantiate SqlCommand objects to do everything you might need.