This recipe shows how you can execute either a hardcoded query or a SQL Script from PowerShell.
Create a file in your C:\Temp folder called SampleScript.sql. This should contain the following:
SELECT TOP 10 * FROM Person.Person
The following are the steps in executing a query / SQL script:
Open PowerShell ISE as administrator.
Import the SQLPS module and create a new SMO Server Object:
#import SQL Server module Import-Module SQLPS -DisableNameChecking #replace this with your instance name $instanceName = "localhost" $server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName
Add the following script and run:
$dbName = "AdventureWorks2014" $db = $server.Databases[$dbName] #execute a passthrough query, and export to a CSV file #line continuation in code below only happens at #the pipe (|) delimiter Invoke-Sqlcmd -Query "SELECT * FROM Person.Person" -ServerInstance "$instanceName" -Database $dbName...