Executing SQL Query Remotely

Tagged: 

This topic contains 7 replies, has 4 voices, and was last updated by Profile photo of NetAdminTX NetAdminTX 2 years, 4 months ago.

  • Author
    Posts
  • #17532
    Profile photo of NetAdminTX
    NetAdminTX
    Participant

    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?

  • #17533
    Profile photo of Don Jones
    Don Jones
    Keymaster

    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.

  • #17535
    Profile photo of NetAdminTX
    NetAdminTX
    Participant

    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 ?

  • #17536
    Profile photo of Don Jones
    Don Jones
    Keymaster

    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.

  • #17537
    Profile photo of Don Jones
    Don Jones
    Keymaster

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

  • #17538
    Profile photo of Sam Boutros
    Sam Boutros
    Participant

    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.

  • #17546
    Profile photo of
    Anonymous

    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 
  • #17562
    Profile photo of NetAdminTX
    NetAdminTX
    Participant

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

    *PROBLEM SOLVED*

You must be logged in to reply to this topic.