locked
How to execute SQL Script using windows powershell(using invoke-sqlcmd or any if) RRS feed

  • Question

  • OS : Windows server 2008
    SQL Server : SQL Server 2012
    Script: Test.sql (T-SQL)  example : "select name from sys.databases"
    Batch script: windows  MyBatchscript.bat ( here connects to sql server using sqlcmd  and output c:\Testput.txt) 
     (sqlcmd.exe -S DBserverName -U username -P p@ssword -i C:\test.sql -o "c:\Testoutput.txt)  ---it working without any issues.....

    This can execute if i double click MyBatchscript.bat file and can see the output in c:\testput.txt.

    Powershell: Similarly, How can i do in powershell 2.0 or higher versions?  can any one give full details with each step?

    I found some of them online, but nowhere seen clear details or examples and it not executing through cmd line (or batch script).

    example: invoke-sqlcmd -Servernameinstance Servername -inputfile "c:\test.sql" | out-File -filepath "c:\psOutput.txt"  --(call this file name MyTest.ps1)

    (The above script working if i run manually. I want to run automatic like double click (or schedule with 3rd party tool/scheduler ) in Batch file and see the output in C drive(c:\psOutput.txt))

    Can anyone Powershell experts give/suggest full details/steps for this. How to proceed? Is there any configurations required to run automatic?

    Thanks in advance.

    Tuesday, February 11, 2014 3:35 AM

Answers

  • Testeted the following code and it's working.....thanks all.

    Execute sql script using invoke-sqlcmd with batch script and without batch script.

    Option1: using Import sqlps

    1.Save sql script as "C:\scripts\Test.sql"  script in side Test.sql: select name from sys.databases

    2.Save Batch script as "C:\scripts\MyTest.bat" Script inside Batch script:

    powershell.exe C:\scripts\mypowershell.ps1

    3.Save powershell script as "C:\scripts\mypowershell.ps1"

    import-module "sqlps" -DisableNameChecking

    invoke-sqlcmd -Servername ServerName -inputFile "C:\scripts\Test.sql" | out-File -filepath "C:\scripts\TestOutput.txt"

    4.Run the Batch script commandline or double click then can able to see the output "C:\scripts\TestOutput.txt" file.

    5.Connect to current scripts location  cd C:\scripts (enter)

    C:\scripts\dir (enter )

    C:\scripts\MyTest.bat (enter)

    Note: can able to see the output in "C:\scripts" location as file name "TestOutput.txt".

    Option2: Otherway, import sqlps and execution

    1.Save sql script as "C:\scripts\Test.sql"  script in side Test.sql: select name from sys.databases

    2.Save powershell script as "C:\scripts\mypowershell.ps1"

    # import-module "sqlps" -DisableNameChecking #...Here it not required.

    invoke-sqlcmd -Servername ServerName -inputFile "C:\scripts\Test.sql" | out-File -filepath "C:\scripts\TestOutput.txt"

    3.Connect to current scripts location

    cd C:\scripts (enter)

    C:\scripts\dir (enter )

    C:\scripts\powershell.exe sqlps C:\scripts\mypowershell.ps1 (enter)

    Note: can able to see the output in "C:\scripts" location as file name "TestOutput.txt".

    • Marked as answer by tracycai Friday, February 14, 2014 1:36 AM
    Wednesday, February 12, 2014 3:18 PM

All replies

  • If you setup a SQL Agent job you can create your job step to be of type Powershell. As long as the Agent Account or the proxy account that the job is to run under has the permissions to the directory that your powershell script resides in then you can execute the powershell script from within the sql agent job step.

    Have a look at this link for setting up a powershell script to run from the Agent. - http://technet.microsoft.com/en-us/library/hh213688.aspx

    I hope that helps

    Thanks


    Warwick Rudd (MCM - SQL 2008 | MCT)

    (SQL Masters Consulting | My SQL Server Blog | Twitter)

    Wednesday, February 12, 2014 12:43 AM
  • Hi,

    You can create a shortcut for MyTest.ps1. Right click it and click 'create shortcut'. Then right click the shortcut and click Properties. In the Target field, type powershell.exe -command "& {C:\MyTest.ps1}", click Apply button.

    Thanks.


    Tracy Cai
    TechNet Community Support

    • Proposed as answer by jai88 Wednesday, February 25, 2015 1:29 AM
    • Unproposed as answer by jai88 Wednesday, February 25, 2015 1:29 AM
    Wednesday, February 12, 2014 4:45 AM
  • Testeted the following code and it's working.....thanks all.

    Execute sql script using invoke-sqlcmd with batch script and without batch script.

    Option1: using Import sqlps

    1.Save sql script as "C:\scripts\Test.sql"  script in side Test.sql: select name from sys.databases

    2.Save Batch script as "C:\scripts\MyTest.bat" Script inside Batch script:

    powershell.exe C:\scripts\mypowershell.ps1

    3.Save powershell script as "C:\scripts\mypowershell.ps1"

    import-module "sqlps" -DisableNameChecking

    invoke-sqlcmd -Servername ServerName -inputFile "C:\scripts\Test.sql" | out-File -filepath "C:\scripts\TestOutput.txt"

    4.Run the Batch script commandline or double click then can able to see the output "C:\scripts\TestOutput.txt" file.

    5.Connect to current scripts location  cd C:\scripts (enter)

    C:\scripts\dir (enter )

    C:\scripts\MyTest.bat (enter)

    Note: can able to see the output in "C:\scripts" location as file name "TestOutput.txt".

    Option2: Otherway, import sqlps and execution

    1.Save sql script as "C:\scripts\Test.sql"  script in side Test.sql: select name from sys.databases

    2.Save powershell script as "C:\scripts\mypowershell.ps1"

    # import-module "sqlps" -DisableNameChecking #...Here it not required.

    invoke-sqlcmd -Servername ServerName -inputFile "C:\scripts\Test.sql" | out-File -filepath "C:\scripts\TestOutput.txt"

    3.Connect to current scripts location

    cd C:\scripts (enter)

    C:\scripts\dir (enter )

    C:\scripts\powershell.exe sqlps C:\scripts\mypowershell.ps1 (enter)

    Note: can able to see the output in "C:\scripts" location as file name "TestOutput.txt".

    • Marked as answer by tracycai Friday, February 14, 2014 1:36 AM
    Wednesday, February 12, 2014 3:18 PM