Execute a .SQL file in Powershell without having SQL Server installed?
database, powershell, sql-scripts, sql-server, sql-server-2012
Solution
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()
Problem
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.