Executing SQL Query Remotely

PowerShell Help
1

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?

2

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.

3

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 ?

4

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.

5

http://msdn.microsoft.com/en-us/library/cc281720.aspx has some lovely examples, BTW. And yes, you can specify an -InputFile for the query.

6

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 [" + $_.name + "]; ALTER DATABASE [" + $_.name + "] SET RECOVERY SIMPLE WITH NO_WAIT;")
    Invoke-SQLCMD -Query ("USE [" + $_.name + "]; DBCC SHRINKFILE($SQLLogString, 1); ALTER DATABASE [" + $_.name + "] 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]http://www.microsoft.com/en-us/download/details.aspx?id=29065[/url]) in order:

Microsoft® System CLR Types for Microsoft® SQL Server® 2012 (SQLSysClrTypes.msi) [url]http://go.microsoft.com/fwlink/?LinkID=239644&clcid=0x409[/url]
Microsoft® SQL Server® 2012 Shared Management Objects (SharedManagementObjects.msi) [url]http://go.microsoft.com/fwlink/?LinkID=239659&clcid=0x409[/url]
Microsoft® Windows PowerShell Extensions for Microsoft® SQL Server® 2012 (PowerShellTools.msi) [url]http://go.microsoft.com/fwlink/?LinkID=239656&clcid=0x409[/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.

7

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.

http://technet.microsoft.com/en-us/library/ee692792.aspx

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

invoke-sqlcmd -query $sqlcmd
8

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

PROBLEM SOLVED