Author Posts

July 28, 2014 at 1:32 pm

I am looking for a way to use powershell to direct a remote MS-SQL server to execute a T-SQL Query

I know I can open a new ps session using
Enter_ps_session -computername
(remove the second underscore)

but can someone tell me how to execute a Query saved as a file once I am in this new PS_session?

July 28, 2014 at 1:39 pm

If the server has the SQL PowerShell module, I think the command is Invoke-SQL. If not, you'll have to code something in .NET.

But you don't need Remoting. You can use .NET to tell the server to run the query; that's a native capability.

July 28, 2014 at 2:00 pm

so a command as simple as this
invoke-SQL -serverinstance SQLServer\defaultinstance -user SA -Password -query \\SQLserver\Queries\query1.sql

can the query argument be used to specifiy a query saved as a file ?

July 28, 2014 at 2:10 pm

I don't have it in front of me so I'm not sure if direct file input is possible, but Get-Content can obviously read a file. Lots if ways to read a file. For that matter if the file is on the server just use Invoke-Command to kick off Osql.exe. It'll take a file.

July 28, 2014 at 2:44 pm has some lovely examples, BTW. And yes, you can specify an -InputFile for the query.

July 28, 2014 at 3:09 pm

Here's a sample script that truncates log files of all user DB's using Invoke-SQLCMD:

(Invoke-SQLCMD -Query "SELECT * FROM sysdatabases WHERE dbid > 4") | ForEach-Object {
    $SQLLogString = "N'" + (Invoke-SQLCMD -Query ("SELECT name FROM sys.master_files WHERE database_id = " + $_.dbid + " AND type = 1;")).name + "'"
    Invoke-SQLCMD -Query ("USE [" + $ + "]; ALTER DATABASE [" + $ + "] SET RECOVERY SIMPLE WITH NO_WAIT;")
    Invoke-SQLCMD -Query ("USE [" + $ + "]; DBCC SHRINKFILE($SQLLogString, 1); ALTER DATABASE [" + $ + "] SET RECOVERY FULL WITH NO_WAIT")

This script needs SQLPS module. This is installed by default with SQL 2012 and higher versions. If you're using an older SQL version, you need to download and install the following 3 components ([url][/url]) in order:

Microsoft® System CLR Types for Microsoft® SQL Server® 2012 (SQLSysClrTypes.msi) [url][/url]
Microsoft® SQL Server® 2012 Shared Management Objects (SharedManagementObjects.msi) [url][/url]
Microsoft® Windows PowerShell Extensions for Microsoft® SQL Server® 2012 (PowerShellTools.msi) [url][/url]
I've linked to the x64 versions of these files, but you need to get the version that matches your OS.

You also need to:

Import-Module SQLPS

then close ISE and reopen it again.

July 28, 2014 at 8:01 pm

in addition to Sam's solution I would suggest using PowerShell here strings with invoke-sqlcmd. If and when you run long and complex T-SQL it will be easier to use as opposed to writing a long one line T-SQL statement.

$sqlcmd = @"
select * from table
where column1='foo'
and column2='bar'
and column3='bong'
order by column3

invoke-sqlcmd -query $sqlcmd 

July 29, 2014 at 6:21 am

Thanks guys, Don's solution of kicking off osql.exe looks like it will be the solution Ill test it this afternoon.