none
PowerShell script to execute sql script

    Question

  • Hi,

    I am a database guy that have just started with PowerShell. I have one question:

    I have a select statement in .sql script file. I want to execute the script on server A.

    Then I have a database on server B, where the result should be inserted. Server B contains a database with a table where the column names and types, matches the columns returned by the script.

    The idea is to have a PowerShell script that can execute the sql script and store the result in the target table, without having to specify any column names or data types in the PowerShell script.

    The PowerShell script should take parameters like SourceServer, SourceDatabase, ScriptName, TargetServer, TargetDatabase, and TargetTable.

    Is it possible to achieve this in PowerShell?

    Ola Hallengren
    http://ola.hallengren.com
    Monday, July 29, 2013 8:05 PM

Answers

  • Rather than thinking about write a script, first test and work out the Powershell commands you want to execute. You can then move the commands into a Powershell script.

    You'll want to run a command to get the data and load into a variable. The invoke-sqlcmd cmdlet which is part of sqlps module in SQL Server 2012 will accomplish this:

    $SourceServer = "MySourceServer"
    $SourceDatabase = "master"
    
    $data = invoke-sqlcmd -ServerInstance $SourceServer -Database $SourceDatabase -Query "SELECT name, database_id, create_date FROM sys.databases"

    Next assuming you have table created on your target server you can use a little .NET to bulk load the data:

        $TargetServer = "MyTargetServer"
            $TargetDatabase = "MyTargetDatabase"
            $TargetTable = "MyTargetTable"
            $connectionString = "Server={0};Database={1};Integrated Security=True" -f $TargetServer,$TargetDatabase
            $conn=new-object System.Data.SqlClient.SQLConnection $ConnectionString
            $conn.Open()
            $bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $conn
            $bulkCopy.DestinationTableName = $TargetTable
            $bulkCopy.WriteToServer($Data)
            $conn.Close()

    I'd suggest running each line until each line is successfully then you can work on moving your commands to .ps1 file. Also if you look at the "help invoke-sqlcmd -full" you'll see there's an inputfile parameter to specify a .sql file instead of using -query.

    Tuesday, July 30, 2013 12:53 AM

All replies

  • Hi,

    Is Server B a linked server on Server A ?

    Take a quick look into the link "sql-server-connectivity-and-script-execution-using-powershell"

    Hope this help

    Sal

    salvador.lopez@live.com

    Monday, July 29, 2013 11:39 PM
  • Rather than thinking about write a script, first test and work out the Powershell commands you want to execute. You can then move the commands into a Powershell script.

    You'll want to run a command to get the data and load into a variable. The invoke-sqlcmd cmdlet which is part of sqlps module in SQL Server 2012 will accomplish this:

    $SourceServer = "MySourceServer"
    $SourceDatabase = "master"
    
    $data = invoke-sqlcmd -ServerInstance $SourceServer -Database $SourceDatabase -Query "SELECT name, database_id, create_date FROM sys.databases"

    Next assuming you have table created on your target server you can use a little .NET to bulk load the data:

        $TargetServer = "MyTargetServer"
            $TargetDatabase = "MyTargetDatabase"
            $TargetTable = "MyTargetTable"
            $connectionString = "Server={0};Database={1};Integrated Security=True" -f $TargetServer,$TargetDatabase
            $conn=new-object System.Data.SqlClient.SQLConnection $ConnectionString
            $conn.Open()
            $bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $conn
            $bulkCopy.DestinationTableName = $TargetTable
            $bulkCopy.WriteToServer($Data)
            $conn.Close()

    I'd suggest running each line until each line is successfully then you can work on moving your commands to .ps1 file. Also if you look at the "help invoke-sqlcmd -full" you'll see there's an inputfile parameter to specify a .sql file instead of using -query.

    Tuesday, July 30, 2013 12:53 AM
  • Thank you for answers.

    Salvador, I have no linked server.

    Cmille, this looks like a good way forward. I will try this.

    Ola Hallengren
    http://ola.hallengren.com

    Tuesday, July 30, 2013 11:56 AM