locked
Calling powershell function from TSQL RRS feed

Answers

  • Have you defined incoming parameters in the beginning of powershell script file?

     param
    (
    [String] $param1,
    [String] $ param2
    ) 
    
    Finction code
    # Pass parameters into function
    Finction $param1 $param2
    

    It executes powershell script with parameters. You can build it on the fly.

    EXEC xp_cmdshell 'powershell C:\Desktop\Test.ps1 val1 val2' 


    Thursday, August 8, 2013 6:06 PM
  • I do not see any reason to call Powershell script from T-SQL.

    Powershell can get all values directly from SQL server tables and use them in functions and modules. You can schedule Powershell script in SQL job as Powershell job step type and start that job from T-SQL.


    Thursday, August 8, 2013 6:18 PM

All replies

  • Have you defined incoming parameters in the beginning of powershell script file?

     param
    (
    [String] $param1,
    [String] $ param2
    ) 
    
    Finction code
    # Pass parameters into function
    Finction $param1 $param2
    

    It executes powershell script with parameters. You can build it on the fly.

    EXEC xp_cmdshell 'powershell C:\Desktop\Test.ps1 val1 val2' 


    Thursday, August 8, 2013 6:06 PM
  • I do not see any reason to call Powershell script from T-SQL.

    Powershell can get all values directly from SQL server tables and use them in functions and modules. You can schedule Powershell script in SQL job as Powershell job step type and start that job from T-SQL.


    Thursday, August 8, 2013 6:18 PM
  • Fedor,

    Generally speaking you are correct there is little need to call PS script from T-SQL. However, I may have a good application for doing so...

    Consider that I want to have a SQL Agent job that call a procedure. Here is the flow.

    1. PS script or Dos command to find the latest file in a network share folder and copy it to a local serve disk.

    2. The File is a CSV file that is UTF-8 Encoded and must be converted to MS-DOS CSV because T-SQL Bulk Insert (SQL Server) doesn't support the aforementioned file type. So use a PS script to change the encoding. The CSV file contains over 100K rows and is 75 MB in size.

    3. From there run the T-SQL script to load via Bulk Insert the CSV file and process it forward to a table. 

    When I am done with my coding I will be happen to send the info either here or off-line this close discussion.

    Anyway thanks for your input back in 2013, it is useful now in 2016.

    Hank Freeman

    hfreeman@msn.com.


    FHankFreeman

    Sunday, May 8, 2016 2:27 AM
  • Why not bulk insert directly from the Powershell script using SqlBulkCopy?


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Sunday, May 8, 2016 2:39 AM